tag:blogger.com,1999:blog-8585649008635531444.post2930284776411315287..comments2024-03-21T18:00:19.162+05:30Comments on SQL Master: How to create a time dimension in SQL ServerAnonymoushttp://www.blogger.com/profile/13041367988205995767noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-8585649008635531444.post-76680467456769604952011-10-25T06:41:04.862+05:302011-10-25T06:41:04.862+05:30can be use this ssis sql 2008?can be use this ssis sql 2008?crishttps://www.blogger.com/profile/16292714698125818295noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-8796460326485203932011-07-23T00:33:10.989+05:302011-07-23T00:33:10.989+05:30Take a look of the code here...Added Semester, Sem...Take a look of the code here...Added Semester, Semi month and some more columns - <br />CREATE PROCEDURE [dbo].[Os_Populate_Date_Dim]<br />(<br /> @FIRST_DATE DATETIME,<br /> @LAST_DATE DATETIME<br />)<br />AS<br />BEGIN<br /> WITH TE_Os_Dim_Date as <br /> ( <br /> SELECT Cast (@FIRST_DATE as DateTime) Date --Start Date <br /> UNION ALL <br /> SELECT Date + 1 FROM TE_Os_Dim_Date <br /> WHERE Date + 1 < = @LAST_DATE --End date <br /> ) <br /> INSERT INTO [dbo].[Os_Dim_Date] (<br /> [DateKey]<br /> ,[AlternateDateKey]<br /> ,[CalenderDate]<br /> ,[DayOfWeek]<br /> ,[ShortDayOfWeek]<br /> ,[DayNumOfYear]<br /> ,[DayNumOfQuarter]<br /> ,[DayNumOfMonth]<br /> ,[DayNumOfWeek]<br /> ,[WeekName]<br /> ,[WeekNumOfYear]<br /> ,[WeekNumOfQuarter]<br /> ,[WeekNumOfMonth]<br /> ,[MonthName]<br /> ,[MonthShortName]<br /> ,[MonthNumOfYear]<br /> ,[MonthNumOfQuarter]<br /> ,[QuarterName]<br /> ,[QuarterShortName]<br /> ,[Quarter]<br /> ,[YearName]<br /> ,[Year]<br /> ,[SemiMonth]<br /> ,[Semester]<br /> )<br /> SELECT <br /> CAST(CONVERT(NVARCHAR(30),DATE, 112) AS INT) AS DateKey<br /> ,LEFT(CAST(CONVERT(NVARCHAR(30),DATE, 120) AS VARCHAR), 10) AS AlternateDateKey<br /> ,DATE AS CalenderDate<br /> <br /> ,DateName (dw, date) as DayOfWeek<br /> ,LEFT (DateName (dw, date), 3) as ShortDayOfWeek<br /> ,DATEPART (DY, DATE) AS DayNumOfYear <br /> ,DATEDIFF(DD,DATEADD(QQ,DATEDIFF(QQ,0,DATE),0),DATE)+1 AS DayNumOfQuarter <br /> ,DAY (DATE) AS DayNumOfMonth <br /> ,DATEPART (DW, DATE) AS DayNumOfWeek <br /> <br /> ,convert(varchar(4),YEAR(Date))+'-Week-'+<br /> CAST(DatePart (wk, Date) AS VARCHAR) AS WeekName<br /> ,DatePart (wk, Date) as WeekNumOfYear <br /> ,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as WeekNumOfQuarter <br /> ,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as WeekNumOfMonth <br /><br /> ,DateName (mm, date) as MonthName <br /> ,LEFT ( DateName (mm, date), 3) MonthShortName <br /> ,MONTH (date) as MonthNumOfYear <br /> ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as MonthNumOfQuarter <br /> <br /> ,datename(year,Date)+' Q'+ datename(quarter,Date) AS QuarterName<br /> ,'Q'+datename(quarter,Date) AS QuarterShortName<br /> ,DatePart ( qq, date) as Quarter <br /> <br /> ,datename(year,Date) AS YearName<br /> ,YEAR (date) as Year <br /> <br /> ,'SemiMonthP' + CASE WHEN Day(Date) <= 15 then '1' ELSE '2' END + ' ' + DateName (mm, date) + ' ' + datename(year,Date) AS SemiMonth<br /> ,CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS Semester<br /> FROM TE_Os_Dim_Date <br /> <br /> OPTION (MAXRECURSION 0)<br />END<br />GORajeshhttps://www.blogger.com/profile/16320550094151917568noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-28031675422819344502011-04-20T18:46:28.635+05:302011-04-20T18:46:28.635+05:30hai i am new to sql from the above query how can i...hai i am new to sql from the above query how can i set primary key for DateID in select statementAnonymoushttps://www.blogger.com/profile/02060495671975543242noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-90508777757876231512010-07-28T14:39:10.199+05:302010-07-28T14:39:10.199+05:30thanks mangal (humus chips salad ? :-)), it really...thanks mangal (humus chips salad ? :-)), it really helpd us.Anonymoushttps://www.blogger.com/profile/16774504487508475721noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-20791588833380831392010-07-06T21:01:45.577+05:302010-07-06T21:01:45.577+05:30have you an example for oracle
h.sassa@yahoo.frhave you an example for oracle<br />h.sassa@yahoo.frJAVA/J2EEhttps://www.blogger.com/profile/06393189565548181455noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-51125730502485151982010-06-22T09:07:26.998+05:302010-06-22T09:07:26.998+05:30Thank you. It really helped me.
But I have a quest...Thank you. It really helped me.<br />But I have a question. How, if I want to add a semester in this dimension?<br /><br />StephanieStephanie Pamelahttps://www.blogger.com/profile/13010071590386551276noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-75421600392104665542010-06-22T09:07:26.999+05:302010-06-22T09:07:26.999+05:30Thank you. It really helped me.
But I have a quest...Thank you. It really helped me.<br />But I have a question. How, if I want to add a semester in this dimension?<br /><br />StephanieStephanie Pamelahttps://www.blogger.com/profile/13010071590386551276noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-30156115302470899922010-06-17T08:02:45.029+05:302010-06-17T08:02:45.029+05:30Mangal Buddy you are a genius ! It did resolve my ...Mangal Buddy you are a genius ! It did resolve my problem Thank you so much for the above code ! <br /><br />Just one question what if I want the Fiscal Calendar as well in this dimension ? <br /><br />Thank you <br />HemantJangomontyhttps://www.blogger.com/profile/15335749912788571084noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-16354148621243977832009-05-09T19:34:00.000+05:302009-05-09T19:34:00.000+05:30Hi Kapil,
for seconds you can do -
WITH Mangal AS
...Hi Kapil,<br />for seconds you can do -<br />WITH Mangal AS<br />( <br />SELECT Cast ('2009-01-01' as DateTime) Date --Start Date <br />UNION ALL <br />SELECT DateAdd(ss, 1, Date)<br />FROM Mangal <br />WHERE DateAdd(ss, 1, Date) < = '2010-01-01' --End date <br />) <br /><br /><br />But remember the above query, is good when number records are less. When you take seconds as lowest level, number of records can shoots up very high, and a recurssive cte can give bad performamce. With help of Numbers tables you will get performance. If want some example using a NUmbers table, let me know, I can mail you the solution.Anonymoushttps://www.blogger.com/profile/13041367988205995767noreply@blogger.comtag:blogger.com,1999:blog-8585649008635531444.post-91874629703034192892009-05-08T22:58:00.000+05:302009-05-08T22:58:00.000+05:30Thanks this is good. I have a question though, thi...Thanks this is good. I have a question though, this is designed keeping at mind that Day is at the mininmun level . If i need to modify it to show at Second level, how can i do it<br /><br />kapilKapil Guptahttps://www.blogger.com/profile/11997992730624097575noreply@blogger.com