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;