Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Tally Calendars and 'Week 1' Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 7:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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?
Post #1403619
Posted Monday, January 7, 2013 8:16 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1403646
Posted Monday, January 7, 2013 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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.
Post #1403689
Posted Monday, January 7, 2013 9:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1403693
Posted Monday, January 7, 2013 1:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:31 PM
Points: 1,945, Visits: 2,851
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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403835
Posted Monday, January 7, 2013 1:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1403866
Posted Monday, January 7, 2013 6:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:31 PM
Points: 1,945, Visits: 2,851
Or normalize the components into indexable columns ISOYear, ISOWeek, ISOWeekDay, and avoid the overhead of using LIKE operators.


I have not measured the overhead. These are strings that are shorter than your columns, with a fixed regular expression. That means LIKE can use underscores and [] lists, which are small and fast. The % is where LIKE has costs.

They preserve the fact that this is ONE unit of temporal measurement. You have the design problem of columnar attribute splitting.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403941
Posted Tuesday, January 8, 2013 1:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 3,605, Visits: 5,201
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1404030
Posted Tuesday, January 8, 2013 1:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:25 AM
Points: 870, Visits: 2,384
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
Post #1404038
Posted Tuesday, January 8, 2013 1:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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!
Post #1404049
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse