How to display data by rolling day for three months when data has datestamp for state change by colu-CodePudding

I have data formatted like this:

ID	Date Created	Date Ready to Start	Date In Progress	Date Dev Complete	Date Test Complete	Date Accepted
1	2021-11-01 12:01:15	2021-11-02 14:01:15	2021-11-04 05:01:15	2021-11-04 12:01:15	2021-11-05 12:01:15	2021-11-06 12:01:15
2	2021-11-01 12:01:45	NULL	2021-11-03 12:01:15	NULL	NULL	2021-11-05 12:01:15
3	2021-11-03 11:11:05	2021-11-04 12:01:15	NULL	NULL	NULL	NULL
4	2021-11-05 19:31:45	2021-11-05 12:01:15	2021-11-06 12:01:15	NULL	NULL	NULL
5	2021-11-04 13:21:25	NULL	NULL	NULL	NULL	NULL

and I need it formatted like this:

Date	Created	Ready to Start	In Progress	Dev Complete	Test Complete	Accepted
2021-11-01	2	0	0	0	0	0
2021-11-02	0	1	0	0	0	0
2021-11-03	1	0	1	0	0	0
2021-11-04	0	1	1	1	0	0
2021-11-05	1	1	0	0	1	1
2021-11-06	0	0	1	0	0	1

with rolling dates going back 3 months from current date.

I am unsure of how to start this... Would I union a recursive table creating the rolling calendar to the existing data or would I do a series of custom selects for counts and then group by date?

CodePudding user response：

Try something like this:

DECLARE @Today date = GetDate();
DECLARE @MinDate date = DateAdd(month, -3, @Today);
DECLARE @DayCount int = 1   DateDiff(day, @MinDate, @Today);

WITH cteNumbers As
(
    /* Use a tally-table here if you have one: */
    SELECT TOP (@DayCount)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N
    FROM
        sys.all_columns
),
cteCalendar As
(
    SELECT
        DateAdd(day, N, @MinDate) As [Date],
        DateAdd(day, N   1, @MinDate) As NextDay
    FROM
        cteNumbers
)
SELECT
    C.[Date],
    (
        SELECT Count(1) 
        FROM YourTable As T 
        WHERE T.[Date Created] >= C.[Date]
        And T.[Date Created] < C.NextDay
    ) As [Created],
    (
        SELECT Count(1) 
        FROM YourTable As T 
        WHERE T.[Date Ready to Start] >= C.[Date]
        And T.[Date Ready to Start] < C.NextDay
    ) As [Ready to Start],
    ...
FROM
    cteCalendar As C
;