Counting on 2 Date columns

Question

So I've got 2 Date Columns, start and end. What I am trying to accomplish is inventory for each day and this will be going back to the beginning of 2020 so that I have these fields:

Start Date, count of new adds that day, count of closed that day, and count of existing open from previous days. My basic data structure I derive is Start Date, End Date, Request Type (if start date = date of report and end date is null then 'New Add', if end date is not null then 'Work Closed' and if Start Date is less than date of report then 'Existing Open'. The problem is that these depend on relativity between the report date and open/close dates. I need to be able to group by a date and give the counts for each day. I tried these 2 solutions and didn't work like I had hoped for as they're slightly different than my scenario. (Count Function on Multiple Columns by Date (SQL Server) and Get count on two different date columns and group by date). When I boils down I need to do a count by each day based on the current date inventory and the existing stuff from the previous day.

My basic data structure is like this and is fake data:

+----+------------+-----------+---+
| ID | StartDate  |  EndDate  |   |
+----+------------+-----------+---+
|  1 | 1/1/2020   |  NULL     |   |
|  2 | 12/1/2019  | 1/1/2020  |   |
|  3 | 1/1/2020   |  1/3/2020 |   |
|  4 | 12/17/2019 | 1/2/2020  |   |
+----+------------+-----------+---+

Expected Result:

+-------------+---------+-----------------+-----------+--+--+------+
| Report Date | NewAdds |    ExistingOpen |    Closed |  |  |      |
+-------------+---------+-----------------+-----------+--+--+------+
| 1/1/2020    |       2 |          1       |         1 |  |  |    |
| 1/2/2020    |       0 |          1      |         1 |  |  |     |
| 1/3/2020    |       0 |          1      |         1 |  |  |     |
+-------------+---------+-----------------+-----------+--+--+------+

Answer 1

set @report_start = '20200101';
set @report_end = '20200103';

select
    d.dt,
    count(case when t.start_dt = d.dt then 1 end) as Adds,
    count(case when d.dt > t.start_dt and d.dt < t.end_dt as Existing,
    count(case when t.end_dt = d.dt then 1 end) as Closed
from T t inner join Dates d on d.dt <= coalesce(t.end_dt, @report_end)
where d.dt between @report_start and @report_end
group by d.dt;

Create a table of dates and join against it. Counting is fairly easy at that point.

This is a bad idea because you need to count up across all dates ever. Also I don't know what null end date means. Apologies if this is sloppy as I typed it on my phone.

Answer 2

This is a begining of a solution that fixes the logic to handle any report date:

If Start_date = report_date and (end_date is null or  end_date > report date) then 'New Add'

if end_date is not null and end_date <= report_date then 'Work Closed'

if Start_Date < report_date  and (end_date is null or end_date > report_date then 'Existing Open'

You need a case expression that will give you one of the three values.

Once you get it working for a single report date, you can generate a range of report dates using this solution and join it with your table: Generate Dates between date ranges

Answer 3

to implement my solution, add a table where I manage the calendar

you have to do several steps to solve the problem:

1. establish which tasks are open for each calendar interval (NewAddTask)
2. calculate total open tasks by interval (TotalNewAddTask)
3. establish which tasks are close for each calendar interval (ClosedTask)
4. calculate total close tasks by interval(TotalClosedTask)
5. calculate a schedule of the interval combination (ExistingOpenCalendar)
6. establish which tasks are Existing Open for each calendar interval (ExistingOpenDetail)
7. calculate total Existing Open tasks by interval(TotalExistingOpenTask)

8. I finally combine all the totals with the calendar

   with NewAddTask as
   (
           SELECT IdCalendar,IdTask
           FROM
           Calendar CROSS JOIN Task 
           where StarDate  between FirstDate and LastDate
   ),
   TotalNewAddTask as
   (
           select IdCalendar,count(IdTask) as Total
           from NewAddTask 
           group by IdCalendar
   ),
   ClosedTask as
   (
           SELECT IdCalendar,IdTask
           FROM
           Calendar CROSS JOIN Task 
           where isnull(CloseDate,'2020-12-31') between FirstDate and LastDate
   ),
   TotalClosedTask as
   (
           select IdCalendar,count(IdTask) as Total
           from ClosedTask 
           group by IdCalendar
   ),
   ExistingOpenCalendar as 
   (
           SELECT 
           Calendarend.IdCalendar ,
           CalendarStart.FirstDate,
           Calendarend.LastDate
           FROM
           Calendar as CalendarStart CROSS JOIN Calendar as Calendarend 
           where 
           CalendarStart.FirstDate<Calendarend.LastDate
   )
   , ExistingOpenDetail as
   (
           select ExistingOpenCalendar.IdCalendar,Task.IdTask 
           from ExistingOpenCalendar CROSS JOIN Task 
           where StarDate  between FirstDate and LastDate
           and not (isnull(CloseDate,'2020-12-31') between FirstDate and LastDate)
           and (CloseDate is null  or (CloseDate < LastDate))
   )
   ,TotalExistingOpenTask  as
   (
           select IdCalendar,count(IdTask) as Total 
           from ExistingOpenDetail
           group by IdCalendar
   )
   select 
   Calendar.IdCalendar,Calendar.FirstDate ,
   isnull(TotalNewAddTask.Total,0)as NewAddTask,
   isnull(TotalClosedTask.Total,0)as ClosedTask,
   isnull(TotalExistingOpenTask.Total,0)as ExistingOpen
   from Calendar 
   left join TotalNewAddTask on Calendar.IdCalendar=TotalNewAddTask.IdCalendar
   left join TotalClosedTask on Calendar.IdCalendar=TotalClosedTask.IdCalendar
   left join TotalExistingOpenTask on Calendar.IdCalendar=TotalExistingOpenTask.IdCalendar
   

this query meets the conditions

in this example you can find example