InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 06:40:38 GMT20http://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspx[quote][b]Jason-299789 (1/8/2013)[/b][hr]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[code="sql"]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]'[/code][/quote]Ah-ha! Indeed that does seem to fix it. Thought Joe meant Julian day not week day number.Tue, 08 Jan 2013 01:56:49 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxI 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!Tue, 08 Jan 2013 01:47:25 GMTRob-350472http://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxFinal 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[code="sql"]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]'[/code]Tue, 08 Jan 2013 01:30:59 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspx[quote][b]CELKO (1/7/2013)[/b][hr]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 [/quote]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?[code="sql"] 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[/code]Tue, 08 Jan 2013 01:15:26 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspx[quote] Or normalize the components into indexable columns ISOYear, ISOWeek, ISOWeekDay, and avoid the overhead of using LIKE operators.[/quote]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.Mon, 07 Jan 2013 18:28:23 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspx[quote][b]CELKO (1/7/2013)[/b][hr]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 [/quote]Or normalize the components into indexable columns ISOYear, ISOWeek, ISOWeekDay, and avoid the overhead of using LIKE operators.Mon, 07 Jan 2013 13:53:12 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxI 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 Mon, 07 Jan 2013 13:03:07 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxThat's the way to do it!Mon, 07 Jan 2013 09:15:18 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxI'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.Mon, 07 Jan 2013 09:08:05 GMTRob-350472http://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxIt 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.Mon, 07 Jan 2013 08:16:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1403619-392-1.aspxHey 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?Mon, 07 Jan 2013 07:51:15 GMTRob-350472