Создание измерения времени для BI

Небольшой запрос, с помощью которого можно раз и навсегда решить вопрос с построением измерения времени для бизнес анализа.


/* На случай если согласно настройкам на сервере неделя
начинается с воскресенья, принудительно меняем начало
недели на понедельник.*/
SET DATEFIRST 1;

WITH temp as
(
SELECT Cast (‘2000-01-01’ as DateTime) Date –Start Date 
UNION ALL
SELECT Date + 1
FROM temp
WHERE Date + 1 <= '2099-12-31' --End date  ) SELECT Row_Number() OVER (ORDER BY Date) as ID ,Date ,YEAR (date) as Year ,DatePart ( qq, date) as Quarter ,MONTH (date) as [Month_Number_Of_Year] ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Month_Number_Of_Quarter] ,DatePart (wk, Date) as [Week_Number_Of_Year] ,DateDiff(wk,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Week_Number_Of_Quarter] ,DateDiff(wk,DateAdd(mm,DateDiff(mm,0,date),0),date)+1 as [Week_Number_Of_Month] ,DatePart (dy, date) as [Day_Number_Of_Year] ,DateDiff(dd,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as [Day_Number_Of_Quarter] ,DAY (date) as [Day_Number_Of_Month] ,DatePart (dw, date) as [Day_Number_Of_Week] ,DateName (mm, date) as [English_Month_Name] ,LEFT ( DateName (mm, date), 3) [English_Month_Name_Abbrevation] ,DateName (dw, date) as [English_Day_Name] ,LEFT (DateName (dw, date), 3) as [English_Day_Name_Abbrevation] ,CONVERT(VarChar(10),date,112) as YYYYMMDD INTO DimDate FROM temp OPTION (MAXRECURSION 0) [/sql]

Запись опубликована в рубрике Business Intelligence, Tips and Tricks, TSQL с метками , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *