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
Asma KhalidPosted Jul 8, 2019, 12:53 AM
Mario ScheerenPosted Jul 7, 2019, 10:13 AM