Sum field with where clause based on a RecordId

  • 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
  • I think that this update should do it:

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

    Adi

  • 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
  • Mark, perfect, thank you!

  • 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

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

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