创建时间维度表(有许多时间函数,记下来以后说不定会用到)

来源：互联网发布：cf自动领枪软件编辑：程序博客网时间：2024/04/27 21:51

先说时间维度我们考虑到的字段

函数解决简单的年月日星期季度等数据插入

CONVERT(varchar(100), getdate(), 112) ,--N'日期键值(显示例子'20140101')'

getdate() ,--N'日期全值'

CAST(YEAR(getdate()) AS VARCHAR(10)) + '年' + CAST(MONTH(getdate()) AS VARCHAR(10)) + '月' + CAST(DAY(getdate()) AS VARCHAR(10)) + '日' --显示为2014年1月1日

CONVERT(VARCHAR(10), getdate(), 120),-- N'年月日值(显示例子'2014-01-01')'

DATEPART(weekday, getdate()) ,--N'星期几(这是数字显示7为星期一,1为星期日)'

DATENAME(weekday, getdate()) , --N'星期几(这里语言是什么,就是那种语言的星期显示)'

DATEPART(dayofyear, getdate()) , --N'今年第几天'

DATEPART(week, getdate()) , --N'今年第几周'

DAY(getdate()) ,--N'日期'

CAST(DAY(getdate()) AS VARCHAR(10)) + '日' ,--N'日期名'

MONTH(getdate()) ,--N'月份'

CAST(MONTH(getdate()) AS VARCHAR(10)) + '月' ,--N'月份名'

DATEPART(quarter, getdate()) ,--N'季度'

CAST(DATEPART(quarter, getdate()) AS VARCHAR(10)) + '季度' ,--N'季度名'

YEAR(getdate()) ,--N'年份'

CAST(YEAR(getdate()) AS VARCHAR(10)) + '年' ,--N'年份名'

<span style="font-family:Comic Sans MS;font-size:12px;"><strong>      day(dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate())))--每月最后一天</strong></span>

根据项目特殊的要求,修改对应的表数据

