I am working on a payroll report that calculates different shift rates per facility. For facility 5,10,20 the shift rate for 1 is 0. For facility 5,10 shift rate 2 and 3 is [Rate]*.06. For facility 20 the shift rate for 2 is [Rate]*.35 and for shift rate 3 it is [Rate]*.45. I created the following query but it is not validating. Any suggestions would be greatly appreciated. Thank you!
Total
(case when [Shift ID] = 1 and [Facility] in ('05','10','20') then 0
else
when [Shift ID] in (2,3) and [Facility] in ('05','10') then ([Rate]*.06)
else
when [Shift ID] = 2 and [Facility] = '20' then ([Rate]*.35)
else
when [Shift ID] = 3 and [Facility] ='20' then ([Rate]*.45)
end)
You could use the query like:
case
when [Shift ID] = 1 and [Facility] in ('05','10','20') then [Shift Rate]=0
when [Shift ID] in (2,3) and [Facility] in ('05','10') then [Shift Rate]=([Rate]*.06)
when [Shift ID] = 2 and [Facility] = '20' then [Shift Rate]=([Rate]*.35)
when [Shift ID] = 3 and [Facility] ='20' then [Shift Rate]=([Rate]*.45)
end
shift rate
Thanks. That worked.