Need Solution on following SQL Query

Question

I have two tables  in sql

First table name tblHolidays with following data 

 

Id holidaydate

1 2019-07-03 00:00:00.000

2 2019-07-31 00:00:00.000

3 2019-08-15 00:00:00.000

4 2019-09-02 00:00:00.000

5 2019-12-25 00:00:00.000

 

Second table name tbl2 with following data 

aeging reqDate

NULL 2019-07-01 00:00:00.000

NULL 2019-07-01 00:00:00.000

NULL 2019-07-02 00:00:00.000

NULL 2019-07-02 00:00:00.000

NULL 2019-07-21 00:00:00.000

NULL 2019-07-29 00:00:00.000

NULL 2019-08-16 00:00:00.000

NULL 2019-09-01 00:00:00.000

NULL 2019-12-24 00:00:00.000

 

declare @enddate datetime

set @enddate='2019-12-29'

 

Plus I have enddate set to 29 December 2019

 

 In the second table I have to update aeging column value to difference between reqDate column in tbl2 and enddate excluding sundays and the holidays which fall between these two dates.

For example out should be

 

Output 

 

aeging reqDate

151    2019-07-01 00:00:00.000

151    2019-07-01 00:00:00.000

150    2019-07-02 00:00:00.000

150    2019-07-02 00:00:00.000

134    2019-07-21 00:00:00.000

128    2019-07-29 00:00:00.000

114    2019-08-16 00:00:00.000

100    2019-09-01 00:00:00.000

4       2019-12-24 00:00:00.000

 

For example the difference between the number of days between 1st july and 29 december excluding sunday was 156 and in between there were 5 holidays so subtracting 5 holidays we get 151.

I have subtracted the number of sundays.Using this query

 

Update tbl2 set aeging=(DATEDIFF(dd, tbl2 .reqDate, @enddate) + 1)-(DATEDIFF(wk, tbl2 .reqDate, @enddate) * 1)

-(CASE WHEN DATENAME(dw, tbl2 .reqDate) = 'Sunday' THEN 1 ELSE 0 END)

 

But how should i subtract number of public holidays between these two dates? what query should i writes i am stuck on this kindly let me know the solution