Source/NuGetGallery.Operations/Scripts/PopulateDimensions.sql

DELETE [dbo].[Dimension_Date]
GO

-- =============================================
-- Populate DimDate
-- populates the date dimension and inserts the
-- unknown date row.
-- =============================================

IF NOT EXISTS(SELECT * FROM [dbo].[Dimension_Date] WHERE Id = -1)
BEGIN

    SET IDENTITY_INSERT [dbo].[Dimension_Date] ON

    INSERT INTO [dbo].[Dimension_Date] (
/*1*/   [Id]
        ,[Date]
        ,[DateName]
        ,[DayOfWeek]
        ,[DayOfWeekName]
        ,[MonthName]
        ,[WeekdayIndicator]
        ,[DayOfYear]
        ,[WeekOfYear]
/*10*/	,[WeekOfYearName]
        ,[WeekOfYearNameInYear]
        ,[MonthOfYear]
        ,[MonthOfYearName]
        ,[MonthOfYearNameInYear]
        ,[Quarter]
        ,[QuarterName]
        ,[QuarterNameInYear]
        ,[HalfYear]
        ,[HalfYearName]
/*20*/	,[HalfYearNameInYear]
        ,[Year]
        ,[YearName]
        ,[FiscalDayOfYear]
        ,[FiscalWeekOfYear]
        ,[FiscalWeekOfYearName]
        ,[FiscalWeekOfYearNameInYear]
        ,[FiscalMonthOfYear]
        ,[FiscalMonthOfYearName]
        ,[FiscalMonthOfYearNameInYear]
/*30*/  ,[FiscalQuarter]
        ,[FiscalQuarterName]
        ,[FiscalQuarterNameInYear]
        ,[FiscalHalfYear]
        ,[FiscalHalfYearName]
        ,[FiscalHalfYearNameInYear]
        ,[FiscalYear]
/*37*/	,[FiscalYearName])
    VALUES(
/*1*/	-1
        ,null
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,null
/*10*/	,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
/*20*/	,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,null
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
/*30*/	,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
        ,'(Unknown)'
        ,'(Unknown)'
        ,null
/*37*/	,'(Unknown)')

    SET IDENTITY_INSERT [dbo].[Dimension_Date] OFF
    
END
GO

SET XACT_ABORT ON
SET NOCOUNT ON
DECLARE @Date DATE, @EndDate DATE
SET @Date = '2010-01-01'
SET @EndDate = '2020-12-31'

