SQL Server 2008 Cumulative Running Total

0
votes

I need to calculate a running total for overtime payments enter image description here

The difference column calculates the difference between the ContractedHours and the Hours_delivered_in_period

In example above, they worked "-4" hours in period 2 and "+1" hour overtime in period 10.
This should give an overall total of -3. Then in period 11, they worked "9.5" hours overtime but that should bring them back into positive "+6.5"

Once they back in positive hours overall, then it should reset back to 0 as they shouldn't accumulate positive hours as they are paid each month, hence they shouldnt be "kept", so they always reset back to 0.

They may then have negative difference in period 12 and the postive hours from the period 11 calculation shouldn't count towards the total, hence the hours should go back to 0 once the hours are positive

How can I calculate this running "negative" total in SQL server 2008 to track undertime?

EDIT - UPDATE

Here is the example column that I would like to do in SQL that I have done in excel and I would like to know how to do that column in SQL called "Are they Undertime"

The Excel forumula is "=IF(OR(H2<0,H3<0),H2+I3)"

enter image description here

My SQL attempt is below, however it doesnt add the difference and it stays at "-4" and doesn't add on the next value.

SELECT  T1.* ,
    T2.RunningSum
FROM    Staff_Tracked_Hours As T1
    CROSS APPLY ( SELECT    sum(differencefromzero) AS RunningSum
                  FROM      dbo.Staff_Tracked_Hours AS CAT1
                  WHERE     ( CAT1.Id <= T1.Id and CAT1.personID ='000033485' )
                ) AS T2

                where t1.personID ='000033485'
3
c#sqlsql-serversql-server-2008cumulative-sum
You need a sql AFTER trigger to update the column in question. - Amit Kumar Singh
Leave just few columns (the ones that are used in your query), generate INSERT statements and provide expected result - Dmitrij Kultasev
please post expected results and actual results as text (not images).Also try sharing your query attempt if any - TheGameiswar
Updated with more details - David Hayward

3 Answers

0
votes

If I understand the question right, you are asking to get the sum of the difference column where it's negative. In that case, this query should do the trick:

Select personID, SUM(difference)
From [table_name]
Where difference < 0
Group by personID
0
votes

Using a window function:

with CTE as
(
select id, personid, year, period, 
       sum(differencefromzero) over (partition by personid order by year, period) as cumu_total
from MyTable
)

select id,  personid, year, period, 
       case when cumu_total < 0 then cumu_total else 0 end as new_difference
from CTE

Note... This assumes differencefromzero goes positive as well as negative.