2
Answers

Need Solution on following SQL Query

Aniket Narvankar

Aniket Narvankar
5y
670
1
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
Answers (2)
Next Recommended Forum