none
scrip to create a date dimension

    Question

  • I need to add the field Datekey to the existing, I'm not getting where I'm missing 

    Year                   Week           Date         Hour        Mins    DateKey
    2013                  2              10/8/13       1               15    2013-10-09 01:15:00:000
    2013                  2              10/8/13       1               30    2013-10-09 01:30:00:000
    2013                   2              10/8/13       1               45    2013-10-09 01:45:00:000
    2013                  2              10/8/13       2               00    2013-10-09 02:00:00:000
    2013                  2              10/8/13       2               15    2013-10-09 02:15:00:000
    2013                  2              10/8/13       2               30    2013-10-09 02:30:00:000
    2013                  2              10/8/13       2               45    2013-10-09 02:45:00:000
    2013                  2              10/8/13       3               00    2013-10-09 03:00:00:000
    2013                  2              10/8/13       3               15    2013-10-09 03:15:00:000
    2013                  2              10/8/13       3               30    2013-10-09 03:30:00:000
    2013                   2              10/8/13       3               45    2013-10-09 03:45:00:000
    2013                 2              10/8/13       4               00    2013-10-09 04:00:00:000

    Below is my script, to which I need add the field DateKey





    • Edited by K.Kalyan Thursday, October 10, 2013 4:45 PM edit
    Thursday, October 10, 2013 2:51 AM

Answers

  • Your question is not clear to me but I suggest using a CTE rather than a loop for this task.  For example:

    DECLARE @DimDate datetime = '2013-01-01';
    DECLARE @SourceDate date = '2014-01-01';
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M WHERE num < DATEDIFF(second, @DimDate, @SourceDate) / 15)
    SELECT
    		CAST(DimDate as Date) as [FullDateAlternateKey]
    		,datepart(weekday,DimDate) as [DayNumberOfWeek]
    		,datename(weekday,DimDate) as [DayNameOfWeek]
    		,datepart(day,DimDate) as [DayNumberOfMonth]
    		,datepart(dayofyear,DimDate) as [DayNumberOfYear]
    		,ltrim(str(datepart(week,DimDate))) as [CalendarNumOfYear]
    		,'W'+ltrim(str(datepart(week,DimDate))) as [WeekNumberOfYear]
    		,datename(month,DimDate) as [MonthName]
    		,datepart(month,DimDate) as [MonthNumberOfYear]
    		,'Q' + Ltrim(str(datepart(quarter,DimDate))) as [CalendarQuarter]
    		,'CY ' + Ltrim(str(datepart(year,DimDate))) as [CalendarYear]
    		,case when datepart(quarter,DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester]
    		--,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter]
    		,[dbo].[uf_GetFiscalQuater](DATEADD(second, DimDate, @DimDate)) [FiscalQuarter]
    		--,'FY ' + Ltrim(str(B.FiscalYearNumber)) as [FiscalYear]
    		,'FY ' + CAST((CASE WHEN DATEPART(MM, DimDate) > 6 THEN DATEPART(YY, DimDate) + 1 
    			WHEN DATEPART(MM, DimDate) <=6 THEN DATEPART(YY, DimDate) END ) as varchar(7)) as FiscalYear
    		, [dbo].[uf_GetFiscalSemester](DATEADD(second, DimDate, @DimDate))as [FiscalSemester]
    		,Convert(nvarchar,DimDate,101) as [strDate]
    		--,B.CalendarMonthName as [FiscalMonthName]
    		--,B.CalendarMonthNumber as [FiscalMonthNumofYear]
    		,datename(month,DimDate) as FiscalMonthName
    		,datepart(month,DimDate) as FiscalMonthNumofYear
    		,ltrim(str(datepart (week, DimDate))) as [FiscalNumofMonth]
    		,'W' + ltrim(str(datepart (week, DimDate))) as [FiscalWeekNumofMonth]
    		,DATEPART(hour, DimDate) AS [Hour]
    		,DATEPART(minute, DimDate) AS [Mins]
    FROM dates;
    
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by K.Kalyan Thursday, October 10, 2013 4:46 PM
    Thursday, October 10, 2013 4:50 AM

All replies

  • Your question is not clear to me but I suggest using a CTE rather than a loop for this task.  For example:

    DECLARE @DimDate datetime = '2013-01-01';
    DECLARE @SourceDate date = '2014-01-01';
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M WHERE num < DATEDIFF(second, @DimDate, @SourceDate) / 15)
    SELECT
    		CAST(DimDate as Date) as [FullDateAlternateKey]
    		,datepart(weekday,DimDate) as [DayNumberOfWeek]
    		,datename(weekday,DimDate) as [DayNameOfWeek]
    		,datepart(day,DimDate) as [DayNumberOfMonth]
    		,datepart(dayofyear,DimDate) as [DayNumberOfYear]
    		,ltrim(str(datepart(week,DimDate))) as [CalendarNumOfYear]
    		,'W'+ltrim(str(datepart(week,DimDate))) as [WeekNumberOfYear]
    		,datename(month,DimDate) as [MonthName]
    		,datepart(month,DimDate) as [MonthNumberOfYear]
    		,'Q' + Ltrim(str(datepart(quarter,DimDate))) as [CalendarQuarter]
    		,'CY ' + Ltrim(str(datepart(year,DimDate))) as [CalendarYear]
    		,case when datepart(quarter,DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester]
    		--,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter]
    		,[dbo].[uf_GetFiscalQuater](DATEADD(second, DimDate, @DimDate)) [FiscalQuarter]
    		--,'FY ' + Ltrim(str(B.FiscalYearNumber)) as [FiscalYear]
    		,'FY ' + CAST((CASE WHEN DATEPART(MM, DimDate) > 6 THEN DATEPART(YY, DimDate) + 1 
    			WHEN DATEPART(MM, DimDate) <=6 THEN DATEPART(YY, DimDate) END ) as varchar(7)) as FiscalYear
    		, [dbo].[uf_GetFiscalSemester](DATEADD(second, DimDate, @DimDate))as [FiscalSemester]
    		,Convert(nvarchar,DimDate,101) as [strDate]
    		--,B.CalendarMonthName as [FiscalMonthName]
    		--,B.CalendarMonthNumber as [FiscalMonthNumofYear]
    		,datename(month,DimDate) as FiscalMonthName
    		,datepart(month,DimDate) as FiscalMonthNumofYear
    		,ltrim(str(datepart (week, DimDate))) as [FiscalNumofMonth]
    		,'W' + ltrim(str(datepart (week, DimDate))) as [FiscalWeekNumofMonth]
    		,DATEPART(hour, DimDate) AS [Hour]
    		,DATEPART(minute, DimDate) AS [Mins]
    FROM dates;
    
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by K.Kalyan Thursday, October 10, 2013 4:46 PM
    Thursday, October 10, 2013 4:50 AM
  • Error message when I run the above query:

    Argument data type datetime is invalid for argument 2 of dateadd function.

    Thursday, October 10, 2013 5:04 AM
  • Dan's script is working fine for me though I commented the functions as I dont have that function definition). 

    You may check the below :

    1. Check the @DimDate datatype. It should be datetime.

    DECLARE @DimDate datetime = '2013-01-01';


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Thursday, October 10, 2013 5:18 AM
  • The compatibility_level is 110, Yes the data type is datetime for the variable @DimDate and I still get the same error
    Thursday, October 10, 2013 5:30 AM
  • Can you share your code, let me try in my system whether I can reproduce your issue? You may also comment your function and try it also to pinpoint the issues.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 10, 2013 5:35 AM
  • Here the query I'm running

    DECLARE @DimDate datetime = '2013-01-01'
    DECLARE @SourceDate datetime = '2014-01-01';
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M WHERE num < DATEDIFF(second, @DimDate, @SourceDate) / 15
    	)
    SELECT
    		CAST(DimDate as Date) as [FullDateAlternateKey]
    		,datepart(weekday,DimDate) as [DayNumberOfWeek]
    		,datename(weekday,DimDate) as [DayNameOfWeek]
    		,datepart(day,DimDate) as [DayNumberOfMonth]
    		,datepart(dayofyear,DimDate) as [DayNumberOfYear]
    		,ltrim(str(datepart(week,DimDate))) as [CalendarNumOfYear]
    		,'W'+ltrim(str(datepart(week,DimDate))) as [WeekNumberOfYear]
    		,datename(month,DimDate) as [MonthName]
    		,datepart(month,DimDate) as [MonthNumberOfYear]
    		,'Q' + Ltrim(str(datepart(quarter,DimDate))) as [CalendarQuarter]
    		,'CY ' + Ltrim(str(datepart(year,DimDate))) as [CalendarYear]
    		,case when datepart(quarter,DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester]
    		--,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter]
    		,[dbo].[uf_GetFiscalQuater](DATEADD(second, DimDate, @DimDate)) [FiscalQuarter]
    		--,'FY ' + Ltrim(str(B.FiscalYearNumber)) as [FiscalYear]
    		,'FY ' + CAST((CASE WHEN DATEPART(MM, DimDate) > 6 THEN DATEPART(YY, DimDate) + 1 
    			WHEN DATEPART(MM, DimDate) <=6 THEN DATEPART(YY, DimDate) END ) as varchar(7)) as FiscalYear
    		, [dbo].[uf_GetFiscalSemester](DATEADD(second, DimDate, @DimDate))as [FiscalSemester]
    		,Convert(nvarchar,DimDate,101) as [strDate]
    		--,B.CalendarMonthName as [FiscalMonthName]
    		--,B.CalendarMonthNumber as [FiscalMonthNumofYear]
    		,datename(month,DimDate) as FiscalMonthName
    		,datepart(month,DimDate) as FiscalMonthNumofYear
    		,ltrim(str(datepart (week, DimDate))) as [FiscalNumofMonth]
    		,'W' + ltrim(str(datepart (week, DimDate))) as [FiscalWeekNumofMonth]
    		,DATEPART(hour, DimDate) AS [Hour]
    		,DATEPART(minute, DimDate) AS [Mins]
    FROM dates
    
    --Select compatibility_level,* From sys.databases


    Thursday, October 10, 2013 5:37 AM
  • >DATEADD(second, DimDate, @DimDate)

    The message says that the second parameter should not be datetime. It should be a number.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, October 10, 2013 5:52 AM
  • Try the below:

    DECLARE @DimDate datetime = '2013-01-01'
    DECLARE @SourceDate datetime = '2014-01-01';
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M WHERE num < DATEDIFF(second, @DimDate, @SourceDate) / 15
    	)
    SELECT
    		CAST(DimDate as Date) as [FullDateAlternateKey]
    		,datepart(weekday,DimDate) as [DayNumberOfWeek]
    		,datename(weekday,DimDate) as [DayNameOfWeek]
    		,datepart(day,DimDate) as [DayNumberOfMonth]
    		,datepart(dayofyear,DimDate) as [DayNumberOfYear]
    		,ltrim(str(datepart(week,DimDate))) as [CalendarNumOfYear]
    		,'W'+ltrim(str(datepart(week,DimDate))) as [WeekNumberOfYear]
    		,datename(month,DimDate) as [MonthName]
    		,datepart(month,DimDate) as [MonthNumberOfYear]
    		,'Q' + Ltrim(str(datepart(quarter,DimDate))) as [CalendarQuarter]
    		,'CY ' + Ltrim(str(datepart(year,DimDate))) as [CalendarYear]
    		,case when datepart(quarter,DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester]
    		--,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter]
    		,[dbo].[uf_GetFiscalQuater](DimDate) [FiscalQuarter]
    		--,'FY ' + Ltrim(str(B.FiscalYearNumber)) as [FiscalYear]
    		,'FY ' + CAST((CASE WHEN DATEPART(MM, DimDate) > 6 THEN DATEPART(YY, DimDate) + 1 
    			WHEN DATEPART(MM, DimDate) <=6 THEN DATEPART(YY, DimDate) END ) as varchar(7)) as FiscalYear
    		, [dbo].[uf_GetFiscalSemester](DimDate)as [FiscalSemester]
    		,Convert(nvarchar,DimDate,101) as [strDate]
    		--,B.CalendarMonthName as [FiscalMonthName]
    		--,B.CalendarMonthNumber as [FiscalMonthNumofYear]
    		,datename(month,DimDate) as FiscalMonthName
    		,datepart(month,DimDate) as FiscalMonthNumofYear
    		,ltrim(str(datepart (week, DimDate))) as [FiscalNumofMonth]
    		,'W' + ltrim(str(datepart (week, DimDate))) as [FiscalWeekNumofMonth]
    		,DATEPART(hour, DimDate) AS [Hour]
    		,DATEPART(minute, DimDate) AS [Mins]
    FROM dates


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 10, 2013 5:53 AM
  • Hi Toth, is the change you are talking at 
    WHERE num >DATEADD(second, DimDate, @DimDate))
    DECLARE @DimDate datetime = '2013-01-01'
    DECLARE @SourceDate datetime = '2014-01-01';
    WITH 	t4 AS (	SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M  WHERE num >DATEADD(second, DimDate, @DimDate))

    Thursday, October 10, 2013 6:03 AM
  • Hi Latheesh, I'm getting the worng results with the above query.
    Thursday, October 10, 2013 6:11 AM
  • Hi Latheesh, I'm getting the worng results with the above query.

    I am not sure of your fiscalquater logic. To be simple,

    Can you try the below and let me know if that works for you?

    DECLARE @DimDate datetime = '2013-01-01'
    DECLARE @SourceDate datetime = '2014-01-01';
    WITH 
    	t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    	,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    	,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
    	,dates AS (SELECT DATEADD(second, num*15, @DimDate) AS DimDate FROM t16M WHERE num < DATEDIFF(second, @DimDate, @SourceDate) / 15
    	)
    SELECT
    		CAST(DimDate as Date) as [FullDateAlternateKey]
    		,datepart(weekday,DimDate) as [DayNumberOfWeek]
    		,datename(weekday,DimDate) as [DayNameOfWeek]
    		,datepart(day,DimDate) as [DayNumberOfMonth]
    		,datepart(dayofyear,DimDate) as [DayNumberOfYear]
    		,ltrim(str(datepart(week,DimDate))) as [CalendarNumOfYear]
    		,'W'+ltrim(str(datepart(week,DimDate))) as [WeekNumberOfYear]
    		,datename(month,DimDate) as [MonthName]
    		,datepart(month,DimDate) as [MonthNumberOfYear]
    		,'Q' + Ltrim(str(datepart(quarter,DimDate))) as [CalendarQuarter]
    		,'CY ' + Ltrim(str(datepart(year,DimDate))) as [CalendarYear]
    		,case when datepart(quarter,DimDate) < 3 then 'H1' else 'H2' end as [CalendarSemester]
    		--,'Q' + Ltrim(str(B.FiscalQuarterNumber)) as [FiscalQuarter]
    		--,[dbo].FiscalDay(dateadd(second,DATEDIFF(second, DimDate, @DimDate),@DimDate)) [FiscalQuarter]
    		,datepart(qq, DimDate) 
    		--,'FY ' + Ltrim(str(B.FiscalYearNumber)) as [FiscalYear]
    		,'FY ' + CAST((CASE WHEN DATEPART(MM, DimDate) > 6 THEN DATEPART(YY, DimDate) + 1 
    			WHEN DATEPART(MM, DimDate) <=6 THEN DATEPART(YY, DimDate) END ) as varchar(7)) as FiscalYear
    		--, [dbo].[uf_GetFiscalSemester](DATEADD(second, DimDate, @DimDate))as [FiscalSemester]
    		,Convert(nvarchar,DimDate,101) as [strDate]
    		--,B.CalendarMonthName as [FiscalMonthName]
    		--,B.CalendarMonthNumber as [FiscalMonthNumofYear]
    		,datename(month,DimDate) as FiscalMonthName
    		,datepart(month,DimDate) as FiscalMonthNumofYear
    		,ltrim(str(datepart (week, DimDate))) as [FiscalNumofMonth]
    		,'W' + ltrim(str(datepart (week, DimDate))) as [FiscalWeekNumofMonth]
    		,DATEPART(hour, DimDate) AS [Hour]
    		,DATEPART(minute, DimDate) AS [Mins]
    FROM dates
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 10, 2013 6:52 AM