cumulative and rollup of the data in sql server

Question

Am trying to write query in sql server 2014 on the below prdct table , which has the below structure
PRODUCT Date price
p1 10/1/2022 1000
p1 11/1/2022 1000
p1 15/1/2022 1000
p1 12/2/2022 1100
p1 13/2/2022 1100
p1 14/3/2022 1200
p1 30/4/2022 1500

and am expecting the o/p as below -
PRODUCT Date price Total
p1 10/1/2022 1000 1000
p1 11/1/2022 1000 2000
p1 15/1/2022 1000 3000
p1 12/2/2022 1100 4100
p1 13/2/2022 1100 5200
p1 14/3/2022 1200 1200
p1 30/4/2022 1500 1500

The total should get reset to price on that date when the difference in the days exceed by 30days ,
The price should roll-up only when the difference in the days is less than or equal to 30.

Answer 1

Check one of methods:

;
with Q1 as
(
    select *, case when Date > dateadd(month, 1, lag(Date) over ( partition by PRODUCT order by Date)) then Date end as f
    from MyTable
),
Q2 as 
(
    select *, max(f) over ( partition by PRODUCT order by Date) m
    from Q1
)
select PRODUCT, Date, price, sum(price) over ( partition by PRODUCT, m order by Date) Total
from Q2
order by PRODUCT, Date

Answer 2

Good day AbdulWahab and welcome to the QnA forums

below prdct table , which has the below structure

If I may comment... this is NOT a table and it does not present the table structure. We have no idea which data type is used for example and in order to test a solution we will need to create a table and insert the sample data from the text - THISIS YOUR TASK.

In forums whenever you have a question that is related to queries or performance you should provide queries to create the relevant table(s) and to INSERT sample data. Next, you can describe your needs and expected result according to the sample table and data.

This information also call DDL+DML in the scope of forums and many time people will ask the OP to provide DDL+DML

If the following do not solve your needs and then please provide the missing information.

Sample DDL+DML

USE tempdb
GO

------------------------------------ DDL - should be provided by the OP!
DROP TABLE IF EXISTS T
GO
CREATE TABLE T(PRODUCT VARCHAR(2), MyDate Date, price INT)
GO
------------------------------------ DML - should be provided by the OP!
INSERT T(PRODUCT, MyDate, price) 
VALUES
('p1',CONVERT(DATE,'10/1/2022',103),1000),
('p1',CONVERT(DATE,'11/1/2022',103),1000),
('p1',CONVERT(DATE,'15/1/2022',103),1000),
('p1',CONVERT(DATE,'12/2/2022',103),1100),
('p1',CONVERT(DATE,'13/2/2022',103),1100),
('p1',CONVERT(DATE,'14/3/2022',103),1200),
('p1',CONVERT(DATE,'30/4/2022',103),1500)
GO
SELECT * FROM T
GO

The total should get reset to price on that date when the difference in the days exceed by 30days ,

Different between what to what?

Your expected result does not fit to the Different between current date and previous date. Between 2022-01-15 and 2022-02-12 you have only 28 day and yet you start new counting.

At this time, I will assume that you mean to reset ate each new month since this fit the requested result. If this not fit you, then please clarify the rule to reset the counting since your description did not match the requested result

And your answer with the mentioned change:

SELECT PRODUCT, MyDate, price, 
 Total = SUM (price) OVER (partition by PRODUCT, EOMONTH(MyDate) ORDER BY MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM T
GO

Answer 3

Thank you for the response,

Your expected result does not fit to the Different between current date and previous date. Between 2022-01-15 and 2022-02-12 you have only 28 day and yet you start new counting.

Yes, you are correct. I have updated my output in the question that was posted.

This information also call DDL+DML in the scope of forums and many time people will ask the OP to provide DDL+DML

Noted.

Answer 4

Hi,

Yes, you are correct. I have updated my output in the question that was posted.

one row makes it not fit since Feb is a short month and the different between 2022-02-13 and 2022-03-14 is only 29 days which mean it should not be reset at 2022-03-14

Anyway, assuming the description is correct and you mean to reset only and always when the difference is 30 days or more, then this is the solution

;With MyCTE as (  
	SELECT PRODUCT, MyDate, price,   
		Dif_Days = DATEDIFF(Day,LAG(MyDate) OVER (ORDER BY MyDate), MyDate), --This present the differences in days  
		DaysDiff = case   
			when isnull(DATEDIFF(Day,LAG(MyDate) OVER (ORDER BY MyDate), MyDate),0) < 30 then 0  
			else 1  
		end  
	FROM T  
),   
MyCTE2 as (  
	select PRODUCT, MyDate, price  
		, Dif_Days, G = SUM(DaysDiff) OVER (partition by PRODUCT ORDER BY MyDate)  
	from MyCTE  
)  
select PRODUCT, MyDate, price  
	, Total = SUM (price) OVER (partition by PRODUCT, G ORDER BY MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  
	, Dif_Days, G  
from MyCTE2  

I added several more rows to make it more clear how the result behaves, so this is the DDL+DML I used:

USE tempdb  
GO  
  
------------------------------------ DDL - should be provided by the OP!  
DROP TABLE IF EXISTS T  
GO  
CREATE TABLE T(PRODUCT VARCHAR(2), MyDate Date, price INT)  
GO  
------------------------------------ DML - should be provided by the OP!  
INSERT T(PRODUCT, MyDate, price)   
VALUES  
('p1',CONVERT(DATE,'10/1/2022',103),1000),  
('p1',CONVERT(DATE,'11/1/2022',103),1000),  
('p1',CONVERT(DATE,'15/1/2022',103),1000),  
('p1',CONVERT(DATE,'12/2/2022',103),1100),  
('p1',CONVERT(DATE,'13/2/2022',103),1100),  
('p1',CONVERT(DATE,'14/3/2022',103),1200),  
('p1',CONVERT(DATE,'30/4/2022',103),1500),  
  
('p1',CONVERT(DATE,'22/5/2022',103),1500),  
('p1',CONVERT(DATE,'30/6/2022',103),1500),  
('p1',CONVERT(DATE,'22/7/2022',103),1500),  
('p1',CONVERT(DATE,'30/7/2022',103),1500),  
('p1',CONVERT(DATE,'30/11/2022',103),1500)  
GO  
SELECT * FROM T  
GO  

The result set fits you description

Answer 5

Hi @AbdulWahab Khan
Please also check this:

;WITH CTE1 AS  
(  
SELECT *,DATEDIFF(DAY,LAG(MyDate,1,MyDate)OVER(ORDER BY MyDate),MyDate) AS Date_Diff  
FROM #Your_Table  
),CTE2 AS  
(  
SELECT *,SUM(Date_Diff/31)OVER(PARTITION BY PRODUCT ORDER BY MyDate)AS PART  
FROM CTE1  
)  
SELECT PRODUCT,MyDate,price, SUM(price)OVER(PARTITION BY PRODUCT,PART ORDER BY MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS Total  
FROM CTE2  

Best regards,
LiHong