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