Date Condition with Case when Holiday exclude

cal = USFederalHolidayCalendar()
holidays = cal.holidays(start='2016-01-01', end='2016-12-31')
print holidays.size

10

idx = pd.DatetimeIndex(pd.date_range(start='2015-1-1', 
                                     end='2016-12-31', freq='30min'))
print idx.size

35041

idx[pd.DatetimeIndex(idx.date).isin(holidays.date)].size

480

    declare @InputDate date='2016-09-16' --yur input date

   select EmployeeId,
   case when (select count(1) from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId)>1 then 'True' else 'False' end as [OnHoliday],
   case when ((select StartMidDay from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId))=0.5 then 'True' else 'False' end as [IsHalf] ,
   from Employee 

- (select ISNULL(count(1),0) from Cal where Cal.Holiday between ALERTS_CREATE_DT AND CreatedDate )

def is_lastday_holiday(lastday):  
    while lastday in sorted(holidays.AT(years=2017)):  
        lastday = lastday + timedelta(days=1) 
    return lastday

lastday += timedelta(days=1)

SELECT YT.{YourColumn},
       WD.WorkingDays
FROM YourTable YT
     CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
                  FROM CalendarTable CT
                  WHERE CT.[DateColumn] >= YT.StartingDateColumn
                    AND CT.[DateColumn] <= YT.EndingDateColumn
                    AND CT.WorkingDay = 1) WD