Last active
October 4, 2019 22:30
-
-
Save sbealer/327ec8ab7c2814d9064bb52a1f5ea3bb to your computer and use it in GitHub Desktop.
Retail 4-5-4 calendar TSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE YOURDB; | |
CREATE TABLE DBO.RETAIL_454_CALENDAR (RETAIL_DATE DATE, RETAIL_WEEK INT, RETAIL_YEAR INT); | |
TRUNCATE TABLE DBO.RETAIL_454_CALENDAR; | |
/* Do not pick an arbitrary start date. This script relies on the correct start date for Retail year 2012 */ | |
DECLARE @BEGIN_DATE date = '29-Jan-2012' | |
DECLARE @END_DATE DATE = '01-May-2090' | |
DECLARE @WEEK_START_DATE DATE = @BEGIN_DATE; | |
DECLARE @DATES_TABLE TABLE (DT DATE); | |
DECLARE @JAN_DAYS_COUNT INT; | |
DECLARE @RETAIL_WEEK_NUMBER INT = 1; | |
DECLARE @RETAIL_YEAR INT = DATEPART(YEAR, @BEGIN_DATE); | |
BEGIN | |
SET NOCOUNT ON; | |
WHILE @BEGIN_DATE <= @END_DATE | |
BEGIN | |
IF @RETAIL_WEEK_NUMBER = 54 | |
BEGIN | |
SET @RETAIL_WEEK_NUMBER = 1; | |
SET @RETAIL_YEAR = @RETAIL_YEAR + 1; | |
END; | |
/*Load 7 days */ | |
WITH DT_CTE AS ( | |
SELECT CAST(@BEGIN_DATE AS DATE) AS DT, 1 AS date_number | |
UNION ALL | |
SELECT DATEADD(DAY, 1, DT), DATE_NUMBER + 1 | |
FROM DT_CTE | |
WHERE DATE_NUMBER < 7 | |
) | |
INSERT INTO @DATES_TABLE | |
SELECT DT FROM DT_CTE | |
ORDER BY DT asc; | |
IF @RETAIL_WEEK_NUMBER = 53 | |
BEGIN | |
SELECT @JAN_DAYS_COUNT = COUNT(*) FROM @DATES_TABLE WHERE DATEPART(MM,DT) = 1; | |
IF @JAN_DAYS_COUNT < 4 | |
BEGIN | |
PRINT 'There were less than 4 days of Jan in week 53. Retail week number set to 1'; | |
SET @RETAIL_WEEK_NUMBER = 1 | |
SET @RETAIL_YEAR = @RETAIL_YEAR + 1; | |
END; | |
ELSE | |
BEGIN | |
PRINT @RETAIL_YEAR; | |
PRINT 'There were more than 4 days of Jan in week 53. Adding week 53'; | |
END; | |
END; | |
INSERT INTO DBO.RETAIL_454_CALENDAR | |
SELECT DT, @RETAIL_WEEK_NUMBER, @RETAIL_YEAR FROM @DATES_TABLE; | |
SET @BEGIN_DATE = DATEADD(DAY, 7, @BEGIN_DATE); | |
SET @RETAIL_WEEK_NUMBER = @RETAIL_WEEK_NUMBER + 1; | |
DELETE FROM @DATES_TABLE; | |
END; | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment