January 7, 2013 at 7:51 am
Hey guys,
I know there's been a few discussions on here about tally calendars and their use, I've got one on the go which seemed to be the one everyone was going for at the time (in the comments at the top, assembled from multiple resources by lowell *at* stormrage dot com)!
I've got a report which pulls in weekly data, however, I'm not sure what to make of the whole week 1 thing...
Week 1 and year 2012 contains the following days:
1st-7th Jan 2012, 30th,31st December 2012.
Week 1 and year 2013 contains hte following days:
1st-5th Jan 2013, 29th, 30th and 31st December 2013
I'm grouping by year number and IsoWeek, my report only had data from say May 2012 onwards, now it's got 2012, week 1 and some data (just for 2 days worth of data), if data did go far back enough it would combine the first few days of 2012 and hte last couple as 'week 1' this seems to be correct as far as ISO Week dates are concerned but conceptually in data terms it seems wrong.
For those of you who produce date based data sets have you encountered this and if so what were your conclusions/thoughts?
January 7, 2013 at 8:16 am
It depends on the business-definition. Which is usually the ISO definition, but not always.
Most places I've worked, they want the first week to include 1 Jan, but to start on a Monday and end on a Friday (or Sun-Sat), so I have to calculate week-beginning even for 1 Jan.
But I have to admit, I generally prefer a persisted Calendar table over a "runtime calendar". You can't store holidays in a runtime version. My current employer gave us the 31st of December off this year, because it was a Monday and the 1st a Tuesday. Normal years, they don't do that. A workdays calculator built using a numbers table (tally table) can't easily record that extra, "not by the usual rules" paid holiday. A persisted calendar table can easily record that kind of thing. Sure, you can store a table just of holidays, and use an Except query to get those into a runtime calendar, but that's more expensive (server resources) and complicated.
With a persisted calendar, you can present it to management, via a simple UI, and they can confirm and sign-off on it. Want the 1st week of the year to begin on 30 Dec (Sunday like it just did)? Sure, no problem. Just record that in the "BusinessYear" and "WeekOfYear" columns. No tricky coding required.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 9:08 am
I've sort of added a cheeky hack to my existing table, just added an IsoWeekYear column, which I set to the year(thedate) first of all then updated for IsoWeek = 1 and Julian Day > 300 - set that to year(thedate)+1
In this way 30th December 2013 actually has 2013 as it's 'year' and 2014 as it's IsoWeekYear - meaning that 'Week 1 2013' in my report reads 30th Dec 2012 - 5th Jan 2012, week 2 is 6th Jan to 12th Jan etc.
May not be the niecest or smartest way to do it but pretty quick, does mean I need to change the reports which group on YearNumber to group on IsoWeekYear but I think that's only a handful of reports.
January 7, 2013 at 9:15 am
That's the way to do it!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:53 pm
CELKO (1/7/2013)
I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'
There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html
Or normalize the components into indexable columns ISOYear, ISOWeek, ISOWeekDay, and avoid the overhead of using LIKE operators.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 1:15 am
CELKO (1/7/2013)
I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'
There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html
I am intrigued by this ISO-8601 week-within-year idea as you've posted it before. But I'm having trouble getting your LIKE to work in the following code (returns no rows). Any ideas?
CREATE FUNCTION GenerateCalendar
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
GO
CREATE TABLE #Calendar
([Date] DATETIME PRIMARY KEY
,[Year] INT
,[JulWk] INT
,[JulDay] INT
,ISO_WK AS (CAST([Year] AS CHAR(4)) + 'W' +
CAST([JulWk] AS VARCHAR(2)) + '-' +
CAST([JulDay] AS VARCHAR(3)))
)
INSERT INTO #Calendar
SELECT [Date], [Year], [JulWk], [JulDay]
FROM GenerateCalendar('20120101', 365*2)
SELECT * FROM #Calendar
WHERE ISO_WK LIKE '2012W26-[67]'
DROP TABLE #Calendar
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 8, 2013 at 1:31 am
Final Edit,
Dwain, I figured out why yours didnt work you're addind on the Julian day which is 1-365/366
I think you need to add the Week day number something like this
CREATE TABLE #Calendar
([Date] DATETIME PRIMARY KEY
,[Year] INT
,[JulWk] INT
,[WkDNo] INT
,ISO_WK AS (CAST([Year] AS CHAR(4)) + 'W' +
CAST([JulWk] AS VARCHAR(2)) + '-' +
CAST([WkDNo] AS VARCHAR(3)))
)
INSERT INTO #Calendar
SELECT [Date], [Year], [JulWk], [WkDNo]
FROM GenerateCalendar('20120101', 365*2)
SELECT * FROM #Calendar
WHERE ISO_WK LIKE '2012W26-[67]'
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 8, 2013 at 1:47 am
I saw that formatting on Wikipedia, which sort of gave me the idea, however, as I didn't see a need for the other columns (I already have ISO Week number and Julian Day and shed loads of other metrics) I just added the ISO Year.
Should I need to make a ISO Date so to speak I've got the necessary columns, a bit backwards compared to your aproach but it seemed quicker!
January 8, 2013 at 1:56 am
Jason-299789 (1/8/2013)
Final Edit,Dwain, I figured out why yours didnt work you're addind on the Julian day which is 1-365/366
I think you need to add the Week day number something like this
CREATE TABLE #Calendar
([Date] DATETIME PRIMARY KEY
,[Year] INT
,[JulWk] INT
,[WkDNo] INT
,ISO_WK AS (CAST([Year] AS CHAR(4)) + 'W' +
CAST([JulWk] AS VARCHAR(2)) + '-' +
CAST([WkDNo] AS VARCHAR(3)))
)
INSERT INTO #Calendar
SELECT [Date], [Year], [JulWk], [WkDNo]
FROM GenerateCalendar('20120101', 365*2)
SELECT * FROM #Calendar
WHERE ISO_WK LIKE '2012W26-[67]'
Ah-ha! Indeed that does seem to fix it. Thought Joe meant Julian day not week day number.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply