Week start for the past 5 weeks with custom start date (Monday)


  
 SET DATEFIRST 1
 declare @enddate date=getdate()
declare @startdate date=DATEADD(week, DATEDIFF(week,0,dateadd(week,-5,@enddate)),-1)


;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
,dates as (
Select datepart(week, dateadd(day,n-1,@startdate) ) wk, dateadd(day,n-1,@startdate) dt   from Nums
WHERE dateadd(day,n-2,@startdate)< @enddate
)
 


 Select wk,     Max(Case when datename(dw,dt)='Monday' Then dt End) weekstart,
 Max(Case when datename(dw,dt)='Sunday' Then dt End) weekend  from dates
Group by wk
Having( Max(Case when datename(dw,dt)='Monday' Then dt End) is not null 
 and Max(Case when datename(dw,dt)='Sunday' Then dt End) is not null)


 SET DATEFIRST 7
 
 
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