Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Query By Week

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Query By Week

    I need a little help with a query where the results are to be grouped by week. To be brief, I'll only use 3 columns from my table, TICKET_NUMBER, OPEN_TIME, RESOLVED_TIME. What I need is a query that by week shows how many Tickets were opened in that week, how many were still open in that week that are 1-30 days old and how many were still open in that week that are more than 30 days old.

    Here is what I have so far:
    SELECT TRUNC(OPEN_TIME, 'D') WEEK,
    COUNT(TICKET_NUMBER) OPENED_CURRENT_WEEK
    FROM MYTABLE
    WHERE OPEN_TIME >= TO_DATE('1/4/2004', 'MM/DD/YYYY') AND
    OPEN_TIME <= TO_DATE('2/28/2004 23:59:59', 'MM/DD/YYYY hh24:mi:ss')
    GROUP BY TRUNC(OPEN_TIME, 'D')

    This gives me the number of tickets opened during the week, but I am unsure of how to get the number of tickets that were still open from previous weeks that are either 1-30 days olds or more than 30 days old.

    Any help would be greatly appreciated.

    Jeff

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query By Week

    Presumably you need to use conditions like:

    OPEN_TIME >= TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND OPEN_TIME< TO_DATE('1/4/2004', 'MM/DD/YYYY')
    AND RESOLVED_TIME IS NULL

    for 1030 days old, and

    OPEN_TIME < TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND RESOLVED_TIME IS NULL

    for over 30 days old.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Hi
    There are 2 ways.
    1. Write an individual query for how many Tickets were opened in that week, how many were still open in that week that are 1-30 days old and how many were still open in that week that are more than 30 days old. Then join all the queries

    2. Use "Case when" and say count(1) when condition is true and count(0) when condition is false.

    The second one is preferable.

    Anitha

  4. #4
    Join Date
    Feb 2004
    Posts
    6

    Re: Query By Week

    However, won't this just give me the tickets that were opened 1-30 days before the week and are still open today?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query By Week

    Originally posted by jjohns09
    However, won't this just give me the tickets that were opened 1-30 days before the week and are still open today?
    Who are you addressing that question to - Ganitha or me?

  6. #6
    Join Date
    Feb 2004
    Posts
    6

    Re: Query By Week

    Originally posted by andrewst
    Who are you addressing that question to - Ganitha or me?
    Andrewst

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query By Week

    Originally posted by jjohns09
    However, won't this just give me the tickets that were opened 1-30 days before the week and are still open today?
    OPEN_TIME >= TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND OPEN_TIME< TO_DATE('1/4/2004', 'MM/DD/YYYY')
    AND RESOLVED_TIME IS NULL

    shows tickets that were opened 1-30 days before 1/4/2004 and are still open today.

    OPEN_TIME < TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND RESOLVED_TIME IS NULL

    shows tickets that were opened over 30 days before 1/4/2004 and are still open today.

  8. #8
    Join Date
    Feb 2004
    Posts
    6

    Re: Query By Week

    Originally posted by andrewst
    OPEN_TIME >= TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND OPEN_TIME< TO_DATE('1/4/2004', 'MM/DD/YYYY')
    AND RESOLVED_TIME IS NULL

    shows tickets that were opened 1-30 days before 1/4/2004 and are still open today.

    OPEN_TIME < TO_DATE('1/4/2004', 'MM/DD/YYYY')-30
    AND RESOLVED_TIME IS NULL

    shows tickets that were opened over 30 days before 1/4/2004 and are still open today.
    But what I am really after is how many tickets where still open in the given week. They may or may not still be open. If they're not then they were resolved after the given week.

    Example:
    TICKET_NUMBER OPEN_TIME RESOLVED_TIME
    123 12/1/2003 1/10/2004
    124 12/28/2003 1/12/2004
    125 1/4/2004 1/10/2004
    126 1/11/2004 1/11/2004

    Results:
    WEEK OPENED 1-30 >30
    1/4/2004 1 1 1
    1/11/2004 1 1 0

    I hope this helps to explain what I'm after better.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query By Week

    So you want the ones that are either still open, or were closed after the given week:

    (resolved_time is null or resolved_time > end_of_week)

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Will this query take care of the requirement?

    Code:
    SQL> desc mytable
     Name                           Null?    Type
     ------------------------------ -------- ------
     TICKET_NUMBER                           NUMBER(4)
     OPEN_TIME                               DATE
     RESOLVED_TIME                           DATE
    
    SQL> select TICKET_NUMBER  ,OPEN_TIME      ,RESOLVED_TIME from mytable
      2  order by OPEN_TIME;
    
    TICKET_NUMBER OPEN_TIME RESOLVED_
    ------------- --------- ---------
                6 05-JAN-04
               10 09-JAN-04
                7 12-JAN-04
               11 16-JAN-04
                8 19-JAN-04
               12 23-JAN-04
                9 26-JAN-04
               13 30-JAN-04
                1 02-FEB-04
               14 06-FEB-04
                2 09-FEB-04
    
    TICKET_NUMBER OPEN_TIME RESOLVED_
    ------------- --------- ---------
               15 13-FEB-04
                3 16-FEB-04
               16 20-FEB-04
                4 23-FEB-04
               17 27-FEB-04
                5 01-MAR-04
    
    17 rows selected.
    
    SQL> select open_wk, sum(A) "currWk", sum(B) "1-30", sum(C) ">30"
    from
    (
    select open_wk,
    decode(greatest(open_days,-6),open_days,decode(greatest(open_days,0),open_days,0,1),0) A ,
    decode(greatest(open_days,1),open_days,decode(greatest(open_days,30),open_days,0,1),0) B ,
    decode(greatest(open_days,31),open_days,1,0) C
    from(
    select A.open_wk open_wk, trunc(B.open_time,'D'), A.open_wk-B.open_time open_days
    from
    (select distinct trunc(open_time, 'D') open_wk from mytable) A, mytable B
    where B.resolved_time is null OR B.resolved_time>A.open_wk+7))
    group by open_wk;
    
    OPEN_WK       currWk       1-30        >30
    --------- ---------- ---------- ----------
    04-JAN-04          2          0          0
    11-JAN-04          2          2          0
    18-JAN-04          2          4          0
    25-JAN-04          2          6          0
    01-FEB-04          2          8          0
    08-FEB-04          2          8          1
    15-FEB-04          2          8          3
    22-FEB-04          2          8          5
    29-FEB-04          1          8          7
    
    9 rows selected.
    
    SQL>


    explanation:

    select distinct trunc(open_time, 'D') open_wk from mytable
    will select a start date of each week.

    join condition
    B.resolved_time is null OR B.resolved_time>A.open_wk+7
    will ensure that either the ticket is still open or it got closed in week after the date selected in query above

    select A.open_wk open_wk, trunc(B.open_time,'D'), A.open_wk-B.open_time open_days
    here the open_days shows the number of days ticket remained opened with respect to each week.

    value of open_days : <-6 means it was opened after the week we are considering
    value of open_days : -6 to 0 means opened in current week
    value of open_days : 1-30 means opened 30 days ago
    value of open_days greater than 30 means open for more than 30 days

    A suggestion : This seems like a report on ticket closing. it will be a good practice to have at table that keeps this kind of history. Have a stoerd proc to update this table. Preferrably with a separate query for each requirement. This will ensure less possibilities of errors.

    I believe - write code that makes your life simpler. Not as complex as the query above

    Do test this query and let me know if it gives you the output that you require.
    Oracle can do wonders !

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    A suggestion : This seems like a report on ticket closing. it will be a good practice to have at table that keeps this kind of history. Have a stoerd proc to update this table. Preferrably with a separate query for each requirement. This will ensure less possibilities of errors.

    I believe - write code that makes your life simpler. Not as complex as the query above
    Since we are philosophising: I would not store this information in a separate, redundant table. However, it might be useful to "encapsulate" the complexity of the query in a view, so that the report becomes a simple select.

  12. #12
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Originally posted by andrewst
    Since we are philosophising: I would not store this information in a separate, redundant table. However, it might be useful to "encapsulate" the complexity of the query in a view, so that the report becomes a simple select.
    Reporting rules do prefer de-normalised database. Performance is the key there. View can help reduce the complexity for sure, report performance requires a better table structure. What do you say Tony?
    Oracle can do wonders !

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    Reporting rules do prefer de-normalised database. Performance is the key there. View can help reduce the complexity for sure, report performance requires a better table structure. What do you say Tony?
    I say:

    1) If the views perform well enough to meet the reporting requirements, then why denormalise?

    2) If the views can't perform well enough after all appropriate tuning, use a materialized view. This is a denormalisation, like your table, but it is system-managed rather than code-managed. Less moving parts, less prone to error.

  14. #14
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Originally posted by andrewst
    I say:

    1) If the views perform well enough to meet the reporting requirements, then why denormalise?

    2) If the views can't perform well enough after all appropriate tuning, use a materialized view. This is a denormalisation, like your table, but it is system-managed rather than code-managed. Less moving parts, less prone to error.
    1) Right. But its always good to take care of growing volume of data, which can be best decided by the application owner.

    2) Materialised view in turn will have an underlying SQL ... which would be as complex as I wrote above. For making it simpler, isn't it better to split SQL (as U suggested above) and execute same in stored proc? Maybe then, schedule the stored proc to refresh at same frequency as the materialised view would !!! Finally, the materialised view will hold as much data and resources as the a table would !!!
    Oracle can do wonders !

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by cmasharma
    1) Right. But its always good to take care of growing volume of data, which can be best decided by the application owner.

    2) Materialised view in turn will have an underlying SQL ... which would be as complex as I wrote above. For making it simpler, isn't it better to split SQL (as U suggested above) and execute same in stored proc? Maybe then, schedule the stored proc to refresh at same frequency as the materialised view would !!! Finally, the materialised view will hold as much data and resources as the a table would !!!
    1) I should have said, if views perform well enough for the anticipated data volumes. You wouldn't advocate denormalisation "just in case", would you?

    2) I don't see the point of simplifying a moderately complex query by writing procedural code, no. It only has to be written once. And whether it is used in a view or a materialised view is irrelevant. Yes, the MV will hold as much data and resources as the table would - so it is no better or worse on that score. But (I claim) it wins because it is managed by the DBMS rather than by bespoke application code. Also, it is transparent to applications, e.g. reports. If we later decide that performance would be improved by storing pre-computed results at a different level, we can change the MV defintion and all reports will reap the benefits without having to be modified.

Posting Permissions

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