I will explain with tables result. I have a table “emp” which consists of columns (number of employee”id_emp” ,”date” and ‘”type”):
id_emp Date Type
5 2019-08-01 NULL
5 2019-08-02 NULL
5 2019-08-03 NULL
5 2019-08-04 NULL
5 2019-08-05 NULL
5 2019-08-06 NULL
5 2019-08-07 NULL
5 2019-08-08 NULL
5 2019-08-09 NULL
5 2019-08-10 NULL
6 2019-08-01 NULL
6 2019-08-02 NULL
6 2019-08-05 NULL
And I have another table “holiday” like this:
id_emp mon tue wed thu fri sat sun 5 0 0 0 0 0 0 1 6 0 0 0 0 1 0 0
The result I want must be like this:
id_emp date type 5 2019-08-01 absent 5 2019-08-02 absent 5 2019-08-03 absent 5 2019-08-04 holiday 5 2019-08-05 absent 5 2019-08-06 absent 5 2019-08-07 absent 5 2019-08-08 absent 5 2019-08-09 absent 5 2019-08-10 absent 5 2019-08-11 holiday 6 2019-08-01 absent 6 2019-08-02 holiday 6 2019-08-05 absent
I tried to do something like this but it gives the wrong result:
select id_emp, date_1, case when mon ='1' and date_1= FORMAT(cast( date_1 as numeric), 'ddd') then 'holiday' else case when tue ='1' and date_1=FORMAT(cast( date_1 as numeric), 'ddd') then 'holiday' else case when wed ='1' and date_1= FORMAT(cast( date_1 as date), 'ddd')then 'holiday' else case when thu ='1' and date_1= FORMAT(cast( date_1 as date), 'ddd')then 'holiday' else case when fri ='1' and date_1= FORMAT(cast(date_1 as date), 'ddd') then 'holiday' else case when sat ='1' and date_1=FORMAT(cast( date_1 as date), 'ddd')then 'holiday' else case when sun ='1' and date_1= FORMAT(cast( date_1 as date), 'ddd')then 'holiday' else 'absent' end end end end end end end from holidays inner join emp on holidays.id_emp = emp.id_emp
Advertisement
Answer
I would unpivot the holiday records. So:
select e.id_emp, e.date,
(case when v.val = 0 then 'absent' else 'holiday' end) as type
from emp e join
holiday h
on h.id_emp = e.id_emp cross apply
(values ('mon', h.mon),
('tue', h.tue),
. . . -- add the rest of the weekdays here
) v(weekday, val)
where left(datename(weekday, e.date), 3) = v.dow; -- datename gives the full name!