CODEWITHSUNDEEP
sql-server
A.Hayter
A.Hayter

Reputation: 13

SQL: Cumulative of A.field since last value in B.field?

Context - I'm very new to SQL. I get the basics, and have my head around basic joins, but I'm not quite at the point of building my own querys beyond basic selects. I have the following table layout:

Site  Date         Cash_1      Sales_1
4     10/04/2017   £1,500      £1,500
4     09/04/2017   NULL        £1,000
4     08/04/2017   NULL        £2,000
4     07/04/2017   NULL        £1,000
4     06/04/2017   £5,000      £1,000

As you can see, the (example) cumulative sales between 06/04 and 09/04 for this site total the cash amount for 06/04.
This happens when site 4 fails it's overnight upload of it's cash statement - the subsequent days get rolled together in a single statement, which is dated on the first date it failed to upload.

What I'm hoping to try and get is something like the below. The idea is that the cumulative column will show the total sales since the last successful cash statement import, which will help with balance checks.

Site  Date         Cash_1      Sales_1   Cumulative
4     10/04/2017   £1,500      £1,500    £1,500
4     09/04/2017   NULL        £1,000    £5,000
4     08/04/2017   NULL        £2,000    £4,000
4     07/04/2017   NULL        £1,000    £2,000
4     06/04/2017   £5,000      £1,000    £1,000

I can get my head around using "SUM(Y) as X " to create a cumulative column in the standard sense, but I can't work out how to reset the accumulation based on another columns values.

Upvotes: 0

Views: 47

Answers (1)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14679

Try with ROW_NUMBER() AND Update Statement As below:

DECLARE @tblTest as Table(
    SiteNo INT,
    [Date] Date,
    Cash INT,
    Sales INT
)

INSERT INTO @tblTest VALUES(4,'10-Apr-2017',1500,1500)
INSERT INTO @tblTest VALUES(4,'09-Apr-2017',NULL,1000)
INSERT INTO @tblTest VALUES(4,'08-Apr-2017',NULL,2000)
INSERT INTO @tblTest VALUES(4,'07-Apr-2017',NULL,1000)
INSERT INTO @tblTest VALUES(4,'06-Apr-2017',5000,1000)

;With T AS
(
    SELECT
        *,
        NULL AS Cumulative,
        ROW_NUMBER() OVER(ORDER BY Date) AS RowNo
    FROM @tblTest   
)
SELECT
    *
INTO #tblTest
FROM T
ORDER BY RowNo  

DECLARE @sum INT=0

Update #tblTest
SET @sum=Cumulative=Sales+ CASE WHEN Cash IS NULL THEN @sum ELSE 0 END 

SELECT * FROM #tblTest ORDER BY RowNo DESC 

DROP TABLE #tblTest

Upvotes: 1

Related Questions