Sum field with where clause based on a RecordId

Question

Sum field with where clause based on a RecordId

SSCertifiable

Points: 5887

August 18, 2021 at 6:58 am

I'm going to blush when I hear the answer to this. Think I'm over tired. I made this simple example that hopefully explains my problem. Thanks!

DESIRED RESULT (line in italics)

1, 20210806, 200, 0, 0

2, 20210807, 200, 0, 0

3, 20210808, 200, 600, 3

4, 20210809, 200, 0, 0

5, 20210810, 200, 400, 2

6, 20210811, 200, 0, 0

7, 20210812, 200, 0, 0

8, 20210813, 200, 0, 0

9, 20210814, 200, 800, 4

10, 20210815, 200, 0, 0

11, 20210816, 200, 0, 0

12, 20210817, 200, 600, 3

DROP TABLE IF EXISTS #test
CREATE TABLE #test
(RecordId int, MyDate varchar(8), Amount int, AmountAll int, NumDays int)
INSERT INTO #test
SELECT 1, 20210806, 200, 0, 0 UNION ALL
SELECT 2, 20210807, 200, 0, 0 UNION ALL
SELECT 3, 20210808, 200, 0, 3 UNION ALL
SELECT 4, 20210809, 200, 0, 0 UNION ALL
SELECT 5, 20210810, 200, 0, 2 UNION ALL
SELECT 6, 20210811, 200, 0, 0 UNION ALL
SELECT 7, 20210812, 200, 0, 0 UNION ALL
SELECT 8, 20210813, 200, 0, 0 UNION ALL
SELECT 9, 20210814, 200, 0, 4 UNION ALL
SELECT 10, 20210815, 200, 0, 0 UNION ALL
SELECT 11, 20210816, 200, 0, 0 UNION ALL
SELECT 12, 20210817, 200, 0, 3 

UPDATE #test SET AmountAll = B.AmountAll
FROM #test A
JOIN (SELECT RecordId, SUM(Amount) as AmountAll
FROM #test 
WHERE RecordId BETWEEN (RecordId - NumDays + 1) AND RecordId
GROUP BY RecordId) B
ON A.RecordId = B.RecordId

SELECT * FROM #test

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

I think that this update should do it:

update #test
set AmountAll = Amount * NumDays
where NumDays > 0
go

Adi

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

WITH Ranges AS (
SELECT (RecordId - NumDays + 1) As Start, RecordId
FROM #test 
WHERE NumDays > 0)
UPDATE #test SET AmountAll = B.AmountAll
FROM #test A
JOIN (SELECT r.RecordId, SUM(t.Amount) as AmountAll
FROM #test t
INNER JOIN Ranges r ON t.RecordId BETWEEN r.Start AND r.RecordId
GROUP BY r.RecordId) B
ON A.RecordId = B.RecordId

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

texpic SSCertifiable Points: 5887 More actions · Answer 3

texpic

SSCertifiable

Points: 5887

August 18, 2021 at 10:49 am

#3921213

Mark, perfect, thank you!

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

Another alternative could be CROSS APPLY

update t
set AmountAll = ca.sum_amount
from #test t
     cross apply (select sum(tt.Amount)
                  from #test tt
                  where tt.RecordId between (t.RecordId - t.NumDays + 1) 
                                    and t.RecordId) ca(sum_amount)
where t.NumDays>0;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können