Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Need assistance with SQL query

    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.

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Re: Need assistance with SQL query

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

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •