Open Ticket Count Per Day

Question

I have a table that looks like this

id | Submit_Date | Close_Date
------------------------------
 1 | 2015-02-01  | 2015-02-05
 2 | 2015-02-02  | 2015-02-04
 3 | 2015-02-03  | 2015-02-05
 4 | 2015-02-04  | 2015-02-06
 5 | 2015-02-05  | 2015-02-07
 6 | 2015-02-06  | 2015-02-07
 7 | 2015-02-07  | 2015-02-08

I can get a count of how many ticket were open on a particular day with this:

Select count(*) from tickets where '2015-02-05' BETWEEN Submit_Date and Close_Date

This gives me 4, but I need this count for each day of a month. I don't want to have to write 30 queries to handle this. Is there a way to capture broken down by multiple days?

you could build a loop that does this in probably < 10 loc. don't think you can do it in a single select without 30 subqueries which kind of defeats the point. — TZHX, Feb 26 '15 at 15:12
Hmm... I guess I could, I just seems like there should be a way to handle this in a single query. — Hoopdady, Feb 26 '15 at 15:14
I can only think of Stored Procedure. Or use Excel to populate 30 lines. — Jama Djafarov, Feb 26 '15 at 15:22
@TZHX I did the loop, but it gave me 30 different result sets instead of putting them in a single result set. — Hoopdady, Feb 26 '15 at 15:41

score 1 · Answer 1 · answered Feb 26 '15 at 15:24

I created a solution a way back using a mix of @Heinzi s solution with the trick from Generate a resultset of incrementing dates in TSQL

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

SELECT dates.myDate,
   (SELECT COUNT(*) 
      FROM tickets 
     WHERE myDate BETWEEN Submit_Date and Close_Date
   )
FROM 
(select Dates_To_Checkselect dateadd(day, number, @dt) mydate
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd) dates

Code is combined from memory, I don't have it in front of me so there can be some typo's

score 0 · Answer 2 · edited May 23 '17 at 12:17

First, you'll need a table that contains each date you want to check. You can use a temporary table for that. Let's assume that this table is called Dates_To_Check and has a field myDate:

SELECT myDate,
       (SELECT COUNT(*) 
          FROM tickets 
         WHERE myDate BETWEEN Submit_Date and Close_Date)
  FROM Dates_To_Check

Alternatively, you can create a huge table containing every possible date and use a WHERE clause to restrict the dates to those you are interested in.

score 0 · Answer 3 · answered Feb 26 '15 at 15:22

If you're in SQL Server 2012 or newer you can do this using window functions with a small trick where you add 1 to the open days -1 to the closing days and then do a running total of this amount:

select distinct date, sum(opencnt) over (order by date) from (
  select 
    Submit_Date as date, 
    1 as opencnt 
  from 
    ticket

  union all

  select 
    dateadd(day, 1, Close_Date), 
    -1
  from 
    ticket  
) TMP

There's a dateadd + 1 day to include the close date amount to that day

SoulTrain · Answer 4 · 2015-02-26T15:34:20.473

You could generate the list of dates and then retrieve the count for each date in your dateset.

The cte part generates the date list since the beginning of the year (an ssumption) and the next part calculates the count from your data set.

with cte as
    (select cast('2015-01-01' as date) dt // you should change this part to the correct start date
    union all
    select dateadd(DD,1,dt) dt from cte
    where dt<getdate()
    )
    select count(*)
    from tickets
    inner join cte
    on cte.dt between Submit_Date and Close_Date
    group by cte.dt

Open Ticket Count Per Day

4 Answers4

Linked