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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy