sql - Check if date is exists among the dates then add extra one day to that date -

sql - Check if date is exists among the dates then add extra one day to that date -

i want add together days(for illustration 3 days) given date. before adding days can check holidays configured in 1 table. here sample code. unable accomplish it.

declare @holidayslist nvarchar(250) = '2014-06-29,2014-07-02,2014-07-18,2014-07-30,2014-10-26' declare @rdate date = '2014-06-28' declare @adddays int = 3 declare @count int = 0 while(@count < @adddays) begin set @rdate = dateadd(day,1,@rdate) --print '1 ' +cast( @rdate nvarchar(100)) if exists(select item fnsplit(@holidayslist,',') item = @rdate) begin select @rdate= convert(varchar(10),dateadd(day,1,@rdate),101) print 'if '+ cast( @hrdate nvarchar(100)) end set @count = @count+1 end print @rdate

here fnsplit function returns table. in above script have add together 3 days @rdate. before adding have check holidays list i.e in @holidayslist. if holiday come can add together date.

in above script output is: 2014-08-03 because 29th holiday , 2nd holiday. output 2014-08-03

you can without loops:

declare @holidays table (item date); insert @holidays values ('2014-06-29'),('2014-07-02'),('2014-07-18'),('2014-07-30'),('2014-10-26'); declare @rdate date = '2014-06-28', @adddays int = 3; cte ( select *, rownumber = row_number() over(order d.date) ( select dateadd(day, row_number() over(order object_id), @rdate) sys.all_objects ) d (date) not exists ( select 1 @holidays h h.item = d.date ) ) select date cte rownumber = @adddays;

the principal part:

select date = dateadd(day, row_number() over(order object_id), @rdate) sys.all_objects

will generate list of dates onwards starting date. exclude holidays list using not exists, , add together rank these days using row_number(). case of selecting date required rank.

sql sql-server sql-server-2008 sql-server-2008-r2