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
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
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...