# SQL Help

• ### 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,
THEN @startDate
THEN @endDate
FROM tally;```

Best Regards,

Chris

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

Hi Chris,

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