Feeds:
Posts
Comments

Archive for the ‘Data Warehouse’ Category


WITH DimTime as
(
SELECT 
	Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL -- usage of recursion
SELECT 
	Date + 1
FROM 
	DimTime
WHERE 
	Date + 1  <= '2020-12-31' --End date
)

SELECT
	Row_Number() OVER (ORDER BY Date) as ROWID
	,Date
	,YEAR (date) as Year
	,DatePart ( qq, date) as Quarter
	,MONTH (date) as MonthOfYear
	,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as MonthOfQuarter
	,DatePart (wk, Date) as WeekOfYear
	,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as WeekOfQuarter
	,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as WeekOfMonth
	,DatePart (dy, date) as DayOfYear
    ,CASE 
		WHEN DatePart(dy, MinAdDate) Between 1 and 30 Then '1 - 30' 
		WHEN DatePart(dy, MinAdDate) Between 31 and 60 Then '31 - 60' 
		WHEN DatePart(dy, MinAdDate) Between 61 and 90 Then '61 - 90' 
		WHEN DatePart(dy, MinAdDate) Between 91 and 120 Then '91 - 120' 
		WHEN DatePart(dy, MinAdDate) Between 121 and 150 Then '121 - 150' 
		WHEN DatePart(dy, MinAdDate) Between 151 and 180 Then '151 - 180' 
		ELSE '181+'														
	END as DayRangeOfYear
	,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as DayOfQuarter
	,DAY (date) as DayOfMonth
	,DatePart (dw, date) as DayOfWeek
	,DateName (mm, date) as MonthName
	,LEFT ( DateName (mm, date), 3) MonthAbbreviation
	,DateName (dw, date) as DayName
	,LEFT (DateName (dw, date), 3) as DayAbbreviation
	,CONVERT(VarChar(10),date,112) as YYYYMMDD
	,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
	,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
	,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]
FROM
	DimTime
OPTION (MAXRECURSION 0) -- Set Max Recursion

Advertisements

Read Full Post »