Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2005
    Posts
    6

    Unanswered: Counting unresolved support calls based on two dates

    I'm sure this question shouldn't be too difficult but I just keep having a mental block about how to do it - any help would be very gratefully received!

    I have a single table which records support calls logged to a helpdesk.
    The three relevant fields for this question are:
    TICKET_NUMBER - unique integer assigned to each call (not null)
    DATE_LOGGED - date when the call was registered (not null)
    DATE_RESOLVED - date when the call was resolved, null if still unresolved.

    Now, I'm trying to produce a report which states the number of unresolved calls carried over at the end of each month. I can work this out for any given month on a parameterised basis as follows:
    SELECT
    COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
    FROM
    CALL_TABLE
    WHERE
    DATE_LOGGED <= @LastDayOfMonth
    AND (DATE_RESOLVED > @LastDayOfMonth OR DATE_RESOLVED IS NULL)

    However, what I just can't seem to get my head round is how to run this for all of the data at once to get a report like this:

    Month End Unresolved Calls
    JAN 102
    FEB 97
    MAR 113
    etc.

    One thought I had was to have a DUMMY_DATE_TABLE which just contained the last date of each month as a field called DUMMY_DATE, and join to that table as follows:
    SELECT
    COUNT(TICKET_NUMBER) AS UNRESOLVED_CALLS
    MONTH(DUMMY_DATE) AS MONTH,
    FROM
    CALL_TABLE, DUMMY_DATE_TABLE
    WHERE
    DATE_LOGGED <= DUMMY_DATE
    AND (DATE_RESOLVED > DUMMY_DATE OR DATE_RESOLVED IS NULL)

    But that seems a bit silly to have to maintain a table that only has the last date of each month in it, and I'm pretty sure there must be a cleaner way.
    Can anybody help point me in the right direction?!

    thanks,

    Alastair

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will still need some way of "generating" the month end dates

    my advice: use an integers table --
    Code:
    create table integers (i integer not null primary key);
    insert into integers (i) values
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    now you can use this to generate as many monthend dates as you want, by generating the correct number of integers

    for example, here we can generate 48 integers (will give you 4 years) --
    Code:
    select tens.i*10 + units.i as dd
      from integers as units
    cross 
      join integers as tens
     where tens.i*10 + units.i between 0 and 47
    with me so far?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The biggest problem is that SQL doesn't have a uniform way of manipulating date values... Every SQL database engine has to "roll your own" functions for manipulating dates, so the only way to simply get portable date manipulation is to do it on the client.

    If it wasn't for that limitation, this would be easy to code in pure SQL... Just group by year and month, count incidents created in that month as one column and incidents not resolved in that month in a second column. This is easy to do on a single database engine, but I don't know of any portable way to do it.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, what about if a call is entered in march 2006, and goes unresolved through to may 2006, with your method it will not get counted as unresolved in april 2006

    what if there were no calls created or resolved in april 2006, with your method that month will then be totally missing

    i think you have to somehow generate the months, and then use a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    portable date manipulation ==> do it on the client?

    no way!!!!

    you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?

    FEH!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2005
    Posts
    6
    Thankyou both for your feedback.

    Pat - sorry I didn't mention but I'll be doing this in DB2 SQL, so I'm happy with the syntax of functions dealing with dates/months etc. - my concerns were more with the problems that r937 mentioned about how to ensure the logic for grouping is done appropriately at the end of every calendar month (even, for instance, if there were no calls received in any dates of that month)...

    Thanks for the suggestion about the integer table - I guess I was hoping there was a way that was a bit.... cleaner, but I guess not!

    alastair

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so do you want me to continue outlining how to generate the monthend dates from the integers?

    i'm afraid i don't have DB2 to play with, but the strategy is to start with the earliest month (which can be obtained by a SELECT MIN() subquery), and then add a number of months to it, where this number is an integer in the range 0 through 47 (or whatever)

    if you want me to help you with this, i will need a moment to go search google to see if i can find the DB2 SQL Reference manual again, they keep moving it

    got a link?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2005
    Posts
    6
    No -thankyou for your help, I can implement it from here (although, for reference, the DB2 SQL reference lib can be found here: http://publib.boulder.ibm.com/infoce...rbafzmst02.htm)

    I am grateful for your answer, it's just that I come from a C background, where constructing a routine to loop through month end-dates is very easy, so the thought of needing to create a user table just to hold integers seems very odd!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but to write efficient queries, you may want to unlearn the programmer's record-at-a-time-in-a-loop mentality, and start dealing with single sets of records

    thanks for the link

    WTF, there's no "date add" function to add 1 month to a date??? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries? eeewww...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, in DB2 release 5 you have to use date arithmetic expressions instead of date manipulation functions... This is exactly the problem I was referring to earlier... It isn't hard to code a database engine specific solution, but those solutions are engine specific... You have to code one for every blasted database engine!

    Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    portable date manipulation ==> do it on the client?

    no way!!!!

    you're going to return 300,000 rows to the client just so that you can avoid having to decide between using EXTRACT() or MONTH() or DATEPART() on the server?

    FEH!!
    Show me a better way to get portable date manipulation in SQL, and I'll be eternally grateful. So far, if I want do to an operation on three different engines, I have to code it three different ways (or resort to bringing the data to a client, which as you pointed out is usually ugly).

    -PatP

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    there's no "date add" function to add 1 month to a date??? does this mean we would have to add 1 to MONTH() and watch for year-end boundaries?
    In DB2, you can write
    Code:
    CURRENT DATE + 1 month
    to specify the date in the next month with the same day number as today.

    B.t.w.: to obtain the last day of (say) February 2006, you may use
    Code:
    CAST('2006-03-01' AS date) - 1 day
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Jun 2005
    Posts
    6
    Quote Originally Posted by Pat Phelan
    Alastair: If a call originates in Novemember and is resolved in February, do you want it counted in just November, or November, December, and January? Based on your original descripton, I would think it would only be counted in November, but based on Rudy's interpretation you might want it counted in all of them. Please clarify.

    -PatP
    No, I want it to be counted in the total for every month until that call is resolved (i.e. November, December, and January).

    I agree with you that data formats are always a bugger to work with - I work across DB2 {d'2005-03-01'}, SQL Server (01/03/2005), and Oracle (01-Mar-05) in both UK and US date formats and it gives me no end of headaches. However, the problem I am concerned with here is not so much how to work out the months - more the logic to work out the outstanding calls (for instance, consider that I had asked for help with a report that listed the outstanding calls at the end of every day, rather than every month and I would still have the same problems)

    In the DB2 SQL reference I have noticed procedural loop statements such as WHILE, DO etc. which I thought would let me calculate and loop through each date, but it seems that these are only available in stored procs rather than in inline SQL which is what I need to do here..... darn.

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A classical "group by" is not possible in this case, since an item from January which is resolved in May has to be counted as "unresolved" in all of January, February, March and April, i.e., not just in one "group by month" group.

    Recursive SQL could be used to accomplish this, but let's keep out of that :-)

    So the only viable approach left is one that generates the report for a single month.
    (This query could be put in a (parametrised) stored procedure, to be called for all months of interest.)

    So let's assume there is a column called MONTH_LOGGED, and a column called MONTH_RESOLVED, both of the form 12*YEAR(date_..)+MONTH(date_..), with a NULL in MONTH_RESOLVED if not yet resolved.
    Now for the report of the month @Month_Reported (which is the mentioned stored proc parameter):
    Code:
    SELECT count(*)
    FROM   cal_table
    WHERE  MONTH_LOGGED <= @Month_Reported
      AND  ( MONTH_RESOLVED > @Month_Reported  OR
             MONTH_RESOLVED IS NULL )
    Last edited by Peter.Vanroose; 05-28-06 at 06:51.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    peter, if you look at post #1, that's pretty much how alastair is doing it now

    what he wants is to do it for multiple months at a time

    the solution i suggested involves generating the months based on "DATEADD" functionality to add an integer number of months to a start date, and i believe he said he could build up that solution on his own
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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