Declare @weekstart date
Declare @weekend date
Set @weekstart='2019-01-20'
Set @weekend='2019-01-26'
Declare @day1 date
Declare @day2 date
Declare @day3 date
Declare @day4 date
Declare @day5 date
Declare @day6 date
Declare @day7 date
if (@weekstart in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=@weekstart END else Begin Set @day1=null End;
if ((DATEADD(dd, 1, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=(DATEADD(dd, 1, @weekstart)) END else Begin Set @day1=null End;
if ((DATEADD(dd, 2, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=(DATEADD(dd, 2, @weekstart)) END else Begin Set @day1=null End;
if ((DATEADD(dd, 3, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=(DATEADD(dd, 3, @weekstart)) END else Begin Set @day1=null End;
if ((DATEADD(dd, 4, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=(DATEADD(dd, 4, @weekstart)) END else Begin Set @day1=null End;
if ((DATEADD(dd, 5, @weekstart)) Not in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=(DATEADD(dd, 5, @weekstart)) END else Begin Set @day1=null End;
if (@weekend in (SELECT [date] FROM [tbl_holidaies])) Begin Set @day1=@weekend END else Begin Set @day1=null End;
Insert Into tbl_week_sched ([1],[2],[3],[4],[5],[6],[7]) values (@day1,@day2,@day3,@day4,@day5,@day6,@day7)
[tbl_holidaies]
date
2019-01-04
2019-01-06
2019-01-11
2019-01-13
2019-01-18
2019-01-20
2019-01-25
2019-01-27
2019-02-01
2019-02-03
The Results in [tbl_week_sched]
id 1 2 3 4 5 6 7
1 NULL NULL NULL NULL NULL NULL NULL
What I have tried:
i want to add seven days from week start if not a holiday