Iterate through dates in SQL Server query

Question

I'm working on an application for a law firm. What I'm trying to do is to run a query that will tell me the number of cases that were open on a given day for a date range.

For example, I want to know how many auto accident cases we had each day since the start of the year. New cases are signed up every day and cases are resolved and closed every day so the total number will fluctuate from day to day. The goal is to be able to see whether our inventory of cases is trending up or down for a given period of time.

Here is the query I'm working with right now:

SELECT
  COUNT(case_id) as total,
  SUM(CASE WHEN x.type in (1) THEN 1 ELSE 0 END) as total_auto_cases,
  SUM(CASE WHEN x.type in (437) THEN 1 ELSE 0 END) as total_dog_bite_cases,
  SUM(CASE WHEN x.type in (1632) THEN 1 ELSE 0 END) as total_slip_and_fall_cases,
  SUM(CASE WHEN x.type in (1, 437, 1632) THEN 1 ELSE 0 END) as total_pi_cases
FROM (
         SELECT DISTINCT ca.casnCaseID as case_id, ca.cassCaseNumber as case_number, ca.casnOrgCaseTypeID as type
         FROM sma_TRN_Cases ca WITH (NOLOCK)
                  LEFT JOIN sma_Vu_CaseDetails cd WITH (NOLOCK) ON ca.casnCaseID = cd.casnCaseID
                  LEFT JOIN sma_VU_Mrg_Retainer mr WITH (NOLOCK) ON ca.casnCaseId = mr.CaseID
         WHERE (ca.casdClosingDate IS NULL OR CAST(ca.casdClosingDate as DATE) > '2021-06-24')
           AND CAST(ca.casdOpeningDate as DATE) < '2021-06-24'
           AND mr.rtndRcvdDt IS NOT NULL
     ) x

This query will tell me the stats I need for the specific date plugged in (e.g. '2021-06-24'). I'd like to iterate through a series of dates and plug the date into the spot where '2021-06-24' is so that I can get a result set back that looks like:

date	total	total_auto_cases	total_dog_bite_cases	total_slip_and_fall_cases	total_pi_cases
2021-06-01	2567	2146	20	74	2240
2021-06-02	2660	2250	25	80	2355

And on and on for the date range.

I have a Calendar table that I use for other queries called DimDate. However, I don't know how to iterate through a date range and plug it into the query above. Hoping someone here can help me figure that out. Alternatively, I can just do it in the application code but was hoping there was a way to do it in the query.

Answer 1

If you have a calendar table you are most of the way there! The point to realise is that, with a relational database, you don't iterate over rows in SQL. Rather, try to think of all the rows in the table as a whole, and from this whole you want to carve the portion which satisfy a query's particular needs.

You have a calendar table. From this you can get the days in which you're interested with a simple WHERE on the range. Then you can join to the other tables and GROUP BY to create the totals. I'll show the whole query and explain after.

drop table if exists #Cases;

create table #Cases(CaseId int, StartDate date, EndDate date, Type int);

insert #Cases(CaseId, StartDate, EndDate, Type)
values (1, '2021-02-14', '2021-02-19', 1),
       (2, '2021-02-16', '2021-02-21', 1),      -- partially overlaps the first
       (3, '2021-02-16', '2021-02-21', 437),    -- same dates different type
       (4, '2021-02-23', '2021-02-25', 1632);   -- no overlap with another

declare @StartRange date = '2021-02-12';    -- before first case
declare @EndRange   date = '2021-02-27';    -- after the last

select
    d.CalendarDate,
    COUNT(c.CaseId) as total            --(9)
    -- all the SUM(CASE ..) go here
from dbo.Calendar d                     --(1)
left outer join #Cases as c             --(2),(3)
    on  c.StartDate <= d.CalendarDate   --(4),(5)
    and c.EndDate   >= d.CalendarDate
where d.CalendarDate >= @StartRange     --(6)
and   d.CalendarDate <= @EndRange
group by d.CalendarDate                 --(7)
order by d.CalendarDate;                --(8)

drop table if exists #Cases;

Points to note

1. My calendar table is differently named. Same idea, though.

2. This must be an OUTER JOIN if we want a row for every date in our range, even if there are no cases.

3. I use #Cases as a place-holder for your inner query. You will be able to drop it in with little change other than removing the checks on the specific date.

4. I've shown the case dates as inclusive. Change the comparison operators to suit your system's conventions.

5. By joining on the case dates we're effectively saying "for each CalendarDate, find all the cases which are active on this CalendarDate."

6. This limits the range you are reporting on. Compare on the calendar table rather than case data so empty overlaps at the start and end are returned. (I know it's unlikely, but still.)

7. GROUP BY returns one row per CalendarDate.

8. Likely we want the results in chronological sequence so an ORDER BY is required to guarantee this.

9. If we COUNT(*) we'll simply count the number of days per day (i.e. 1). Using COUNT(CaseId) eliminates the NULLs on days when there are no cases, returning 0.

This returns the expected output

CalendarDate total
------------ -----------
2021-02-12   0
2021-02-13   0
2021-02-14   1
2021-02-15   1
2021-02-16   3
2021-02-17   3
2021-02-18   3
2021-02-19   3
2021-02-20   2
2021-02-21   2
2021-02-22   0
2021-02-23   1
2021-02-24   1
2021-02-25   1
2021-02-26   0
2021-02-27   0