<span style="font-family:Comic Sans MS;"><strong>        --修改星期(英文版)        UPDATE  dbo.Dimdate         SET Dimdate.EnglishDayNameOfWeek = (         SELECT CASE WHEN DATEPART(weekday,getdate()) = 1 THEN 'Sunday'                    WHEN DATEPART(weekday, getdate()) = 2 THEN 'Monday'                    WHEN DATEPART(weekday, getdate()) = 3 THEN 'Tuesday'                    WHEN DATEPART(weekday, getdate()) = 4 THEN 'Wednesday'                    WHEN DATEPART(weekday, getdate()) = 5 THEN 'Thursday'                    WHEN DATEPART(weekday, getdate()) = 6 THEN 'Friday'                    WHEN DATEPART(weekday, getdate()) = 7 THEN 'Saturday'        END) WHERE Dimdate.FullDateKey = getdate()        --修改星期(数字版将星期对应的7123456改为1234567)        UPDATE  dbo.Dimdate        SET     Dimdate.DayNumberOfWeek = ( SELECT CASE WHEN DATEPART(weekday, getdate()) = 1 THEN 7                    WHEN DATEPART(weekday, getdate()) = 2 THEN 1                    WHEN DATEPART(weekday, getdate()) = 3 THEN 2                    WHEN DATEPART(weekday, getdate()) = 4 THEN 3                    WHEN DATEPART(weekday, getdate()) = 5 THEN 4                    WHEN DATEPART(weekday, getdate()) = 6 THEN 5                    WHEN DATEPART(weekday, getdate()) = 7 THEN 6        END ) WHERE Dimdate.FullDateKey = getdate()        --修改月份(英文)        UPDATE  dbo.Dimdate        SET     Dimdate.EnglishMonthName = ( SELECT CASE WHEN MONTH(getdate()) = 1 THEN 'January'WHEN MONTH(getdate()) = 2 THEN 'February'WHEN MONTH(getdate()) = 3 THEN 'March'WHEN MONTH(getdate()) = 4 THEN 'April'WHEN MONTH(getdate()) = 5 THEN 'May'WHEN MONTH(getdate()) = 6 THEN 'June'WHEN MONTH(getdate()) = 7 THEN 'July'WHEN MONTH(getdate()) = 8 THEN 'August'WHEN MONTH(getdate()) = 9 THEN 'September'WHEN MONTH(getdate()) = 10 THEN 'October'WHEN MONTH(getdate()) = 11 THEN 'November'WHEN MONTH(getdate()) = 12 THEN 'December'        END ) WHERE Dimdate.FullDateKey = getdate()        --是否周末(默认添加为0:否)        UPDATE  dbo.Dimdate        SET     Dimdate.IsWeekend = 1 WHERE Dimdate.FullDateKey = getdate()AND DATEPART(weekday,getdate()) in (1,7)         --是否月末(默认添加为0:否)        UPDATE  dbo.Dimdate        SET     Dimdate.MonthEnd = 1 WHERE Dimdate.FullDateKey = getdate()AND Dimdate.FullDateKey = (SELECT DATEADD(day,  -1, DATEADD(month, 1,DATEADD(day,-DATEPART(day, getdate())+1,getdate()))))        --修改半年度[2:下半年](默认添加为1:上半年)        UPDATE  dbo.Dimdate        SET     Dimdate.IntoAccountMidYear = 2,Dimdate.IntoAccountMidYearName = '下半年'WHERE Dimdate.FullDateKey = getdate()AND month(Dimdate.FullDateKey) IN (7,8,9,10,11,12)           --修改扎帐月份(要求以每月6号扎帐,所以1-6号算上个月的)        UPDATE  dbo.Dimdate        SET     Dimdate.IntoAccountMonth = MONTH(dateadd(month,-1,getdate()))WHERE Dimdate.FullDateKey = getdate()AND day(Dimdate.FullDateKey) IN (1,2,3,4,5,6)           --修改扎帐年份(一月1-6号算上一年的)        UPDATE  dbo.Dimdate        SET     Dimdate.IntoAccountYear = YEAR(dateadd(year,-1,getdate())),Dimdate.IntoAccountMidYear = 2,Dimdate.IntoAccountMidYearName = '下半年'WHERE Dimdate.FullDateKey = getdate()AND day(Dimdate.FullDateKey) IN (1,2,3,4,5,6)  AND MONTH(Dimdate.FullDateKey) = 1          --修改扎帐季度(1\4\7\10月的1-6日为上一个季度)        UPDATE  dbo.Dimdate        SET     Dimdate.IntoAccountQuarter = DATEPART(quarter, dateadd(month,-1,getdate()))WHERE Dimdate.FullDateKey = getdate()AND day(Dimdate.FullDateKey) IN (1,2,3,4,5,6)  AND MONTH(Dimdate.FullDateKey) IN (1,4,7,10) </strong></span>

下面解决是否节假日的问题:由于中国的节假日大多都是旧历,所以我们想出的解决方案是添加一个节假日表

表里面就存放一个假期的日期,以"20140101"的方式录入显示,这样就直接与时间维度表的键值相对应了.至于数据就只好幸苦一下了,一条一条的加吧.

下面2012年到2014年的节假日(在万年历上对的,个别需求请特殊处理):

<span style="font-family:Comic Sans MS;font-size:14px;">20120101 20120102 20120103 20120122 20120123 2012012420120125 20120126 20120127 20120128 20120402 2012040320120404 20120429 20120430 20120501 20120622 2012062320120624 20120930 20121001 20121002 20121003 2012100420121005 20121006 2012100720130101 20130102 20130103 20130209 20130210 2013021120130212 20130213 20130214 20130215 20130404 2013040520130406 20130429 20130430 20130501 20130610 2013061120130612 20130919 20130920 20130921 20131001 2013100220131003 20131004 20131005 20131006 2013100720140101 20140131 20140201 20140202 20140203 2014020420140205 20140206 20140207 20140405 20140406 2014040720140501 20140502 20140503 20140531 20140601 2014060220140906 20140907 20140908 20141001 20141002 2014100320141004 20141005 20141006 20141007</span>

下面就修改是否节假日字段,录入时默认0:否

<span style="font-family:Comic Sans MS;font-size:14px;">        --修改节假日(节假日的表)        UPDATE  dbo.Dimdate        SET     Dimdate.IsHoliday = 1WHERE Dimdate.DateKey in (SELECT holiday.[date] FROM dbo.holiday)</span>

好了,这样一个完整的时间维度表大致就建成了.

以前开发没有接触过BI,数据库也是会用就行,所以很多东西也不是特懂,如果有什么语法描述错误的请多多指教,谢谢.

0 0