## Tally Calendars and 'Week 1'

 Author Message Rob-350472 SSCommitted Group: General Forum Members Points: 1565 Visits: 684 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 2013I'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? GSquared SSC Guru Group: General Forum Members Points: 58295 Visits: 9730 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon Rob-350472 SSCommitted Group: General Forum Members Points: 1565 Visits: 684 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)+1In 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. GSquared SSC Guru Group: General Forum Members Points: 58295 Visits: 9730 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon GSquared SSC Guru Group: General Forum Members Points: 58295 Visits: 9730 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon dwain.c SSCoach Group: General Forum Members Points: 17979 Visits: 6431 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) ) dpGOCREATE 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 #CalendarSELECT [Date], [Year], [JulWk], [JulDay]FROM GenerateCalendar('20120101', 365*2)SELECT * FROM #CalendarWHERE 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Jason-299789 SSCertifiable Group: General Forum Members Points: 5021 Visits: 3232 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 #CalendarSELECT [Date], [Year], [JulWk], [WkDNo]FROM GenerateCalendar('20120101', 365*2)SELECT * FROM #CalendarWHERE ISO_WK LIKE '2012W26-[67]'` _________________________________________________________________________SSC Guide to Posting and Best Practices Rob-350472 SSCommitted Group: General Forum Members Points: 1565 Visits: 684 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!