Hello I am missing something because my code errors.
select * from ##ScheduleDetail SD left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate where (ScheduleDate = testdate) and (Case when HF.IsHoliday = 1 then (overtime = 1 and makeup = 0) else (overtime = 0 and Makeup = 0) end ) and DOW = 5 order by ActivityStartTime
I’ve attempted several combinations and each one errors at either the first equal sign or the second. What am I missing?
Advertisement
Answer
The branches of a case
expression can only return values, not additional expressions to be evaluated in the where
condition. You could, however, simulate this behavior with the and
and or
logical operators:
select * from ##ScheduleDetail SD left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate where (ScheduleDate = testdate) and ((HF.IsHoliday = 1 and overtime = 1 and makeup = 0) or (overtime = 0 and Makeup = 0)) and DOW = 5 order by ActivityStartTime
Note that you have makeup = 0
on both branches of the case
expression in the question (or both sides of the or
in the answer), so you could extract it out of it and simplify the condition a bit:
select * from ##ScheduleDetail SD left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate where ScheduleDate = testdate and makeup = 0 and ((HF.IsHoliday = 1 and overtime = 1) or overtime = 0) and DOW = 5 order by ActivityStartTime