Load Dim_date ( i have loaded 2023)

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DimDate')) BEGIN DROP TABLE DimDate END go GO CREATE TABLE [dbo].[DimDate] ( [DateKey] INT primary key, [Date] DATETIME, [FullDate] CHAR(10),-- Date in MM-dd-yyyy format [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday [DayOfWeek] CHAR(1),-- First Day Sunday=1 and Saturday=7 [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month [DayOfWeekInYear] VARCHAR(2), [DayOfQuarter] VARCHAR(3), [DayOfYear] VARCHAR(3), [WeekOfMonth] VARCHAR(1),-- Week Number of Month [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter [WeekOfYear] VARCHAR(2),--Week Number of the Year [Month] VARCHAR(2), --Number of the Month 1 to 12 [MonthName] VARCHAR(9),--January, February etc [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter [Quarter] CHAR(1), [QuarterName] VARCHAR(9),--First,Second.. [Year] CHAR(4),-- Year value of Date stored in Row [YearName] CHAR(7), --CY 2012,CY 2013 [MonthYear] CHAR(10), --Jan-2013,Feb-2013 [MMYYYY] CHAR(6), [FirstDayOfMonth] DATE, [LastDayOfMonth] DATE, [FirstDayOfQuarter] DATE, [LastDayOfQuarter] DATE, [FirstDayOfYear] DATE, [LastDayOfYear] DATE ) GO /********************************************************************************************/ --Specify Start Date and End date here --Value of Start Date Must be Less than Your End Date DECLARE @StartDate DATETIME = '01/01/2023' --Starting value of Date Range DECLARE @EndDate DATETIME = '12/31/2023' --End Value of Date Range --Temporary Variables To Hold the Values During Processing of Each Date of Year DECLARE @DayOfWeekInMonth INT, @DayOfWeekInYear INT, @DayOfQuarter INT, @WeekOfMonth INT, @CurrentYear INT, @CurrentMonth INT, @CurrentQuarter INT /*Table Data type to store the day of week count for the month and year*/ DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT) INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0) --Extract and assign various parts of Values from Current Date to Variable DECLARE @CurrentDate AS DATETIME = @StartDate SET @CurrentMonth = DATEPART(MM, @CurrentDate) SET @CurrentYear = DATEPART(YY, @CurrentDate) SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) /********************************************************************************************/ --Proceed only if Start Date(Current date ) is less than End date you specified above WHILE @CurrentDate < @EndDate BEGIN /*Begin day of week logic*/ /*Check for Change in Month of the Current date if Month changed then Change variable value*/ IF @CurrentMonth != DATEPART(MM, @CurrentDate) BEGIN UPDATE @DayOfWeek SET MonthCount = 0 SET @CurrentMonth = DATEPART(MM, @CurrentDate) END /* Check for Change in Quarter of the Current date if Quarter changed then change Variable value*/ IF @CurrentQuarter != DATEPART(QQ, @CurrentDate) BEGIN UPDATE @DayOfWeek SET QuarterCount = 0 SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) END /* Check for Change in Year of the Current date if Year changed then change Variable value*/ IF @CurrentYear != DATEPART(YY, @CurrentDate) BEGIN UPDATE @DayOfWeek SET YearCount = 0 SET @CurrentYear = DATEPART(YY, @CurrentDate) END -- Set values in table data type created above from variables UPDATE @DayOfWeek SET MonthCount = MonthCount + 1, QuarterCount = QuarterCount + 1, YearCount = YearCount + 1 WHERE DOW = DATEPART(DW, @CurrentDate) SELECT @DayOfWeekInMonth = MonthCount, @DayOfQuarter = QuarterCount, @DayOfWeekInYear = YearCount FROM @DayOfWeek WHERE DOW = DATEPART(DW, @CurrentDate) /*End day of week logic*/ /* Populate Your Dimension Table with values*/ INSERT INTO [dbo].[DimDate] SELECT CONVERT (char(8),@CurrentDate,112) as DateKey, @CurrentDate AS Date, CONVERT (char(10),@CurrentDate,101) as FullDate, DATEPART(DD, @CurrentDate) AS DayOfMonth, DATENAME(DW, @CurrentDate) AS DayName, DATEPART(DW, @CurrentDate) AS DayOfWeek, @DayOfWeekInMonth AS DayOfWeekInMonth, @DayOfWeekInYear AS DayOfWeekInYear, @DayOfQuarter AS DayOfQuarter, DATEPART(DY, @CurrentDate) AS DayOfYear, DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS WeekOfMonth, (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS WeekOfQuarter, DATEPART(WW, @CurrentDate) AS WeekOfYear, DATEPART(MM, @CurrentDate) AS Month, DATENAME(MM, @CurrentDate) AS MonthName, CASE WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1 WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2 WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3 END AS MonthOfQuarter, DATEPART(QQ, @CurrentDate) AS Quarter, CASE DATEPART(QQ, @CurrentDate) WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END AS QuarterName, DATEPART(YEAR, @CurrentDate) AS Year, 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName, LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MonthYear, RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS LastDayOfMonth, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter, DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter, CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS FirstDayOfYear, CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS LastDayOfYear SET @CurrentDate = DATEADD(DD, 1, @CurrentDate) END /********************************************************************************************/ go SELECT * FROM [dbo].[DimDate] INSERT INTO dbo.DimDate ( DateKey, Date, FullDate, DayOfMonth, DayName, DayOfWeek, DayOfWeekInMonth, DayOfWeekInYear, DayOfQuarter, DayOfYear, WeekOfMonth, WeekOfQuarter, WeekOfYear, Month, MonthName, MonthOfQuarter, Quarter, QuarterName, Year, YearName, MonthYear, MMYYYY, FirstDayOfMonth, LastDayOfMonth, FirstDayOfQuarter, LastDayOfQuarter, FirstDayOfYear, LastDayOfYear ) VALUES ( 20231231, -- DateKey - int '2023-12-31 00:00:00.000', -- Date - datetime '12/31/2023', -- FullDate - char(10) '31', -- DayOfMonth - varchar(2) 'Sunday', -- DayName - varchar(9) '1', -- DayOfWeek - char(1) '6', -- DayOfWeekInMonth - varchar(2) '53', -- DayOfWeekInYear - varchar(2) '14', -- DayOfQuarter - varchar(3) '365', -- DayOfYear - varchar(3) '6', -- WeekOfMonth - varchar(1) '14', -- WeekOfQuarter - varchar(2) '53', -- WeekOfYear - varchar(2) '12', -- Month - varchar(2) 'December', -- MonthName - varchar(9) '3', -- MonthOfQuarter - varchar(2) '4', -- Quarter - char(1) 'Fourth', -- QuarterName - varchar(9) '2023', -- Year - char(4) 'CY 2023', -- YearName - char(7) 'Dec-2023 ', -- MonthYear - char(10) '122023', -- MMYYYY - char(6) '2023-12-01', -- FirstDayOfMonth - date '2023-12-31', -- LastDayOfMonth - date '2023-10-01', -- FirstDayOfQuarter - date '2023-12-31', -- LastDayOfQuarter - date '2023-01-01', -- FirstDayOfYear - date '2023-12-31' -- LastDayOfYear - date ) --another example --INSERT INTO [dbo].[Dim_Date] -- ([Date_Type] -- ,[Full_Date] -- ,[Full_Date_BI] -- ,[Year_Month_Number] -- ,[Year_Number] -- ,[Day_Of_Week_Number] -- ,[Day_Of_Month_Number] -- ,[Day_Of_Quarter_Number] -- ,[Day_Of_Year_Number] -- ,[Day_Name] -- ,[Week_Of_Month_Number] -- ,[Week_Of_Quarter_Number] -- ,[Week_Of_Year_Number] -- ,[Month_Of_Quarter_Number] -- ,[Month_Of_Year_Number] -- ,[Month_Name] -- ,[Abbr_Month_Name] -- ,[Quarter_Of_Year_Number] -- ,[Quarter_Name] -- ,[is_Today] -- ,[is_Current_Week] -- ,[is_Last_Week] -- ,[is_Current_Month] -- ,[is_Last_Month] -- ,[is_Last_Day_Of_Month] -- ,[is_Last_30_Days] -- ,[is_Last_28_Days] -- ,[is_Last_12_Weeks] -- ,[is_Last_12_Months] -- ,[is_Holiday] -- ,[Sales_Start_Time] -- ,[Sales_End_Time] -- ,[Support_Start_Time] -- ,[Support_End_Time] -- ,[Dialer_Start_Time] -- ,[Dialer_End_Time] -- ,[Lead_Business_Day] -- ,[Sale_Business_Day] -- ,[Shipping_Day_Num] -- ,[Insert_Audit_Key] -- ,[Update_Audit_Key]) SELECT 'Date' AS Date_type , [Date] AS FULL_date ,[Date] AS FULL_date_BI ,CASE WHEN [MonthName] = 'January' THEN '202301' WHEN [MonthName] = 'February' THEN '202302' WHEN [MonthName] = 'March' THEN '202303' WHEN [MonthName] = 'April' THEN '202304' WHEN [MonthName] = 'May' THEN '202305' WHEN [MonthName] = 'June' THEN '202306' WHEN [MonthName] = 'July' THEN '202307' WHEN [MonthName] = 'August' THEN '202308' WHEN [MonthName] = 'September' THEN '202309' WHEN [MonthName] = 'October' THEN '202310' WHEN [MonthName] = 'November' THEN '202311' WHEN [MonthName] = 'December' THEN '202312' ELSE NULL END AS Year_month_Number, [Year] AS Year_Number, [Dayofweek] AS Day_Of_Week_Number ,[dayofmonth] AS Day_of_Month_number ,dayofQuarter AS Day_of_Quarter_number---? Have to fix this ,[DayOFyear] AS day_of_year_number ,[dayname] AS Day_name ,[Weekofmonth] AS week_of_month_number ,[WeekofQuarter] AS week_of_Quarter_number ,WeekOfyear AS Week_of_year_number ,MonthofQuarter AS Month_Of_Quarter_number ,[Month] AS Month_of_year_number ,[MonthName] AS Month_name , CASE WHEN [MonthName] = 'January' THEN 'Jan' WHEN [MonthName] = 'February' THEN 'Feb' WHEN [MonthName] = 'March' THEN 'Mar' WHEN [MonthName] = 'April' THEN 'Apr' WHEN [MonthName] = 'May' THEN 'May' WHEN [MonthName] = 'June' THEN 'Jun' WHEN [MonthName] = 'July' THEN 'Jul' WHEN [MonthName] = 'August' THEN 'Aug' WHEN [MonthName] = 'September' THEN 'Sep' WHEN [MonthName] = 'October' THEN 'Oct' WHEN [MonthName] = 'November' THEN 'Nov' WHEN [MonthName] = 'December' THEN 'Dec' ELSE NULL END AS Abbr_month_name ,[Quarter] AS Quarter_of_Year_name ,Quartername AS Quarter_name ,0 AS is_today ,0 AS is_current_week ,0 AS is_last_week ,0 AS is_current_Month ,0 AS is_last_month ,0 AS is_last_day_of_month --case add here ---ned to fix case when month =02 and date 28 then 1 ---like that addd case ,0 AS is_last_30_Days ,0 AS is_last_28_days ,0 AS is_last_12_weeks ,0 AS is_last_12_months ,0 as is_holiday , CASE WHEN [DayName]= 'Sunday' THEN '10:00:00' WHEN [DayName]= 'Saturday' THEN '10:00:00' WHEN [DayName]= 'Friday' THEN '08:00:00' WHEN [DayName]= 'Thursday' THEN '08:00:00' WHEN [DayName]= 'Wednesday' THEN '08:00:00' WHEN [DayName]= 'Tuesday' THEN '08:00:00' WHEN [DayName]= 'Monday' THEN '08:00:00' ELSE NULL END AS sales_start_Time , CASE WHEN [DayName]='Sunday' THEN '19:00:00' WHEN [DayName]='Saturday' THEN '19:00:00' WHEN [DayName]='Friday' THEN '23:00:00' WHEN [DayName]='Thursday' THEN '23:00:00' WHEN [DayName]='Wednesday' THEN '23:00:00' WHEN [DayName]='Tuesday' THEN '23:00:00' WHEN [DayName]='Monday' THEN '23:00:00' ELSE NULL END AS sales_End_Time , CASE WHEN [DayName]='Sunday' THEN '19:00:00' WHEN [DayName]='Saturday' THEN '19:00:00' WHEN [DayName]='Friday' THEN '23:00:00' WHEN [DayName]='Thursday' THEN '23:00:00' WHEN [DayName]='Wednesday' THEN '23:00:00' WHEN [DayName]='Tuesday' THEN '23:00:00' WHEN [DayName]='Monday' THEN '23:00:00' ELSE NULL END AS Support_Start_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Support_End_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Dialer_Start_Time , CASE WHEN [DayName]= 'Sunday' THEN '19:00:00' WHEN [DayName]= 'Saturday' THEN '19:00:00' WHEN [DayName]= 'Friday' THEN '23:00:00' WHEN [DayName]= 'Thursday' THEN '23:00:00' WHEN [DayName]= 'Wednesday' THEN '23:00:00' WHEN [DayName]= 'Tuesday' THEN '23:00:00' WHEN [DayName]= 'Monday' THEN '23:00:00' ELSE NULL END AS Dialer_End_Time , CASE WHEN [DayName]= 'Sunday' THEN '0.39' WHEN [DayName]= 'Saturday' THEN '0.46' WHEN [DayName]= 'Friday' THEN '0.69' WHEN [DayName]= 'Thursday' THEN '0.75' WHEN [DayName]= 'Wednesday' THEN '0.83' WHEN [DayName]= 'Tuesday' THEN '0.84' WHEN [DayName]= 'Monday' THEN '1.00' ELSE NULL END AS Lead_Business_day , CASE WHEN [DayName]= 'Sunday' THEN '0.30' WHEN [DayName]= 'Saturday' THEN '0.70' WHEN [DayName]= 'Friday' THEN '0.71' WHEN [DayName]= 'Thursday' THEN '0.84' WHEN [DayName]= 'Wednesday' THEN '0.83' WHEN [DayName]= 'Tuesday' THEN '1,00' WHEN [DayName]= 'Monday' THEN '0.06' ELSE NULL END AS Lead_Business_day --,Shipping_day_num --have to fix this ---increment need to fix this , 681161 AS Insert_audit_key ,-1 AS Update_Audit_key FROM AuditDB.[dbo].[DimDate]