I have a date table, and I have a table with tickets that have an open date field and a close date field. I need to count the number of tickets which were open on each calendar day
ie.
Date Table
| Date | Year |
|---|---|
| 2021-02-19 | 2021 |
| 2021-02-20 | 2021 |
| 2021-02-21 | 2021 |
| 2021-02-22 | 2021 |
| 2021-02-23 | 2021 |
| 2021-02-24 | 2021 |
Ticket Table
| Ticket # | Opened Date | Closed Date |
|---|---|---|
| 00000001 | 2021-02-01 | 2021-02-23 |
| 00000001 | 2021-02-16 | 2021-02-19 |
| 00000001 | 2021-02-21 | Null |
| 00000001 | 2021-10-01 | 2021-01-23 |
| 00000001 | 2021-02-01 | 2021-02-19 |
| 00000001 | 2021-02-05 | 2021-02-19 |
| 00000001 | 2021-02-01 | 2021-02-18 |
| 00000001 | 2021-02-23 | 2021-02-24 |
| 00000001 | 2021-02-22 | 2021-02-22 |
| 00000001 | 2021-02-01 | 2021-02-21 |
I haven't tried anything because I am not sure where to start. I assume it would be beneficial to join with the date table which is why I added it to my question here.
SQL 2015
Thanks in Advance
When asking questions like this it's really helpful to provide easily reproducible DDL/DML. This makes it easier for folks to answer your question.
Consider:
DECLARE @Dates TABLE (Date DATE, Year INT);
INSERT INTO @Dates (Date, Year) VALUES
('2021-02-19', 2021), ('2021-02-20', 2021), ('2021-02-21', 2021),
('2021-02-22', 2021), ('2021-02-23', 2021), ('2021-02-24', 2021);
DECLARE @Tickets TABLE (TicketNumber VARCHAR(8), OpenedDate DATE, ClosedDate DATE);
INSERT INTO @Tickets (TicketNumber, OpenedDate, ClosedDate) VALUES
('00000001', '2021-02-01', '2021-02-23'), ('00000002', '2021-02-16', '2021-02-19'), ('00000003', '2021-02-21', NULL ),
('00000004', '2021-10-01', '2021-01-23'), ('00000005', '2021-02-01', '2021-02-19'), ('00000006', '2021-02-05', '2021-02-19'),
('00000007', '2021-02-01', '2021-02-18'), ('00000008', '2021-02-23', '2021-02-24'), ('00000009', '2021-02-22', '2021-02-22'),
('00000010', '2021-02-01', '2021-02-21');
I've adjusted your example data here as I think you intended the ticket numbers to be different for each row.
To produce a result set which shows the number of open tickets all you really need is a LEFT OUTER JOIN from the dates table to the tickets table. Since we want the date to be within a range (which is essentially endless when there is no closed date) we can use a BETWEEN as the predicate, and then aggregate by the date. A LEFT OUTER JOIN is used so we get a 0 when there are no tickets for that day. Using INNER JOIN would result in the row being excluded from the result set.
SELECT d.Date, COUNT(t.TicketNumber) AS OpenTickets
FROM @Dates d
LEFT OUTER JOIN @Tickets t
ON d.Date BETWEEN t.OpenedDate AND COALESCE(t.ClosedDate,'9999-12-31')
GROUP BY d.Date
| Date | OpenTickets |
|---|---|
| 2021-02-19 | 5 |
| 2021-02-20 | 2 |
| 2021-02-21 | 3 |
| 2021-02-22 | 3 |
| 2021-02-23 | 3 |
| 2021-02-24 | 2 |