Query holiday from column with condition

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

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!