Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
 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
Posted
Updated 21-Jan-19 9:36am
Comments
ZurdoDev 21-Jan-19 15:30pm    
What is your question?

1 solution

I found it thanks all there is a mistake in @day1 alll values is @date1
 
Share this answer
 
Comments
Maciej Los 21-Jan-19 16:31pm    
If you've found solution, please, accept your answer as a solution (green button) - to remove your question from unanswered list.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900