CODEWITHSUNDEEP
sqlsql-serverselectsumwindow-functions
mecnism
mecnism

Reputation: 195

SUM column with previous column

I have the following table:

+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
| InvoiceId |       InvoiceDate       | ClientId | TotalPayment | CurrentInvoicePoints  | TotalPoints |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
|         1 | 2016-09-28 00:00:00.000 |        10 |      2050.14 |                   136 | NULL        |
|         9 | 2016-12-28 00:00:00.000 |        10 |       772.31 |                    58 | NULL        |
|        25 | 2017-02-11 00:00:00.000 |        10 |       168.51 |                    17 | NULL        |
|        32 | 2017-02-21 00:00:00.000 |        20 |       758.27 |                    80 | NULL        |
|        24 | 2017-02-08 00:00:00.000 |        20 |      4493.45 |                   718 | NULL        |
|         8 | 2016-10-08 00:00:00.000 |        20 |      7049.08 |                   483 | NULL        |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+

Each invoice has a number of points: [CurrentInvoicePoints]
In [TotalPoints] I have the sum all the points ([CurrentInvoicePoints] + previous [TotalPoints])
Example: if the first invoice from clientId 10 has 136 points the next one will have 136 + [CurrentInvoicePoints] and so on.

Therefore the result should look like this:

+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
| InvoiceId |       InvoiceDate       | PartnerId | TotalPayment | CurrentInvoicePoints  | TotalPoints |
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+
|         1 | 2016-09-28 00:00:00.000 |        10 |      2050.14 |                   136 |         136 |  =136 (first invoice for clientId 10)
|         9 | 2016-12-28 00:00:00.000 |        10 |       772.31 |                    58 |         194 |  =136+58
|        25 | 2017-02-11 00:00:00.000 |        10 |       168.51 |                    17 |         211 |  =194+17
|        32 | 2017-02-21 00:00:00.000 |        20 |       758.27 |                    80 |          80 |  =80 (first invoice for clientId 20)
|        24 | 2017-02-08 00:00:00.000 |        20 |      4493.45 |                   718 |         798 |  =80+718
|         8 | 2016-10-08 00:00:00.000 |        20 |      7049.08 |                   483 |        1281 |  =718+483
+-----------+-------------------------+-----------+--------------+-----------------------+-------------+

Need some help, hopefully I'll post a query as soon as I'll find an approach

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222422

You are describing a window sum:

select
    t.*,
    sum(CurrentInvoicePoints) 
        over(partition by PartnerId order by InvoiceDate) as TotalPoints
from mytable t

You should not need to actually store this derived value. But if you really want an update statement, then you can turn the query to an updatable cte:

with cte as (
    select
        totalPoints,
        sum(CurrentInvoicePoints) 
            over(partition by PartnerId order by InvoiceDate) as NewTotalPoints
    from mytable
)
update cte set TotalPoints = NewTotalPoints

Upvotes: 1

Related Questions