#title Dim_Date
[[TableOfContents]]

==== Type1 ====
{{{
WITH Dim_Date
AS
(
        SELECT 1 Seq, CONVERT(datetime, '20030101') DT 
        UNION ALL
        SELECT Seq + 1, DATEADD(hh, 1, DT) FROM Dim_Date
        WHERE DATEADD(hh, 1, DT) <= GETDATE()
)
SELECT 
	Seq DateKey
,	CONVERT(int, CONVERT(char(8), DT, 112)) DT_YYYYMMDD_int
,	DATEPART(hh, DT) DT_HH_tinyint
,	CONVERT(char(8), DT, 112) DT_YYYYMMDD_char
,	CONVERT(char(2), DT, 108) DT_HH_char
,	DT DT_DateTime
,	DATEPART(yyyy, DT) YYYY
,	CASE WHEN DATEPART(mm,DT) < 7 THEN 'H1' ELSE 'H2' END HALF
,	DATEPART(qq, DT) QQ
,	DATEPART(mm, DT) MM
,	DATEPART(dd, DT) DD
,	DATEPART(dy, DT) DY
,	DATEPART(week, DT) WEEK
,	DATEPART(weekday, DT) WEEKDAY
,	CASE 
		WHEN DATEPART(weekday, DT) = 1 THEN '일'
		WHEN DATEPART(weekday, DT) = 2 THEN '월'
		WHEN DATEPART(weekday, DT) = 3 THEN '화'
		WHEN DATEPART(weekday, DT) = 4 THEN '수'
		WHEN DATEPART(weekday, DT) = 5 THEN '목'
		WHEN DATEPART(weekday, DT) = 6 THEN '금'
		WHEN DATEPART(weekday, DT) = 7 THEN '토'
	END WEEKDAY_KOR
FROM Dim_Date
OPTION (MAXRECURSION 0);
}}}

==== Type2 ====
{{{
WITH Dim_Date
AS
(
        SELECT 1 Seq, CONVERT(datetime, '20080701') DT 
        UNION ALL
        SELECT Seq + 1, DATEADD(dd, 1, DT) FROM Dim_Date
        WHERE DATEADD(dd, 1, DT) <= GETDATE()
)
SELECT 
	CONVERT(int, CONVERT(char(8), DT, 112)) DateKey
,	CONVERT(char(8), DT, 112) DT_char8
,	CONVERT(char(10), DT, 121) DT_char10
,	DT DT_DateTime
,	DATEPART(yyyy, DT) YYYY
,	CASE WHEN DATEPART(mm,DT) < 7 THEN 'H1' ELSE 'H2' END HALF
,	DATEPART(qq, DT) QQ
,	DATEPART(mm, DT) MM
,	DATEPART(dd, DT) DD
,	DATEPART(dy, DT) DY
,	DATEPART(week, DT) WEEK
,	DATEPART(weekday, DT) WEEKDAY
,	CASE 
		WHEN DATEPART(weekday, DT) = 1 THEN '일'
		WHEN DATEPART(weekday, DT) = 2 THEN '월'
		WHEN DATEPART(weekday, DT) = 3 THEN '화'
		WHEN DATEPART(weekday, DT) = 4 THEN '수'
		WHEN DATEPART(weekday, DT) = 5 THEN '목'
		WHEN DATEPART(weekday, DT) = 6 THEN '금'
		WHEN DATEPART(weekday, DT) = 7 THEN '토'
	END WEEKDAY_KOR
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -6, DT), 112)) Begin_Week1
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 0, DT), 112)) End_Week1

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -5, DT), 112)) Begin_Week2
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 1, DT), 112)) End_Week2

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -4, DT), 112)) Begin_Week3
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 2, DT), 112)) End_Week3

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -3, DT), 112)) Begin_Week4
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 3, DT), 112)) End_Week4

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -2, DT), 112)) Begin_Week5
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 4, DT), 112)) End_Week5

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, -1, DT), 112)) Begin_Week6
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 5, DT), 112)) End_Week6

,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 0, DT), 112)) Begin_Week7
,	CONVERT(int, CONVERT(char(8), DATEADD(dd, 6, DT), 112)) End_Week7
FROM Dim_Date
OPTION (MAXRECURSION 0);
}}}

에이.. 주단위 업뎃 쳐야함..

{{{

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-01'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week1 = b.begin_dt
,   end_week1 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-02'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week2 = b.begin_dt
,   end_week2 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-03'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week3 = b.begin_dt
,   end_week3 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-04'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week4 = b.begin_dt
,   end_week4 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-05'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week5 = b.begin_dt
,   end_week5 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-06'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week6 = b.begin_dt
,   end_week6 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

;with ww
as
(
    select   
        date_key
    ,   row_number() over(order by date_key) rownum
    from (
        select
            date_key
        --,   row_number() over(order by date_key) rownum
        ,   row_number() over(order by date_key) % 7 rownum
        from [dim].[date]
        where date_key >= '2000-01-07'
    ) t
    where rownum in (1, 0)
), rs
as
(
    select 
        begin_dt
    ,   end_dt
    from (
        select
            a.date_key begin_dt
        ,   b.date_key end_dt
        ,   row_number() over(order by a.date_key) rownum
        from ww a
            inner join ww b
                on a.rownum = b.rownum - 1
    ) t
    where rownum % 2 = 1
)
update a
set
    begin_week7 = b.begin_dt
,   end_week7 = b.end_dt
from dim.date a
    inner join rs b
        on a.date_key between b.begin_dt and b.end_dt

}}}
==== 참고자료 ====
 * [attachment:lunartosolar.txt 음력/양력 매핑 테이블(출처:데브피아)]