Is there a way to perform a CASE to CASE subtraction inside another case.

Here is a simplified part of the query ( I am only including the case part for brevity)

-- Sum up if the shift is contracted
CASE 
   WHEN s.IsAbsence = 0 AND s.IsAnnualLeave = 0 AND s.IsOvertime = 0 
   THEN SUM(s.ShiftHours) 
   ELSE 0 
   END AS [Shift Hours],
-- Sum if the shift is paid over and above contract hours
CASE 
   WHEN s.IsOvertime = 1 OR s.IsAnnualLeave = 1 
   THEN SUM(s.ShiftHours) 
   ELSE 0 
   END AS [Paid Hours],
-- SUM if the colleague is absent
CASE 
   WHEN s.IsAbsence = 1 
   THEN SUM(s.ShiftHours) 
   ELSE 0 
   END  AS [Unpaid Hours],

This is the part that I need help with. Net Hours cannot be less than zero due to other calculations so if the first case minus the second case is less than zero it should be zero.

CASE
   WHEN
   CASE 
      WHEN s.IsOvertime = 1 OR s.IsAnnualLeave = 1 
      THEN SUM(s.ShiftHours) 
      ELSE 0 
      END 
   - -- This is a minus
   CASE 
      WHEN s.IsAbsence = 1 
      THEN SUM(s.ShiftHours) 
      ELSE 0 
      END
   < 0
THEN 0 
ELSE (Perform the minus above)
END

Put simply IF ((a - b) < 0) return 0 else return (a - b)

CodePudding user response：

The problem is one of conditional aggregation, in this case summing hours into different categories depending on some status derived from other columns. That is easily accomplished by moving the conditional logic inside the aggregation:

-- Sum of all regular shift hours.
Sum( case
  when s.IsAbsence = 0 and s.IsAnnualLeave = 0 and s.IsOvertime = 0 then s.ShiftHours else 0 end
  ) as [Shift Hours],
-- Sum of hours paid over and above contract hours.
Sum( case when s.IsOvertime = 1 or s.IsAnnualLeave = 1 then s.ShiftHours else 0 end ) as [Paid Hours],
-- Sum of hours when the colleague is absent.
Sum( case when s.IsAbsence = 1 then s.ShiftHours else 0 end ) as [Unpaid Hours]

For each row processed the conditions will be evaluated and the hours credited or ignored by summing either s.ShiftHours or 0 as appropriate.