Hi,

I have a table which I want to analyse and get the following results from:

The table contains is a helpdesk table and contains information on a job, who it is allocated to and when it was submitted.

I'm trying to break down the table and find out what jobs are submitted to a certain group of people and the length of time the job was sitting there.

So for example if there are 10 jobs in the queue, I want to be able to say ok out of the 10 jobs, 5 of them were logged in the last 7 days, 2 were logged in the last fortnight and the rest in the last month.

SELECT ticket_id, priority,submitted_on,deadline,assigned_to
FROM pt6hd_ticket_view
WHERE (NOT status = 'closed' and (assigned_to = 'sean' or assigned_to = 'andrew' or assigned_to = 'matthew' or assigned_to = 'kevin' or assigned_to = '!service_desk' or assigned_to = 'belinda'))
ORDER BY submitted_on ASC

The above is my code so far...

Any help would be appreciated.. thanks.

very decent way of doing this in the arena of data warehousing or other wise is to have a table to define week,quarter,half yearly,yearly fields
and then you can run joins to run between any two dates/weekly or etc... I guess it would be of help.. Sorry I cant give you any code as it is boring to code some old sql...

Hi,
Your explanation and the example is a bit confusing.
Still as per my understanding.

if ur table is TRY with the following structure

Name Null? Type
------------------------------- -------- ----
TID NUMBER
SUBMITiON DATE
DEADLINE DATE
ASSIGNEDTO VARCHAR2(4)
STATUS VARCHAR2(1)
PRIORITY NUMBER

The following query will give the GROUP ASSIGNED TO, NUMBER OF DAYS PENDING FROM , NUMBER OF JOBS Pending.

SELECT ASSIGNEDTO ,COUNT(TID), TRUNC(SYSDATE-SUBMITION) FROM TRY
WHERE STATUS !='C'
GROUP BY ASSIGNEDTO , TRUNC(SYSDATE-SUBMITiON);

I hope this helps
Regards