Sum Of last 6 rows for same day of week to predict the num for next day of week

Question

Hi

I need help to write the sql code to sum last 6 weeks for same day of the week and predict the number for next day of week.

for eg:- sum of num of last 6 (2023-06-06,2023-05-30,2023-05-23,2023-05-16,2023-05-09,2023-05-02) rows for 2023-06-13

please find the below data

create table #t (dy varchar(10), dt datetime, code varchar(4), wd varchar(10), num int)
insert into #t values
('Wednesday', '2023-03-01','c','101A',5),
('Wednesday', '2023-03-08','c','101A',3),
('Wednesday', '2023-03-15','c','101A',1),
('Wednesday', '2023-03-22','c','101A',1),
('Wednesday', '2023-03-29','c','101A',3),
('Wednesday', '2023-04-05','c','101A',2),
('Wednesday', '2023-04-12','c','101A',5),
('Wednesday', '2023-04-19','c','101A',2),
('Wednesday', '2023-04-26','c','101A',0),
('Wednesday', '2023-05-03','c','101A',6),
('Wednesday', '2023-05-10','c','101A',4),
('Wednesday', '2023-05-17','c','101A',3),
('Wednesday', '2023-05-24','c','101A',5),
('Wednesday', '2023-05-31','c','101A',1),
('Wednesday', '2023-05-07','c','101A',0),
('Wednesday', '2023-03-01','c','102A',4),
('Wednesday', '2023-03-08','c','102A',6),
('Wednesday', '2023-03-15','c','102A',4),
('Wednesday', '2023-03-22','c','102A',3),
('Wednesday', '2023-03-29','c','102A',6),
('Wednesday', '2023-04-05','c','102A',2),
('Wednesday', '2023-04-12','c','102A',1),
('Wednesday', '2023-04-19','c','102A',2),
('Wednesday', '2023-04-26','c','102A',3),
('Wednesday', '2023-05-03','c','102A',2),
('Wednesday', '2023-05-10','c','102A',5),
('Wednesday', '2023-05-17','c','102A',0),
('Wednesday', '2023-05-24','c','102A',1),
('Wednesday', '2023-05-31','c','102A',1),
('Wednesday', '2023-06-07','c','102A',0),
('Tuesday', '2023-02-28','d','101A',2),
('Tuesday', '2023-03-07','d','101A',3),
('Tuesday', '2023-03-14','d','101A',8),
('Tuesday', '2023-03-21','d','101A',10),
('Tuesday', '2023-03-28','d','101A',3),
('Tuesday', '2023-04-04','d','101A',2),
('Tuesday', '2023-04-11','d','101A',5),
('Tuesday', '2023-04-18','d','101A',2),
('Tuesday', '2023-04-25','d','101A',4),
('Tuesday', '2023-05-02','d','101A',6),
('Tuesday', '2023-05-09','d','101A',3),
('Tuesday', '2023-05-16','d','101A',4),
('Tuesday', '2023-05-23','d','101A',5),
('Tuesday', '2023-05-30','d','101A',1),
('Tuesday', '2023-06-06','d','101A',6),
('Tuesday', '2023-02-28','cd','103',5),
('Tuesday', '2023-03-07','cd','103',5),
('Tuesday', '2023-03-14','cd','103',4),
('Tuesday', '2023-03-21','cd','103',1),
('Tuesday', '2023-03-28','cd','103',3),
('Tuesday', '2023-04-04','cd','103',2),
('Tuesday', '2023-04-11','cd','103',4),
('Tuesday', '2023-04-18','cd','103',2),
('Tuesday', '2023-04-25','cd','103',0),
('Tuesday', '2023-05-02','cd','103',3),
('Tuesday', '2023-05-09','cd','103',2),
('Tuesday', '2023-05-16','cd','103',1),
('Tuesday', '2023-05-23','cd','103',5),
('Tuesday', '2023-05-30','cd','103',1),
('Tuesday', '2023-06-06','cd','103',0)
select * From #t
create table #t_result (dy varchar(10), dt datetime, code varchar(4), wd varchar(10), num int)
insert into #t_result values
('Tuesday', '2023-06-13','cd','103',12),
('Tuesday', '2023-06-06','cd','103',12),
('Tuesday', '2023-05-30','cd','103',13),
('Tuesday', '2023-05-23','cd','103',12),
('Tuesday', '2023-05-16','cd','103',13),
('Tuesday', '2023-05-09','cd','103',14),
('Tuesday', '2023-05-02','cd','103',12),
('Tuesday', '2023-06-13','d','101A',25),
('Tuesday', '2023-06-06','d','101A',23),
('Tuesday', '2023-05-30','d','101A',24),
('Tuesday', '2023-05-23','d','101A',24),
('Tuesday', '2023-05-16','d','101A',22),
('Tuesday', '2023-05-09','d','101A',22),
('Tuesday', '2023-05-02','d','101A',26)

Answer 1

Hi @leo dec

Not sure about the predict logic. Is it based on the average, mode or median of the last six same weekdays?

Here is a query assuming that next value is predicted based on the average of last six days.

;WITH CTE1 AS
(
 SELECT dy,DATEADD(WEEK,1,MAX(dt))AS dt,code,wd,AVG(num) AS num
 FROM #t
 GROUP BY dy,code,wd
 UNION
 SELECT * FROM #t
),CTE2 AS
(
 SELECT dy,dt,code,wd
       ,ROW_NUMBER()OVER(PARTITION BY dy,code,wd ORDER BY dt DESC) AS RNum
       ,SUM(num)OVER(PARTITION BY dy,code,wd ORDER BY dt ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS num 
 FROM CTE1
 WHERE dy='Tuesday'
)
SELECT * FROM CTE2
WHERE RNum <= 6
ORDER BY code,wd,dt DESC

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.