有沒有辦法在另一個案例中執行 CASE 到 CASE 減法。
這是查詢的簡化部分（為了簡潔，我只包括案例部分）
-- 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],
這是我需要幫助的部分。由于其他計算，凈小時數不能小于零，因此如果第一種情況減去第二種情況小于零，則應為零。
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
簡單地說 IF ((a - b) < 0) return 0 else return (a - b)
uj5u.com熱心網友回復：
問題是條件聚合之一，在這種情況下，根據從其他列派生的某些狀態，將小時數匯總到不同的類別中。這很容易通過在聚合內移動條件邏輯來實作：
-- 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]
對于處理的每一行，將評估條件，并通過相加s.ShiftHours或0視情況相加來計入或忽略小時數。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/384654.html
標籤：sql sql-server 查询语句