locked
SQL Help RRS feed

  • Question

  • User-1216534573 posted

    Hi,

    I have to generate a table, week numbers  as columns in between dates using SQL.

    Similarly like this.

    Here W1,W2,etc.. are no. of Week columns between given dates.

    Start Date 9/30/2016
    End Date 10/31/2016
    TableA
    W1 W2 W3 W4 W5 W6 W7
               
               
               
               
               
    Monday, October 3, 2016 9:51 AM

All replies

  • User-2057865890 posted

    Hi Magesh.L,

    DECLARE @startDate DATE = '2016-09-30', @endDate DATE = '2016-10-31';
    
    WITH t1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)), -- 2 Rows
    t2(N) AS (SELECT 1 FROM t1 x, t1 y), -- 4 Rows (2*2)
    t3(N) AS (SELECT 1 FROM t2 x, t2 y), -- 16 Rows (4*4)
    t4(N) AS (SELECT 1 FROM t3 x, t3 y), -- 256 Rows (16*16)
    t5(N) AS (SELECT 1 FROM t4 x, t4 y), -- 65,536 Rows (256*256)
    tally(N) AS (SELECT 0 UNION ALL
                 SELECT TOP (DATEDIFF(WEEK, @startDate, @endDate)) -- Limit the result-set straight up front
                 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
                 FROM t5 x, t5 y) -- 4,294,967,296 Rows (65,536*65,536)
    SELECT N AS WeekNo,
    CASE WHEN DATEADD(WEEK, DATEDIFF(WEEK, '2016-09-30', DATEADD(WEEK,N,@startDate)), '2016-09-30') < @startDate 
         THEN @startDate
         ELSE DATEADD(WEEK, DATEDIFF(WEEK, '2016-09-30', DATEADD(WEEK,N,@startDate)), '2016-09-30') END AS weekStart,
    CASE WHEN DATEADD(WEEK, DATEDIFF(WEEK, '2016-10-31', DATEADD(WEEK,N+1,@startDate)), '2016-10-31') > @endDate 
         THEN @endDate
         ELSE DATEADD(WEEK, DATEDIFF(WEEK, '2016-10-31', DATEADD(WEEK,N+1,@startDate)), '2016-10-31') END AS weekEnd
    FROM tally;

    Best Regards,

    Chris

    Tuesday, October 4, 2016 5:09 AM
  • User-1216534573 posted

    Hi Chris,

    Thanks for your answer.

    I need to create Weekno(Like example given in my First Post) as columns and also Date should be dynamic.

    Can you help in this?

    Wednesday, October 5, 2016 4:30 AM
  • User364663285 posted

    Hi,

    1. Create table having 52/53 week columns
    2. Here is example for getting start/end of one given week
    declare @yr int,
    @week int,
    @start_dt datetime;
    set @yr=2016
    set @week=53
    
    set @start_dt=convert(datetime,ltrim(rtrim(cast(@yr as varchar)))+'0101',112)
    
    select dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end-7,@start_dt) [1st Day of Week],dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end-1,@start_dt) [Last Day of Week]
    

    3. Create loop to insert start/end dates into the table

    Wednesday, October 5, 2016 7:29 AM