Check Next Two Wednsedays With an Exclusion Table (T-SQL Code snippet)


--date of Wednseday of the job run week
declare @rundate date=Dateadd(day,2,dateadd(week,datediff(week,0,getdate()),0) )
declare @firstrun date,@lastrun date
--,@lastrunExt6 date

---Option 1

declare @NumOfNoRundate int
;with mycte as

(select norundate from [dbo].[exc_norun]
where norundate=dateadd(week,1,@rundate)
union all
select dateadd(week,1,m.norundate)
from mycte m join [dbo].[norunDates] n on n.norundate=dateadd(week,1,m.norundate))

select @NumOfNoRundate=count(*) from mycte

select @firstrun=dateadd(week,1+@NumOfNoRundate,@rundate),
@lastrun=dateadd(week,2+@NumOfNoRundate,@rundate )
--,@lastrunExt6=Dateadd(day,-1,dateadd(week,3+@NumOfNoRundate,@rundate ))

print @NumOfNoRundate
print @firstrun
print @lastrun
--print @lastrunExt6

--Option 2

;with mycte1 as

(select 1 as i, dateadd(week,1,@rundate) dt
union all
select m.i+1 as i, dateadd(week,m.i,m.dt)
from mycte1 m where m.i<100
and Not exists (select 1 from [dbo].[norunDates] n where n.norundate=m.dt)

)

Select @firstrun=min(dt)
,@lastrun= dateadd(week,1,min(dt))
--,@lastrunExt6=Dateadd(day,-1,dateadd(week,2,min(dt) ))
from mycte1
print @firstrun
print @lastrun
--print @lastrunExt6
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s