Our database stores a history of our cases and what stages they passed through, with start and end dts for each.
So the SQL table has CaseID, Step, StartDt, EndDt.
Each case will have a number of rows (between 1 and 7) each with a different Step.
The difference between startdt and enddt may be less than one month or many months, depending on the case.
I need to be able to work out, for the first day of each month, how many cases (e..g distinct caseIDs) were "in" each step (i.e the start of the month was >= startdt and < enddt.
Any suggestions? I've tried various combinations of set analysis and if statements, alongside a generic calendar with month dates in, but can't get what I'm looking for.
