Query for Holiday as per Attendance

Transact-SQL
1

Hi,

I have Holiday Master table given below.

Hold_No numeric
Hold_From Date
Hold_To Date
Hold_Type varchar(50)

I have Attendance Table is given below.

Atn_No Numeric
Atn_Date Date
Atn_EmpNo numeric
Atn_Type Varchar(50)

What want is check in Attendance if Type is "OD" then check in Holiday Master how many days are OD as per Holidays in Attendance.

Data

Hold_No Hold_From Hold_To Hold_Type
1 01-Jan-19 02-Jan-19 XYZ
2 05-Jan-19 05-Jan-19 LMN
Atn_No Atn_Date Atn_EmpNo Atn_Type
1 01-Jan-19 1 OD
2 02-Jan-19 1 AB
3 03-Jan-19 1 OD
4 04-Jan-19 1 SL
5 05-Jan-19 1 OD
6 06-Jan-19 1 OD
7 07-Jan-19 1 OD
8 08-Jan-19 1 OD

as per above Output No. of Holidays is 2

Thanks
Basit.

2 
select count(*)
  from attendance as a
       inner join holiday_master as h
               on h.hold_from>=a.atn_date
              and h.hold_to<=a.atn_date
 where a.atn_type='OD'
;