티스토리 뷰

Database

[MSSQL]일별,주별,월별 통계

애플자라 2010. 6. 29. 23:14
반응형

-- 일단위
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

반응형

'Database' 카테고리의 다른 글

IBM DB2에서 select and update,delete 하기  (0) 2010.07.19
[MSSQL] MSSQL2000 ->2005 마이그레이션 attach  (0) 2010.07.05
MSSQL 데이터 백업  (0) 2010.06.07
Oracle DELETE_TRIGGER  (0) 2010.05.31
Oracle 짝수,홀수 구별  (0) 2010.05.26
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함