March 27, 2013 at 2:27 pm
I have a cte script to create a calendar table for 53 weeks,
starts at 2011-01-01 (week 1, Saturday) and ends at 2011-12-31 (week 53, Saturday).
The average weeks starts on a Sunday (2011-01-02) and ends on a Saturday (2011-01-08).
I use 'SELECT datepart(ww, [Date])' to generate the weeks, the problem is that datepart week only generates 53 weeks (since there is only 53 weeks in a year).
Customer wants weeks from 01 January 2011 to 31 March 2012 (65 weeks).
The calendar table i use spans over 15 yrs
(2011-01-01 to 2011-12-31, 2012-01-01 to 2012-12-31, etc.)
Below is the cte script
declare
@StartDate datetime = '2011-01-01',
@EndDate datetime = '2012-01-01'; -- for testing purposes I only selected one year
with Calendar
as
(SELECT cast(@StartDate as datetime) [Date]
union all
SELECT [Date] + 1 FROM Calendar WHERE [Date] + 1 < @EndDate)
SELECT
[Date]
,datepart(dy, [Date]) [day of year]
,datepart(dd, [Date]) [day of month]
,datepart(dw, [Date]) [day of week]
,datename(dw, [Date]) [dayname]
,datepart(ww, [Date]) [week]
,datepart(mm, [Date]) [month]
,datepart(yy, [Date]) [year]
--into dbo.CalendarTable -- uncomment to populate CalendarTable
FROM Calendar
OPTION (MAXRECURSION 0)
March 27, 2013 at 2:54 pm
Does this do what you want?
DECLARE @StartDate DATETIME = '2011-01-01',
@EndDate DATETIME = '2012-03-31';
WITH Base AS (
SELECT * FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b (number)
),
Calendar AS (
SELECT DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT 1)), @StartDate) AS Date
FROM Base b1 CROSS JOIN Base b2 CROSS JOIN Base b3
)
SELECT
[Date]
,DATEPART(dy, [Date]) [day of year]
,DATEPART(dd, [Date]) [day of month]
,DATEPART(dw, [Date]) [day of week]
,DATENAME(dw, [Date]) [dayname]
,DATEPART(ww, [Date]) + DATEDIFF(yy,@StartDate, Date)*52 [week]
,DATEPART(mm, [Date]) [month]
,DATEPART(yy, [Date]) [year]
--into dbo.CalendarTable -- uncomment to populate CalendarTable
FROM Calendar
WHERE Date < @EndDate;
I changed how the rows are generated because recursive CTEs aren't that efficient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2013 at 3:30 pm
Thanks GilaMonster, you're sharp, very sharp (and quick too).
Had one issue (two entire weeks were = week 53):
2011-12-31 00:00:00.000 Week 53
2012-01-01 00:00:00.000 Week 53
Replaced *52 with *53 to correct it, view below:
----------------------------------------------
DATEPART(ww, [Date]) + DATEDIFF(yy,@StartDate, Date)*53 [week]
Also, the first record in table starts with '2011-01-02 00:00:00.000' ,
supposed to start with 2011-01-01 00:00:00.000.
Added -1 to below Calendar cte:
-------------------------------
Calendar
AS (
SELECT DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT 1)), @StartDate) -1 AS Date
FROM Base b1 CROSS JOIN Base b2 CROSS JOIN Base b3
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply