CODEWITHSUNDEEP
sql-server-2012sum
IFink
IFink

Reputation: 774

reset a running total in same partition based on a condition

To make running total over groups is very easy today with sum over partition.

I have a need to reset the total in the same partition based on a condition, if some field in a row is false, the sum should be reset and begin from this row. In code this is very easy, just loop over the rows and check for the condition. but how can we achieve this in SQL?

Here is a sample, it contains a table with four fields, and a query to sum the running amounts. the sum should be reset if the ResetSum field is true.

CREATE TABLE dbo.Table_1
(
PersonID int NOT NULL,
Amount money NOT NULL,
PayDate date NOT NULL,
ResetSum bit NOT NULL
)

INSERT INTO Table_1 (PersonID, Amount, PayDate, ResetSum) 
VALUES  (1, 100, '2015-1-1', 0)
,(1,200,'2015-1-2',0)
,(1,180,'2015-1-3',0)
,(1,200,'2015-1-4',1)
,(1,200,'2015-1-5',0)
,(1,360,'2015-1-6',0)

SELECT *,SUM(Amount) over(PARTITION BY PersonID ORDER BY PayDate) as SumAmount
FROM Table_1

Desired result should be 760, not 1140.

The records cannot be grouped by the ResetSum field, because if it is true, all the fields below this should be reset though the ResetField in this row is false.

here is a sample of my .net code, it is very simple:

    Public Function SumTest() As Decimal
    Dim lst As New List(Of TestRecords)
    Dim sum As Decimal = 0
    For Each tst As TestRecords In lst
        If tst.ResetSum = true Then
            sum = fcf.Amount
        Else
            sum += fcf.Amount
        End If
    Next
    Return sum
End Function

Upvotes: 3

Views: 3655

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Do a running total on ResetSum in a derived table and use that as a partition column in the running total on Amount.

select T.PersonID,
       T.Amount,
       T.PayDate,
       sum(T.Amount) over(partition by T.PersonID, T.ResetSum 
                          order by T.PayDate rows unbounded preceding) as SumAmount
from (
     select T1.PersonID,
            T1.Amount,
            T1.PayDate,
            sum(case T1.ResetSum 
                  when 1 then 1 
                  else 0 
                end) over(partition by T1.PersonID 
                          order by T1.PayDate rows unbounded preceding) as ResetSum
     from dbo.Table_1 as T1
     ) as T;

SQL Fiddle

Upvotes: 5

Related Questions