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.