Hello again and thank you for your continued interest in this in advance. I want to clarify a couple of things real quick in the interests of better understanding. The system I am currently (and hopefully somewhat temporarily) saddled with maintaining uses a procedural process to generate a material identifier, a portion of which is a fixed format "YYWK" representing the last 2 digits of the year and another 2 for the week of the year. Our process is driven by a weekly order cycle, currently with a Monday date representing the order week, but could be designated as any day of the week. This date drives the population of the "YYWK", which is the same for every ID for that week. If the week is the first week of the year, the year value should reflect the year that Jan 1st is in, not necessarily the year of the date given. I did create and post what for now is an acceptable solution, but for both academic interest and potential future improvement I wanted to take this further.
The date data to drive this could be any date, forgive my awkward usage of a CTE (which was fun) to create the test data.
IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
CREATE TABLE #TestTable
(
CalendarKey INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CalendarDate DATETIME,
ISO_YearWeek VARCHAR(4),
Adj_YearWeek VARCHAR(4)
)
SET IDENTITY_INSERT #TestTable ON
;WITH tDates AS
(
SELECT 1 AS CalendarKey,
CAST('12/26/1999' AS DATETIME) AS CalendarDate,
NULL AS ISO_YearWeek,
NULL AS Adj_YearWeek
UNION ALL
SELECT CalendarKey + 1,
CalendarDate + 1 AS CalendarDate,
NULL AS ISO_YearWeek,
NULL AS Adj_YearWeek
FROM tDates
WHERE CalendarDate < '1/4/2020'
)
INSERT #TestTable
(
CalendarKey,
CalendarDate,
ISO_YearWeek,
Adj_YearWeek
)
SELECT CalendarKey,
CalendarDate,
ISO_YearWeek,
Adj_YearWeek
FROM tDates
OPTION (MAXRECURSION 7315)
SET IDENTITY_INSERT #TestTable OFF
Below find the equivalent of what is in place today (ISO), with the somewhat unsatisfactory replacement (Jan 1st based) to determine the "YYWK" value:
UPDATE #TestTable
SET ISO_YearWeek =
CASE
WHEN (MONTH(CalendarDate) = 12 AND DATEPART(ISOWW, CalendarDate) = 1)
THEN RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate) + 1), 2)
ELSE RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate)), 2)
END +
RIGHT('0' + CAST(CONVERT(INT, DATEPART(ISOWW, CalendarDate)) AS VARCHAR), 2)
UPDATE #TestTable
SET Adj_YearWeek =
CASE
WHEN (DATEDIFF( DD, DATEADD(WEEK,DATEDIFF(WEEK,'18991231', (CAST('1/1/' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR) AS DATETIME) )), '18991231'), CalendarDate )) >= 0
THEN (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR), 2)) + '01'
ELSE (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) AS VARCHAR), 2)) + RIGHT( '0' + CAST(DATEPART(WK, CalendarDate) AS VARCHAR), 2)
END
SELECT CalendarDate,
DATENAME(DW,CalendarDate) AS Weekday,
ISO_YearWeek,
Adj_YearWeek
FROM #TestTable
The results illustrate the wanted value in the "Adj" column, and also enlightened me that even our current ISO method would fail if our weekly order date was changed to a Sunday.
CalendarDateWeekdayISO_YearWeekAdj_YearWeek
----------------------- ------------------------------------------------------
2008-12-26 00:00:00.000 Friday08520852
2008-12-27 00:00:00.000 Saturday08520852
2008-12-28 00:00:00.000 Sunday08520901
2008-12-29 00:00:00.000 Monday09010901
2008-12-30 00:00:00.000 Tuesday09010901
2008-12-31 00:00:00.000 Wednesday09010901
2009-01-01 00:00:00.000 Thursday09010901
2009-01-02 00:00:00.000 Friday09010901
2009-01-03 00:00:00.000 Saturday09010901
2009-01-04 00:00:00.000 Sunday09010902
2009-01-05 00:00:00.000 Monday09020902