;with tblA(ymd,amt) As ( Select '20160201 10:00:00.000',100000 Union All Select '20160206 10:00:00.000',100000 Union All Select '20160207 10:00:00.000',100000 Union All Select '20160201 11:00:00.000',300000 Union All Select '20160201 12:00:00.000',250000 Union All Select '20160201 15:00:00.000',250000 Union All Select '20160206 15:00:00.000',500000 Union All Select '20160206 19:00:00.000',200000 ) ,tblB(ymd,amt,dw,hour) As ( Select a.* ,DATEPART(DW,a.ymd) -1 ,DATEPART(hour,a.ymd) From tblA a ) ,tblC(hour,mon_to_fri,sat,sun) As ( Select a.hour ,SUM(Case When a.dw Between 1 And 5 Then a.amt Else 0 End ) ,SUM(Case When a.dw Between 1 And 6 Then a.amt Else 0 End ) ,SUM(Case When a.dw Between 0 And 0 Then a.amt Else 0 End ) From tblB a Group By a.hour ) ,time_tbl(hour) As ( Select '00' Union All Select '01' Union All Select '02' Union All Select '03' Union All Select '04' Union All Select '05' Union All Select '06' Union All Select '07' Union All Select '08' Union All Select '09' Union All Select '10' Union All Select '11' Union All Select '12' Union All Select '13' Union All Select '14' Union All Select '15' Union All Select '16' Union All Select '17' Union All Select '18' Union All Select '19' Union All Select '20' Union All Select '21' Union All Select '22' Union All Select '23' ) Select x.hour ,b.tot ,a.mon_to_fri ,( Case When IsNull(c.mon_to_fri,0) = 0 Or IsNull(a.mon_to_fri,0) = 0 Then 0 Else Convert(Decimal(18,1),a.mon_to_fri)/ c.mon_to_fri * 100 End ) ,a.sat ,( Case When IsNull(c.sat,0) = 0 Or IsNull(a.sat,0) = 0 Then 0 Else Convert(Decimal(18,1),a.sat) / c.sat * 100 End ) ,a.sun ,( Case When IsNull(c.sun,0) = 0 Or IsNull(a.sun,0) = 0 Then 0 Else Convert(Decimal(18,1),a.sun) / c.sun * 100 End ) from time_tbl x Left Outer Join tblC a On x.hour = a.hour Left Outer Join ( Select a.mon_to_fri + a.sat + a.sun As tot ,a.hour From tblC a ) b On a.hour = b.hour Left Outer Join ( Select Sum(a.mon_to_fri) As mon_to_fri ,Sum(a.sat) As sat ,Sum(a.sun) As sun From tblC a ) c On 1 = 1
Union All
Select '합계' , Sum(a.mon_to_fri) + Sum(a.sat) + SUM(a.sun) ,Sum(a.mon_to_fri) As mon_to_fri ,100 ,Sum(a.sat) As sat ,100 ,Sum(a.sun) As sun ,100 From tblC a
하기 쿼리 참고하세요.
---쿼리시작---
;with tblA(ymd,amt) As
(
Select '20160201 10:00:00.000',100000 Union All
Select '20160206 10:00:00.000',100000 Union All
Select '20160207 10:00:00.000',100000 Union All
Select '20160201 11:00:00.000',300000 Union All
Select '20160201 12:00:00.000',250000 Union All
Select '20160201 15:00:00.000',250000 Union All
Select '20160206 15:00:00.000',500000 Union All
Select '20160206 19:00:00.000',200000
)
,tblB(ymd,amt,dw,hour) As
(
Select a.*
,DATEPART(DW,a.ymd) -1
,DATEPART(hour,a.ymd)
From tblA a
)
,tblC(hour,mon_to_fri,sat,sun) As
(
Select a.hour
,SUM(Case When a.dw Between 1 And 5 Then a.amt Else 0 End )
,SUM(Case When a.dw Between 1 And 6 Then a.amt Else 0 End )
,SUM(Case When a.dw Between 0 And 0 Then a.amt Else 0 End )
From tblB a
Group By a.hour
)
,time_tbl(hour) As
(
Select '00' Union All
Select '01' Union All
Select '02' Union All
Select '03' Union All
Select '04' Union All
Select '05' Union All
Select '06' Union All
Select '07' Union All
Select '08' Union All
Select '09' Union All
Select '10' Union All
Select '11' Union All
Select '12' Union All
Select '13' Union All
Select '14' Union All
Select '15' Union All
Select '16' Union All
Select '17' Union All
Select '18' Union All
Select '19' Union All
Select '20' Union All
Select '21' Union All
Select '22' Union All
Select '23'
)
Select x.hour
,b.tot
,a.mon_to_fri
,(
Case When IsNull(c.mon_to_fri,0) = 0 Or IsNull(a.mon_to_fri,0) = 0 Then 0
Else Convert(Decimal(18,1),a.mon_to_fri)/ c.mon_to_fri * 100
End
)
,a.sat
,(
Case When IsNull(c.sat,0) = 0 Or IsNull(a.sat,0) = 0 Then 0
Else Convert(Decimal(18,1),a.sat) / c.sat * 100
End
)
,a.sun
,(
Case When IsNull(c.sun,0) = 0 Or IsNull(a.sun,0) = 0 Then 0
Else Convert(Decimal(18,1),a.sun) / c.sun * 100
End
)
from time_tbl x
Left Outer Join tblC a On x.hour = a.hour
Left Outer Join (
Select
a.mon_to_fri
+ a.sat
+ a.sun As tot
,a.hour
From tblC a
)
b On a.hour = b.hour
Left Outer Join (
Select
Sum(a.mon_to_fri) As mon_to_fri
,Sum(a.sat) As sat
,Sum(a.sun) As sun
From tblC a
)
c On 1 = 1
Union All
Select
'합계'
, Sum(a.mon_to_fri)
+ Sum(a.sat)
+ SUM(a.sun)
,Sum(a.mon_to_fri) As mon_to_fri
,100
,Sum(a.sat) As sat
,100
,Sum(a.sun) As sun
,100
From tblC a
---쿼리끝---