Conditional SUM in SQL Server 2014

Question

I am using SQL Server 2014. When I was testing my code I noticed a problem. Assume that max personal hour is 80 hours.

SELECT    
    lsm.EmployeeName,
    pd.absenceDate,
    pd.amountInDays * 8 AS [HoursReported],
    pd.status,
    (SUM(CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
              ELSE 0 END) OVER (partition by lsm.[EmployeeName] order by pd.absenceDate)) AS [TotalUsedHours]
          ( @maxPSHours ) - (sum(
              CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
                   ELSE 0 END)
          over (
              partition by  lsm.[EmployeeName] order by pd.absenceDate)) AS [TotalRemainingHours]
FROM    
    [LocationStaffMembers] lsm 
INNER JOIN 
    [PersonalDays] pd ON lsm.staffMemberId = pd.staffMemberId

This query returns these results:

EmployeeName AbsenceDate HoursReported   Status     TotalUsdHrs  TotalRemingHrs
 X            11/11/2015       4         approved     4             76
 X            11/15/2015       8         approved     12            68
 X            11/20/2015       2         decline      14            66
 X            11/20/2015       2         approved     14            66

So, query works fine for different status. First 2 rows are fine. But when an employee does more than one action in a day (decline, approved etc.), my query only shows the total used and total remaining for the day.

Here is the expected result.

EmployeeName AbsenceDate HoursReported   Status     TotalUsdHrs  TotalRemingHrs
 X            11/11/2015       4         approved     4             76
 X            11/15/2015       8         approved     12            68
 X            11/20/2015       2         decline      12            68
 X            11/20/2015       2         approved     14            66

sstan · Accepted Answer · 2015-12-16 18:44:34Z

2

You are doing a cumulative sum that returns results based on the order of AbsenceDate (sum(...) over (partition by ... order by pd.absenceDate). But your last 2 records have the exact same date (11/20/2015) -- at least, according to what you are showing us. This creates an ambiguity.

So, it is absolutely conceivable, and legal, that SQL Server is processing the 2 approved hours row before the 2 declined hours row when calculating the cumulative sum --which would explain your current results--, despite the fact that rows themselves are returned to you in a different order (BTW, consider adding an order by clause to the query, otherwise, the order of the rows themselves are not guaranteed).

If the 2 rows do in fact share the exact same date, you'll have to find a 2nd column to remove the ambiguity and add that to the order by clause in the cumulative sum window function. Maybe you could add a timestamp field that you can order by.

Or maybe you always want the declined status to be considered ahead of the approved status when the AbsenceDate is the same. Here is an example of a query that would do exactly that (notice the changes in the order by clauses):

SELECT    
    lsm.EmployeeName,
    pd.absenceDate,
    pd.amountInDays * 8 AS [HoursReported],
    pd.status,
    (SUM(CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
              ELSE 0 END) OVER (partition by lsm.[EmployeeName] order by pd.absenceDate,
                                                                         case when pd.[status] = 'App' then 1 else 0 end)) AS [TotalUsedHours]
          ( @maxPSHours ) - (sum(
              CASE WHEN pd.[status]='App' THEN (pd.amountInDays * 8)
                   ELSE 0 END)
          over (
              partition by  lsm.[EmployeeName] order by pd.absenceDate,
                                                        case when pd.[status] = 'App' then 1 else 0 end)) AS [TotalRemainingHours]
FROM    
    [LocationStaffMembers] lsm 
INNER JOIN 
    [PersonalDays] pd ON lsm.staffMemberId = pd.staffMemberId
ORDER BY lsm.[EmployeeName],
         pd.absenceDate,
         case when pd.[status] = 'App' then 1 else 0 end

edited Dec 16, 2015 at 18:44

answered Dec 16, 2015 at 18:30

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shawnt00 Over a year ago

I haven't really read through the whole problem...but wouldn't framing the window with RANGE fix it?

sstan Over a year ago

@shawnt00: No, I'm pretty sure it's just an ordering problem.

Collectives™ on Stack Overflow

Conditional SUM in SQL Server 2014

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related