달력을 보면 1일이 언제이냐에 따라... 주계산을 하는데...
4개의 주로만 구간을 나누려고 합니다.
이런 달력인 경우
1 ~ 10일
11 ~ 17
18~ 24
25 ~ 31
| 일(Sun) | 월(Mon) | 화(Tue) | 수(Wed) | 목(Thu) | 금(Fri) | 토(Sat) |
| 1 국군의 날 | 2 8.20 | 3 개천절 | ||||
| 4 8.22 | 5 8.23 | 6 8.24 | 7 8.25 | 8 8.26 | 9 한글날 | 10 8.28 |
| 11 8.29 | 12 8.30 | 13 9.1 | 14 9.2 | 15 9.3 | 16 9.4 | 17 9.5 |
| 18 9.6 | 19 9.7 | 20 9.8 | 21 9.9 | 22 9.10 | 23 9.11 | 24 9.12 |
| 25 9.13 | 26 9.14 | 27 9.15 | 28 9.16 | 29 9.17 | 30 9.18 | 31 9.19 |
1 ~ 7
8 ~ 14
15~21
22~30
| 1 9.20 | 2 9.21 | 3 9.22 | 4 9.23 | 5 9.24 | 6 9.25 | 7 9.26 |
| 8 9.27 | 9 9.28 | 10 9.29 | 11 9.30 | 12 10.1 | 13 10.2 | 14 10.3 |
| 15 10.4 | 16 10.5 | 17 10.6 | 18 10.7 | 19 10.8 | 20 10.9 | 21 10.10 |
| 22 10.11 | 23 10.12 | 24 10.13 | 25 10.14 | 26 10.15 | 27 10.16 | 28 10.17 |
| 29 10.18 | 30 10.19 |
으로 앞뒤의 일자를 첫째주나 마지막주에 넣어서 계산을 할 수 있도록 뽑으려면 어떻게 해야 할까요?
그룹을 지을때.. case 문을 사용을 해서 뽑아야 할것 같은데요..
그룹을 지어서 4개 구간에 대한 데이터의 통계를 뽑으려고 합니다.
Comment 10
-
건우아빠
2015.10.23 11:18
-
Terry
2015.10.23 12:07
실력이 부족해서 -_-;;;
쿼리로 프로그램(?) 을 짯습니다--;;
많이 복잡하실거에요..
참고만 하세요~~
-----쿼리시작----
Declare @ls_from_ymd Char(8)
Declare @ls_to_ymd Char(8)
Declare @ll_week Decimal(18)Select @ls_from_ymd = '20160101'
Select @ls_to_ymd = Convert(char(8),Dateadd(day,-1,DATEADD(month,1,@ls_from_ymd)),112)select @ll_week = ceiling((day(@ls_to_ymd)+datepart(dw,left(convert(varchar(10),@ls_to_ymd,112),6)+'01')-1)/7.0)
with wk (wk) As
(
Select 1 Union All
Select 2 Union All
Select 3 Union All
Select 4
)
,tblA (wk,s_date,e_date) As
(
Select 1
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),-1),112)
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),5),112)Union All
Select wk + 1
,Convert(char(8),DATEADD(DAY,7,s_date),112)
,Convert(char(8),DATEADD(DAY,7,e_date),112)
From tblA a
Where a.wk + 1 <= @ll_week
)
,tblB (wk,s_date,e_date) As
(
Select a.wk
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.e_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(day,-(day(@ls_from_ymd)-1),@ls_from_ymd),112)
Else a.s_date
End
) As s_date
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.s_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(month,1,@ls_from_ymd)-(day(@ls_from_ymd)),112)
Else a.e_date
End
) As e_date
From tblA a
)
,tblC (wk,s_date,e_date,s_dw,e_dw,w_day) As
(
Select a.wk
,a.s_date
,a.e_date
,DATEPART(DW,DATEADD(DAY,7,s_date))
,DATEPART(DW,DATEADD(DAY,7,e_date))
,DATEPART(DW,DATEADD(DAY,7,e_date)) - DATEPART(DW,DATEADD(DAY,7,s_date)) + 1
From tblB a
)
,tblD (wk,s_date,e_date,s_dw,e_dw) As
(
Select
a.wk
,(
Case b.wk When 4
Then c.s_date
When 5
Then
(
Case When a.wk = 1
Then c.s_date
Else d.s_date
End
)
When 6
Then
(
Case When a.wk = 1
Then c.s_date
Else d.s_date
End
)
End
) As s_date,(
Case b.wk When 4
Then c.e_date
When 5
Then d.e_date
When 6
Then
(
Case When a.wk = 4
Then e.e_date
Else d.e_date
End
)
End
) As e_date
,c.s_dw
,(
Case b.wk When 4
Then c.e_dw
When 5
Then d.e_dw
When 6
Then
(
Case When a.wk = 4
Then e.e_dw
Else d.e_dw
End
)
End
) As e_dw
From wk a
Inner Join
(
Select MAX(a.wk) As wk
From tblC a
) b
On 1 = 1
Inner Join
tblC c
On a.wk = c.wk
Inner Join
tblC d
On a.wk + 1 = d.wk
Inner Join
tblC e
On a.wk + 2 = e.wk
)
Select a.wk
,a.s_date
,a.e_date
,DATEDIFF(DAY,a.s_date,a.e_date) + 1 As tot_date
,a.s_dw
,a.e_dw
,(
Case a.s_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) s_dw_kor
,(
Case a.e_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) e_dw_kor
From tblD a----쿼리끝----
-
건우아빠
2015.10.23 14:00
2월 달이 ? 수정해주세요.. ㅎㅎ
-
Terry
2015.10.23 14:05
저도 샘플링으로 테스트해봤는데 2019년12월도 이상하네유 ㅎㅎ;;
-
Terry
2015.10.23 15:08
수정본이에요~~ㅋ
검증하는건 대충 된거 같은데..-_-완벽하진 않을수도 있을듯요..
---쿼리시작---
-----쿼리시작----
Declare @ls_from_ymd Char(8)
Declare @ls_to_ymd Char(8)
Declare @ll_week Decimal(18)Select @ls_from_ymd = '20091201'
Select @ls_to_ymd = Convert(char(8),Dateadd(day,-1,DATEADD(month,1,@ls_from_ymd)),112)select @ll_week = ceiling((day(@ls_to_ymd)+datepart(dw,left(convert(varchar(10),@ls_to_ymd,112),6)+'01')-1)/7.0)
with wk (wk) As
(
Select 1 Union All
Select 2 Union All
Select 3 Union All
Select 4
)
,tblA (wk,s_date,e_date) As
(
Select 1
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),-1),112)
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),5),112)Union All
Select wk + 1
,Convert(char(8),DATEADD(DAY,7,s_date),112)
,Convert(char(8),DATEADD(DAY,7,e_date),112)
From tblA a
Where a.wk + 1 <= @ll_week
)
,tblB (wk,s_date,e_date) As
(
Select a.wk
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.e_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(day,-(day(@ls_from_ymd)-1),@ls_from_ymd),112)
Else a.s_date
End
) As s_date
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.s_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(month,1,@ls_from_ymd)-(day(@ls_from_ymd)),112)
Else a.e_date
End
) As e_date
From tblA a
)
,tblC (wk,s_date,e_date,s_dw,e_dw,w_day) As
(
Select a.wk
,a.s_date
,a.e_date
,DATEPART(DW,DATEADD(DAY,7,s_date))
,DATEPART(DW,DATEADD(DAY,7,e_date))
,DATEPART(DW,DATEADD(DAY,7,e_date)) - DATEPART(DW,DATEADD(DAY,7,s_date)) + 1
From tblB a
)
,tblD (wk,s_date,e_date,s_dw,e_dw) As
(
Select
a.wk
,(
Case b.wk When 4
Then c.s_date
When 5
Then
(
Case When a.wk = 1
Then c.s_date
Else
(
Case When x.s_dw = 1
Then c.s_date
Else d.s_date
End
)
End
)
When 6
Then
(
Case When a.wk = 1
Then c.s_date
Else d.s_date
End
)
End
) As s_date
,(
Case b.wk When 4
Then c.e_date
When 5
Then
(
Case When a.wk = 1
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else (
Case When a.wk IN (2,3)
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else d.e_date
End
)
End
)
When 6
Then
(
Case When a.wk = 1
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else (
Case When a.wk IN (2,3)
Then d.e_date
Else e.e_date
End
)
End
)
End
) As e_date
,c.s_dw
,(
Case b.wk When 4
Then c.e_dw
When 5
Then d.e_dw
When 6
Then
(
Case When a.wk = 4
Then e.e_dw
Else d.e_dw
End
)
End
) As e_dw
From wk a
Inner Join
(
Select MAX(a.wk) As wk
From tblC a
) b
On 1 = 1
Inner Join
tblC c
On a.wk = c.wk
Left Outer Join
tblC d
On a.wk + 1 = d.wk
Left Outer Join
tblC e
On a.wk + 2 = e.wk
Left Outer Join
tblC x
On x.wk = 1
Left Outer Join
tblC y
On y.wk = (
Select Max(k.wk)
From tblC k
)
)
Select a.wk
,a.s_date
,a.e_date
,DATEDIFF(DAY,a.s_date,a.e_date) + 1 As tot_date
,a.s_dw
,a.e_dw
,(
Case a.s_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) s_dw_kor
,(
Case a.e_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) e_dw_kor
From tblD a----쿼리끝----
-
건우아빠
2015.10.23 15:38
잘되네요.. 수고 하셨습니다.
-
Terry
2015.10.23 15:57
아마도 다른 테이블과 Join 해서 처리될듯하여
편하게 사용할수 있게끔
일자별로 row를 분리해보았습니다.
참고만 하세요..
---쿼리시작---
-----쿼리시작----
Declare @ls_from_ymd Char(8)
Declare @ls_to_ymd Char(8)
Declare @ll_week Decimal(18)Select @ls_from_ymd = '20151201'
Select @ls_to_ymd = Convert(char(8),Dateadd(day,-1,DATEADD(month,1,@ls_from_ymd)),112)select @ll_week = ceiling((day(@ls_to_ymd)+datepart(dw,left(convert(varchar(10),@ls_to_ymd,112),6)+'01')-1)/7.0)
with wk (wk) As
(
Select 1 Union All
Select 2 Union All
Select 3 Union All
Select 4
)
,tblA (wk,s_date,e_date) As
(
Select 1
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),-1),112)
,Convert(char(8),DATEADD(WEEK,DATEDIFF(WEEK,0,@ls_from_ymd),5),112)Union All
Select wk + 1
,Convert(char(8),DATEADD(DAY,7,s_date),112)
,Convert(char(8),DATEADD(DAY,7,e_date),112)
From tblA a
Where a.wk + 1 <= @ll_week
)
,tblB (wk,s_date,e_date) As
(
Select a.wk
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.e_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(day,-(day(@ls_from_ymd)-1),@ls_from_ymd),112)
Else a.s_date
End
) As s_date
,(
Case When LEFT(a.s_date,6) < LEFT(a.e_date,6) And LEFT(a.s_date,6) = LEFT(@ls_from_ymd,6)
Then Convert(char(8),dateadd(month,1,@ls_from_ymd)-(day(@ls_from_ymd)),112)
Else a.e_date
End
) As e_date
From tblA a
)
,tblC (wk,s_date,e_date,s_dw,e_dw,w_day) As
(
Select a.wk
,a.s_date
,a.e_date
,DATEPART(DW,DATEADD(DAY,7,s_date))
,DATEPART(DW,DATEADD(DAY,7,e_date))
,DATEPART(DW,DATEADD(DAY,7,e_date)) - DATEPART(DW,DATEADD(DAY,7,s_date)) + 1
From tblB a
)
,tblD (wk,s_date,e_date,s_dw,e_dw) As
(
Select
a.wk
,(
Case b.wk When 4
Then c.s_date
When 5
Then
(
Case When a.wk = 1
Then c.s_date
Else
(
Case When x.s_dw = 1
Then c.s_date
Else d.s_date
End
)
End
)
When 6
Then
(
Case When a.wk = 1
Then c.s_date
Else d.s_date
End
)
End
) As s_date
,(
Case b.wk When 4
Then c.e_date
When 5
Then
(
Case When a.wk = 1
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else (
Case When a.wk IN (2,3)
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else d.e_date
End
)
End
)
When 6
Then
(
Case When a.wk = 1
Then
(
Case When x.s_dw = 1
Then c.e_date
Else d.e_date
End
)
Else (
Case When a.wk IN (2,3)
Then d.e_date
Else e.e_date
End
)
End
)
End
) As e_date
,c.s_dw
,(
Case b.wk When 4
Then c.e_dw
When 5
Then d.e_dw
When 6
Then
(
Case When a.wk = 4
Then e.e_dw
Else d.e_dw
End
)
End
) As e_dw
From wk a
Inner Join
(
Select MAX(a.wk) As wk
From tblC a
) b
On 1 = 1
Inner Join
tblC c
On a.wk = c.wk
Left Outer Join
tblC d
On a.wk + 1 = d.wk
Left Outer Join
tblC e
On a.wk + 2 = e.wk
Left Outer Join
tblC x
On x.wk = 1
Left Outer Join
tblC y
On y.wk = (
Select Max(k.wk)
From tblC k
)
)
,tblE (wk,s_date,e_date,tot_date,s_dw,e_dw,s_dw_kor,e_dw_kor) As
(
Select a.wk
,a.s_date
,a.e_date
,DATEDIFF(DAY,a.s_date,a.e_date) + 1 As tot_date
,a.s_dw
,a.e_dw
,(
Case a.s_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) s_dw_kor
,(
Case a.e_dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) e_dw_kor
From tblD a
)--Select * from tblE
,tblF (wk,ymd,s_date,e_date,dw) As
(
Select a.wk
,a.s_date
,a.s_date
,a.e_date
,DATEPART(DW,DATEADD(DAY,7,a.s_date))
From tblE a
Union All
Select a.wk
,Convert(Char(8),DATEADD(day,1,a.ymd),112)
,a.s_date
,a.e_date
,DATEPART(DW,DATEADD(DAY,7,Convert(Char(8),DATEADD(day,1,a.ymd),112)))
From tblF a
Where Convert(Char(8),DATEADD(day,1,a.ymd),112) <= a.e_date
)
Select a.wk
,a.ymd
,a.dw
,(
Case a.dw When 1 Then '일'
When 2 Then '월'
When 3 Then '화'
When 4 Then '수'
When 5 Then '목'
When 6 Then '금'
When 7 Then '토'
End
) s_dw_kor
From tblF a
Order By a.wk Asc
,a.ymd Asc----쿼리끝----
---결과값---
1 20151201 3 화
1 20151202 4 수
1 20151203 5 목
1 20151204 6 금
1 20151205 7 토
1 20151206 1 일
1 20151207 2 월
1 20151208 3 화
1 20151209 4 수
1 20151210 5 목
1 20151211 6 금
1 20151212 7 토
2 20151213 1 일
2 20151214 2 월
2 20151215 3 화
2 20151216 4 수
2 20151217 5 목
2 20151218 6 금
2 20151219 7 토
3 20151220 1 일
3 20151221 2 월
3 20151222 3 화
3 20151223 4 수
3 20151224 5 목
3 20151225 6 금
3 20151226 7 토
4 20151227 1 일
4 20151228 2 월
4 20151229 3 화
4 20151230 4 수
4 20151231 5 목 -
건우아빠
2015.10.23 16:12
테리님걸 로직 참고로 수정했습니다..
저희도 업무별 일정관리가 있는데 규ㅣ찮아서 무조간 1~부터 7일간을 한주로 처리 했는데...
이걸 적용해 봐여 겠네요
declare @month varchar(6) ,@pvt integerset @month = '201502' ;
set @pvt = 1 ; -- 1-일 2-월 3-화....... 달 1일으,ㅣ 요일이 지정요일 이전이면 첫번째주로 이후이면 다음주에 표함with res
as (select @month+ right( '0'+ convert(varchar,number) ,2 ) 일
, DATEPART(wk,@month+ right( '0'+ convert(varchar,number) ,2 ) ) - DATEPART(wk,@month+ '01' ) + 1 주
, case when DATEPART(dw,@month + '01' ) <= @pvt then 4 else 5 end pvt_mm
from master..spt_values
where type ='P' and number between 1 and 31
and ISDATE(@month+ right( '0'+ convert(varchar,number) ,2 )) = 1 ) ,
result as (
select 일
, pvt_mm
, case pvt_mm
when 4 then
case when 주 > 4 then 4 else 주 end
when 5 then
case 주
when 1 then 1
when 2 then 1
when 3 then 2
when 4 then 3
else 4 end
end 주
from res )
select 주 , min(일) s_date , max(일) e_date
from result
group by 주
-
Terry
2015.10.23 16:44
엄지척입니다~~!! b-_-)b
건우아빠님의 쿼리를 보면..항상 경이로운 느낌이 드네요~~
저리 길게 늘여뜨렸던 쿼리가
이리 단순해질수 있다니 놀랍습니다.
제 미천한 실력을 깨닫고 갑니다.
더욱더 열심히 실력을 갈고 닦아야겠어요^^
불타는 금요일이네요~~
즐거운 주말 보내세요^-^
감사합니다!!
-
건우아빠
2015.10.23 16:53
Terry 님이 이미 분석이 되어 있어서...
그것 보고 다듬었을뿐입니다.....
select number 일
, case when ceiling(number / 7.) > 4 then 4 else ceiling(number /7.) end 주
from master..spt_values where type ='P' and number between 1 and 31