CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vidath Adheesha
Vidath Adheesha

Reputation: 15

Get Week from a date range SQL Server

Hello can someone tell me how to get the week no according to the given date range (Not week number by year or month) but the date range.

Eg : From Date : '6/26/2017' and To Date: '7/23/2017'

Results

Week 1 - (6/26/2017 - 7/2/2017)
Week 2 - (7/3/2017 - 7/10/2017)
Week 3 - (7/11/2017 - 7/18/2017)
Week 4 - (7/19/2017 - 7/23/2017)

Upvotes: 1

Views: 448

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

If you are open to a TVF (Table-Valued Function)

I'll often use a udf to create dynamic date/time ranges. It is faster than a recursive CTE and is parameter driven. You supply the Date/Time range, DatePart, and Increment.

Example

Select WeekNbr = 'Week '+cast(RetSeq as varchar(10))
      ,WeekBeg = cast(RetVal as date)
      ,WeekEnd = cast(RetVal+6 as date)
 From [dbo].[udf-Range-Date]('2017-06-26','2017-07-23','WK',1)

Returns

WeekNbr  WeekBeg      WeekEnd
Week 1   2017-06-26   2017-07-02
Week 2   2017-07-03   2017-07-09
Week 3   2017-07-10   2017-07-16
Week 4   2017-07-17   2017-07-23

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Upvotes: 1

Related Questions