How to use recurring CTE for Time Series-CodePudding

I have a table which shows the following data

Called Declines

date	accountId	declineReason
04/10/22	1344	Not enough funds
05/10/22	1222	Incorrect Password
05/10/22	1677	timeout
06/10/22	1222	Incorrect Password
07/10/22	1677	timeout
07/10/22	1222	Incorrect Password
10/10/22	1677	timeout
11/10/22	1344	Incorrect Password
11/10/22	1222	Incorrect Password
12/10/22	1677	timeout
13/10/22	1222	Incorrect Password
15/10/22	1677	timeout
15/10/22	1222	Incorrect Password
17/10/22	1677	timeout

etc (note declineReasons may increase in terms of distinct values and is not limited to 3 shown)

what I would like to have as my final table is the below

WeekEnd	declineReason	CountOfReasonPerWeek
10/10/22	Not enough funds	1
10/10/22	Incorrect Password	3
10/10/22	timeout	3
17/10/22	Not enough funds	0
17/10/22	Incorrect Password	4
17/10/22	timeout	3

I have created the week loop and table which counts no. of total declines


WITH date_loop AS (
            SELECT
                CAST('2022-08-01' AS DATE) AS WeekEnd,   
                CAST('2022-08-02' AS DATE) AS BeforeDate,
                CAST('2022-07-25' AS DATE) AS AfterDate 
        UNION ALL
            SELECT
                DATEADD(ww, 1, WeekEnd),
                DATEADD(ww, 1, BeforeDate),
                DATEADD(ww, 1, AfterDate)
            FROM date_loop
            WHERE BeforeDate < DATEADD(ww, -1, GETDATE())
    ),

CountOfDDeclineByReasonAllTime AS (
    SELECT
        count(*) AS totalDeclinesPerReasonCode
    FROM
        Declines
    GROUP BY
        declineReason),

But I am unsure how I can use a correlated subquery to get values returned in the format as desired, if anyone has any pointers that would be great

CodePudding user response：

data

CREATE TABLE Declines(
   date          VARCHAR(100)  NOT NULL
  ,accountId     INTEGER  NOT NULL
  ,declineReason VARCHAR(100) NOT NULL
);
INSERT INTO Declines
(date,accountId,declineReason) VALUES
('04/10/22',1344,'Not enough funds'),
('05/10/22',1222,'Incorrect Password'),
('05/10/22',1677,'timeout'),
('06/10/22',1222,'Incorrect Password'),
('07/10/22',1677,'timeout'),
('07/10/22',1222,'Incorrect Password'),
('10/10/22',1677,'timeout'),
('11/10/22',1344,'Incorrect Password'),
('11/10/22',1222,'Incorrect Password'),
('12/10/22',1677,'timeout'),
('13/10/22',1222,'Incorrect Password'),
('15/10/22',1677,'timeout'),
('15/10/22',1222,'Incorrect Password'),
('17/10/22',1677,'timeout');

query first since your value is not date change to date by using Convert(date, column, 3) second get the last day of week by using DATEADD(dd, 7-(DATEPART(dw, column)), column) as WeekEnd third use Subquery to have better understanding of using Count function

select WeekEnd,
       declineReason,
       Count(declineReason) CountOfReasonPerWeek
from   (select Dateadd(dd, 7 - ( Datepart(dw, Convert(date, date, 3)) ),
                      Convert(date, date, 3)
               ) WeekEnd,
               declineReason
        from   Declines) a
group  by declineReason,
          WeekEnd

dbfiddle