IF (SELECT COUNT(*) FROM [dbo].[Dimension_Date] WHERE Id <> -1) = 0 
BEGIN

    DECLARE @FYDays INT, @FYWeek INT, @FYMonth INT, @FYQuarter INT, @FYYear INT, @FYStartDate DATETIME
     
    WHILE @Date <= @EndDate
    BEGIN
        SET @FYStartDate = '7/1/' + 
            CASE WHEN DATEPART(MONTH, @date) < 7 THEN CAST(DATEPART(YEAR, @date) - 1 AS NVARCHAR(4))
            ELSE CAST(DATEPART(YEAR, @date) AS NVARCHAR(4))
        END
        SET @FYDays = DATEDIFF(DAY, @FYStartDate, @date) + 1
        SET @FYWeek = DATEDIFF(WEEK, @FYStartDate, @date) + 1
        SET @FYMonth = DATEDIFF(MONTH, @FYStartDate, @date) + 1
        SET @FYQuarter = CASE
            WHEN @FYMonth BETWEEN 1 AND 3 THEN 1
            WHEN @FYMonth BETWEEN 4 AND 6 THEN 2
            WHEN @FYMonth BETWEEN 7 AND 9 THEN 3
            WHEN @FYMonth BETWEEN 10 AND 12 THEN 4
        END
        SET @FYYear = DATEPART(YEAR, @FYStartDate) + 1

        INSERT INTO [dbo].[Dimension_Date] ( 
            [Date]
            ,[DateName]
            ,[DayOfWeek]
            ,[DayOfWeekName]
            ,[MonthName]
            ,[WeekdayIndicator]
-- CY			
            ,[DayOfYear]
            ,[WeekOfYear]
            ,[WeekOfYearName]
            ,[WeekOfYearNameInYear]
            ,[MonthOfYear]
            ,[MonthOfYearName]
            ,[MonthOfYearNameInYear]
            ,[Quarter]
            ,[QuarterName]
            ,[QuarterNameInYear]
            ,[HalfYear]
            ,[HalfYearName]
            ,[HalfYearNameInYear]
            ,[Year]
            ,[YearName]
-- FY			
            ,[FiscalDayOfYear]
            ,[FiscalWeekOfYear]
            ,[FiscalWeekOfYearName]
            ,[FiscalWeekOfYearNameInYear]
            ,[FiscalMonthOfYear]
            ,[FiscalMonthOfYearName]
            ,[FiscalMonthOfYearNameInYear]
            ,[FiscalQuarter]
            ,[FiscalQuarterName]
            ,[FiscalQuarterNameInYear]
            ,[FiscalHalfYear]
            ,[FiscalHalfYearName]
            ,[FiscalHalfYearNameInYear]
            ,[FiscalYear]
            ,[FiscalYearName]
        )
        VALUES (
            @date
            ,DATENAME(WEEKDAY, @date) + ', ' + DATENAME(MONTH, @date) + ' ' + DATENAME(DAY, @date) + ' ' + DATENAME(YEAR, @date)
            ,DATEPART(WEEKDAY, @date)
            ,DATENAME(WEEKDAY, @date)
            ,DATENAME(month, @date)
            ,CASE WHEN DATEPART(WEEKDAY, @date) > 1 AND DATEPART(WEEKDAY, @date) < 7 THEN 'Weekday' ELSE 'Weekend' END
-- CY			
            ,DATEPART(DAYOFYEAR, @date)
            ,DATEPART(WEEK, @date)
            ,'Week ' + CAST(DATEPART(WEEK, @date) AS NVARCHAR(2))
            ,'CY ' + DATENAME(YEAR, @date) + '-Week ' + CAST(DATEPART(WEEK, @date) AS NVARCHAR(2))
            ,DATEPART(MONTH, @date)
            ,'Month ' + CAST(DATEPART(MONTH, @date) as nvarchar(2))
            ,'CY ' + DATENAME(YEAR, @date) + '-' + RIGHT(REPLICATE('0',2) + CAST(DATEPART(MONTH, @date) AS NVARCHAR(2)),2)
            ,DATEPART(QUARTER, @date)
            ,'Q' + CAST(DATEPART(QUARTER, @date) AS NVARCHAR(2))
            ,'CY ' + DATENAME(YEAR, @date) + '-' + 'Q' + CAST(DATEPART(QUARTER, @date) AS NVARCHAR(1))
            ,CASE WHEN DATEPART(MONTH, @date) < 7 THEN 1 ELSE 2 END
            ,'H' + CAST(CASE WHEN DATEPART(MONTH, @date) < 7 THEN 1 ELSE 2 END AS NVARCHAR(1))
            ,'CY ' + DATENAME(YEAR, @date) + '-' + 'H' + CAST(CASE WHEN DATEPART(MONTH, @date) < 7 THEN 1 ELSE 2 END AS NVARCHAR(1))
            ,DATEPART(YEAR, @date)
            ,'CY ' + DATENAME(YEAR, @date)
-- FY			
            ,@FYDays
            ,@FYWeek
            ,'Week ' + CAST(@FYWeek AS NVARCHAR(2))
            ,'FY ' + CAST(@FYYear AS NVARCHAR(4)) + '-Week ' + CAST(@FYWeek AS NVARCHAR(2))
            ,@FYMonth
            ,'Month ' + CAST(@FYMonth AS NVARCHAR(2))
            ,'FY ' + CAST(@FYYear AS NVARCHAR(4)) + '-' + RIGHT(REPLICATE('0',2) + CAST(@FYMonth AS NVARCHAR(2)),2)
            ,@FYQuarter
            ,'Q' + CAST(@FYQuarter AS NVARCHAR(2))
            ,'FY ' + CAST(@FYYear AS NVARCHAR(4)) + '-' + 'Q' + CAST(@FYQuarter AS NVARCHAR(2))
            ,CASE WHEN @FYMonth < 7 THEN 1 ELSE 2 END
            ,'H' + CAST(CASE WHEN @FYMonth < 7 THEN 1 ELSE 2 END AS NVARCHAR(1))
            ,'FY ' + CAST(@FYYear AS NVARCHAR(4)) + '-' + 'H' + CAST(CASE WHEN @FYMonth < 7 THEN 1 ELSE 2 END AS NVARCHAR(1))
            ,@FYYear
            ,'FY ' + CAST(@FYYear AS NVARCHAR(4))
        )
                   
        SET @Date = DATEADD(d, 1, @Date)
    END
END
GO

DELETE [dbo].[Dimension_Time]
GO

DECLARE @current INT = 0;
WHILE (@current < 24)
BEGIN
    INSERT [dbo].[Dimension_Time] ( HourOfDay ) VALUES ( @current );
    SELECT @current = @current + 1;
END
GO

DELETE [dbo].[Dimension_Operation]
INSERT [dbo].[Dimension_Operation] VALUES ( 'Install' )
INSERT [dbo].[Dimension_Operation] VALUES ( 'Update' )
INSERT [dbo].[Dimension_Operation] VALUES ( 'Restore' )
INSERT [dbo].[Dimension_Operation] VALUES ( '(unknown)' )
INSERT [dbo].[Dimension_Operation] VALUES ( 'Install-Dependency' )
INSERT [dbo].[Dimension_Operation] VALUES ( 'Update-Dependency' )
INSERT [dbo].[Dimension_Operation] VALUES ( 'Restore-Dependency' )
GO

DELETE [dbo].[Dimension_Project]
INSERT [dbo].[Dimension_Project] VALUES ( '(unknown)' )
GO