"Amended" calendar table in SQL?

1

Hope someone can help.

I need to "fudge" week numbers - I will create a calendar table to do this but need help with populating it.

Assume, just for this task, we are looking at 2017 only.

The first seven days of January I want to be Week 1-2017
Days 8-14 I want to be Week 2-2017
Days 15-21 I want to be Week 3-2017
Days 22-28 I want to be Week 4-2017
Days 29, 30 and 31 will be Week 5-2017

The first seven days of February I want to be Week 6-2017
Days 8-14 I want to be Week 7-2017
Days 15-21 I want to be Week 8-2017
Days 22-28 I want to be Week 9-2017

The first seven days of March I want to be Week 10-2017
....and so on throughout the year.

This will mean 59 weeks in a normal year, 60 weeks in a leap year.

I thought I could accomplish it easily in Excel but it's a bit tricker than I thought.

Has anyone got a SQL solution?

Many thanks.

2

try this

with cte (ddate)
as
(
select cast('1/1/2017' as datetime) ddate
union all
select dateadd(dd,1,ddate) from cte where ddate < '12/31/2017'
)
select * , cast(DATEDIFF(day,'2017-01-01',ddate)/7+1 as varchar(100))+'-'+cast(year(ddate) as varchar(10))

d from cte option ( MaxRecursion 0 )

1 Like
3 
DECLARE @base_date date
SET @base_date = GETDATE() --current year,adjust as needed for different year

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT date,
    ROW_NUMBER() OVER(ORDER BY date) AS week_number
FROM (
    SELECT DATEADD(DAY, (weeks.number - 1) * 7, DATEADD(MONTH, DATEDIFF(MONTH, 0, base_year) + (months.number - 1), 0)) AS date
    FROM (
        SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @base_date), 0) AS base_year
    ) AS base_year
    INNER JOIN cteTally100 months ON months.number BETWEEN 1 AND 12
    INNER JOIN cteTally100 weeks ON weeks.number BETWEEN 1 AND 5
    WHERE months.number <> 2 OR YEAR(@base_date) % 4 = 0 OR weeks.number < 5
) AS derived
ORDER BY date
1 Like
4

Thanks both, much appreciated.

Love this forum, so much wisdom out there :relaxed: