관리 메뉴

심재운 블로그

주단위, 월단위, 년단위, 분기별 단위 sql 통계 구현하기 본문

데이터베이스/MS-SQL

주단위, 월단위, 년단위, 분기별 단위 sql 통계 구현하기

재우니 2012. 7. 6. 16:59




-- 주 단위

Select DATEPART(ww, dates), count(order_no) From 테이블

group by DATEPART(ww, dates)

order by DATEPART(ww, dates)


 


-- 월단위

Select DATEPART(mm, dates), count(order_no) From tables

group by DATEPART(mm, dates)

order by DATEPART(mm, dates)


 


-- 년단위

Select DATEPART(yy, dates), count(order_no) From tables

group by DATEPART(yy, dates)

order by DATEPART(yy, dates) 





--분기별

SELECT A.memyear,A.AA,SUM(A.CNT) AS CNT

FROM ( 

Select DATEPART(yy, dates) as memyear,DATEPART(mm, dates) as memmonth,  count(dates) as cnt

,(CASE DATEPART(mm, dates) WHEN '1' THEN '1' WHEN '2' THEN '1' WHEN '3' THEN '1' 

WHEN '4' THEN '2' WHEN '5' THEN '2' WHEN '6' THEN '2' 

WHEN '7' THEN '3' WHEN '8' THEN '3' WHEN '9' THEN '3' 

WHEN '10' THEN '4'

WHEN '11' THEN '4'

WHEN '12' THEN '4' ELSE '0' END) AS AA

From Tables

group by DATEPART(mm, dates),DATEPART(yy, dates)

--order by DATEPART(ww, dates) 

) A

group by A.memyear,A.AA

order by A.memyear,A.AA


Tag
,
2 Comments
댓글쓰기 폼