Find alternate week day

My co-worker had a task of finding minimum alternate week day as of today for a given date and day

Consider these data

Date : 2009-12-22

day  : 5 (it denotes Thursday)

It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date).

The task is finding the minimum of current available day

Here is my solution

declare @date datetime, @day int

select @date='2009-12-22',@day=5

select
       
min(dateadd(day,14*(number),date))
from 
       
(
               
select dateadd(day,@day-datepart(weekday,@date),@date) as date
       
) as t,master..spt_values
where type='p' and dateadd(day,14*(number),date)>=getdate()

 

Published Tuesday, January 12, 2010 1:45 PM by Madhivanan
Filed under: , ,

Comments

No Comments