본문 바로가기
데이터베이스/MSSQL

일별,주별,월별 통계

by o테리o 2013. 4. 24.

-- 일단위

Select DATEPART(dd, order_dt), count(order_no) From 주문테이블

group by DATEPART(dd, order_dt)

order by DATEPART(dd, order_dt)


 


-- 주 단위

Select DATEPART(ww, order_dt), count(order_no) From 주문테이블

group by DATEPART(ww, order_dt)

order by DATEPART(ww, order_dt)


 


-- 월단위

Select DATEPART(mm, order_dt), count(order_no) From 주문테이블

group by DATEPART(mm, order_dt)

order by DATEPART(mm, order_dt)


 


-- 년단위

Select DATEPART(yy, order_dt), count(order_no) From 주문테이블

group by DATEPART(yy, order_dt)

order by DATEPART(yy, order_dt) 




--분기별

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

FROM ( 

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

,(CASE DATEPART(mm, regdate) 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 SMEM01MEM

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

--order by DATEPART(ww, regdate) 

) A

group by A.memyear,A.AA

order by A.memyear,A.AA


출처: http://applejara.tistory.com/entry/MSSQL%EC%9D%BC%EB%B3%84%EC%A3%BC%EB%B3%84%EC%9B%94%EB%B3%84-%ED%86%B5%EA%B3%84