cumulative and rollup of the data in sql server

AbdulWahab Khan 1 Reputation point
2022-03-25T22:54:03.31+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Viorel 113.7K Reputation points
    2022-03-26T08:27:40.593+00:00

    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
    
    1 person found this answer helpful.

  2. Ronen Ariely 15,101 Reputation points
    2022-03-26T00:17:19.3+00:00

    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
    
    0 comments No comments

  3. AbdulWahab Khan 1 Reputation point
    2022-03-26T05:22:54.247+00:00

    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.

    0 comments No comments

  4. Ronen Ariely 15,101 Reputation points
    2022-03-26T10:50:53.043+00:00

    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

    187057-image.png

    0 comments No comments

  5. LiHong-MSFT 10,046 Reputation points
    2022-03-28T06:21:07.667+00:00

    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

    0 comments No comments