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,

Alastairyou will still need some way of "generating" the month end dates

my advice: use an integers table --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) --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?|||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|||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|||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!!|||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|||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?|||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/infocenter/iseries/v5r3/index.jsp?topic=/db2/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!|||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...|||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|||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|||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 writeCURRENT DATE + 1 monthto 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 useCAST('2006-03-01' AS date) - 1 day|||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.|||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):
SELECT count(*)
FROM cal_table
WHERE MONTH_LOGGED <= @.Month_Reported
AND ( MONTH_RESOLVED > @.Month_Reported OR
MONTH_RESOLVED IS NULL )|||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|||Sure, but does that take into account the fact that a call from January, resolved in March, must be counted in both January and February?