CODEWITHSUNDEEP
sqlsql-serverperformancecumulative-sum
DineshDB
DineshDB

Reputation: 6193

Preventing running total from going Negative - SQL Server

I have a table with the following values;

Earnings Penalties
1000     500
0        500
0        100
0        100
500      100
400      500

Whenever the cumulative sum (Earnings-Penalty) value greater than or equal to 0, then only the penalty will be applied. Otherwise it should be zero.

I don't need any query with Cursor or While loop. I need some query with high performance.

I want the result like below,

AttDay        Earnings Penalties IsPenaltyApplicable Reason
2019-05-01    1000     500       Yes                 (1000-500=500) Remaining 500
2019-05-02    0        500       Yes                 (500-500=0) Remaining 0
2019-05-03    0        100       Yes                 (0-100=-100) Remaining -100
2019-05-04    0        100       No                  0
2019-05-05    500      100       Yes                 (500-100=400) Remaining 400
2019-05-06    400      500       Yes                 (400-500=-100) Remaining -100

Upvotes: 1

Views: 473

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use cumulative approach : 

select pt.*, 
      sum(case when IsPenaltyApplicable = 1 then Earnings - Penalties else 0 end) over (partition by grp order by AttDay)
from (select t.*, sum(case when IsPenaltyApplicable = 0 then 1 else 0 end) over (order by AttDay) as grp
      from table t
     ) pt;

Upvotes: 0

Fleshy
Fleshy

Reputation: 182

CREATE TABLE #penaltytest
    (
    Earnings NUMERIC(7,2),
    Penalties NUMERIC(7,2),
    AttDate DATE
    )

    INSERT INTO #penaltytest VALUES (1000,500, '01 Jan 2000')
    INSERT INTO #penaltytest VALUES (0,500, '02 Jan 2000')
    INSERT INTO #penaltytest VALUES (0,100, '03 Jan 2000')
    INSERT INTO #penaltytest VALUES (0,100, '04 Jan 2000')
    INSERT INTO #penaltytest VALUES (500,100, '05 Jan 2000')
    INSERT INTO #penaltytest VALUES (400,500, '06 Jan 2000')

;with cte as(
select
            AttDate,
            Earnings,
            Penalties,
            Earnings - Penalties as subTotal,           
            (select 
                        isnull(sum(Earnings - Penalties),0)
                from 
                        #penaltytest previousRow
                where
                        previousRow.AttDate < currentRow.AttDate                
            ) as cumulative
    from 
            #penaltytest currentRow 
)
select
            t.AttDate
            ,t.Earnings
            ,t.Penalties
            ,cte.subTotal
            ,cte.cumulative
            ,case when cte.cumulative - (t.Earnings - t.Penalties) >= 0 then 'Yes' else 'No' end as Penalty
    from #penaltytest t
        join cte
            on cte.AttDate = t.AttDate
drop table #penaltytest



Results:
AttDate Earnings    Penalties   subTotal    cumulative  Penalty
2000-01-01  1000.00 500.00  500.00  0.00    No
2000-01-02  0.00    500.00  -500.00 500.00  Yes
2000-01-03  0.00    100.00  -100.00 0.00    Yes
2000-01-04  0.00    100.00  -100.00 -100.00 Yes
2000-01-05  500.00  100.00  400.00  -200.00 No
2000-01-06  400.00  500.00  -100.00 200.00  Yes

Upvotes: 1

Ketan Kotak
Ketan Kotak

Reputation: 957

CASE.. WHEN.. will work for the same. 

CREATE TABLE #penaltytest
    (
    Earnings NUMERIC(7,2),
    Penalties NUMERIC(7,2)

    )

    INSERT INTO #penaltytest VALUES (1000,500)
    INSERT INTO #penaltytest VALUES (0,500)
    INSERT INTO #penaltytest VALUES (0,100)
    INSERT INTO #penaltytest VALUES (0,100)
    INSERT INTO #penaltytest VALUES (500,100)
    INSERT INTO #penaltytest VALUES (400,500)

    SELECT *,CASE WHEN (Earnings-Penalties) >0 THEN 'Yes' ELSE 'No' END AS 'IsPenaltyApplicable',CASE WHEN (Earnings-Penalties) >0 THEN (Earnings-Penalties) ELSE 0 END AS PenaltyAmount  FROM #penaltytest

Upvotes: 1

Related Questions