TSQL Running Total but only based on Logic

Question

Just been tasked to work on an old system that's running on SQL2008r2.

Need to audit the number of reserv earned and compare against the number of ticks taken.

So here's a sample dataset - Expected Result is the column I want to get.

Date        EmpId   Reserv      Booked      Expected
20160201    100     10.00       -11.00      -1.00 
20160301    100     5.00        -6.00       -2.00 
20170201    100     6.00        -1.00       3.00
20170301    100     10.00       0.00        10.00
20180201    100     5.00        -4.00       1.00
20180301    100     50.0        -45.00      5.00
20190201    100     0.00        -1.00       -1.00
20190301    100     5.50        0.00        4.50

Answer 1

What if you do it the simple way?

;WITH cteDATA
AS(
 SELECT [ReqId]=1,[Date]='20160613',HrsEarned=10,HrsBooked=-31.7,[EmpId]=100 UNION ALL
 SELECT 2,'20160713', 52.7,-20,100 UNION ALL
 SELECT 3,'20170613',1,-11.5,100 UNION ALL
 SELECT 4,'20170713',16.7,0,100  UNION ALL
 SELECT 5,'20180613',5,-16.7,100 UNION ALL
 SELECT 6,'20180713',112.7,-100,100 UNION ALL
 SELECT 7,'20190613',0,-0.5,100  UNION ALL
 SELECT 8,'20190713',6.7,0,100
),cteAddRN
AS(
    SELECT 
        RN = ROW_NUMBER() OVER
        (
         PARTITION BY C.EmpId
         ORDER BY C.[Date]
        )
        ,C.ReqId
        ,C.[Date]
        ,C.HrsEarned
        ,C.HrsBooked
        ,C.EmpId
    FROM 
        cteDATA  C
)
SELECT 
    EmpID, Date, 
    SUM(HrsEarned-HrsBooked) totals
from cteDATA
group by EmpId, Date

output:

EmpID   Date    totals
100 20160613    41.7
100 20160713    72.7
100 20170613    12.5
100 20170713    16.7
100 20180613    21.7
100 20180713    212.7
100 20190613    0.5
100 20190713    6.7

It think the value at the end should be 6.7, also in the method you do not have implemented yet?

After your response, I think I do miss some information (or I misinterpreted).

when you write:

100     20160713    40.7        -20.0       -1.0  --the running total here is < 0
100     20170613    1.0         -11.5       -10.5 --Expected here should be -11.5

You almost state that the '-1.0' does not count, And you claim you are missing -1.0 on the next row.

This 'missing' piece is the difference between 6.7 and 6.2. Whatever the right answer is, the definition of the problem makes it hard to guess.