find open trouble tickets - by date

SQL Apprentice Question
This one has me stumped. We have a help desk system in which we create
a ticket on the BeginDate and close it on the EndDate. How would I
find out how many tickets were open for each day in a range. I can do
it easily for one day, because i'm entering the date as a parameter -
where MyDate is greater than BeginDate and less that EndDate.

In my current mode of thought [which is probably narrow and incorrect
=) ] the only thing i can think of is to create a table with each day
of the year and use the dates in that table as the parameter in some
way.



Celko Answers
Yes, that is how you do it. That is called a Calendar table and it
holds other date information like holidays, etc.

CREATE TABLE Tickets
(ticker_nbr INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME, --null means open ticket
CHECK (start_date < end_date)
..);


use COALESCE (end_date, CURRENT_TIMESTAMP) in your queries


SELECT C.cal_date, COUNT(T.ticket_nbr) AS open_ticket_tally,
FROM T.Tickets, Calendar AS C
WHERE C.cal_date BETWEEN C.start_date
AND COALESCE (C.end_date, CURRENT_TIMESTAMP)
AND C.cal_date BETWEEN @my_start_date AND @my_end_date;