프로그래밍/DB

MS-SQL 시간별 통계 쿼리

Super User 2010. 4. 21. 18:18

1.......................
SELECT  SUBSTRING(IN_DT,1,10) Hh,COUNT(*) TOT_IN_CNT
FROM E_PROG_RPT_IF
WHERE  EQUIP_ID LIKE 'OP040%'
AND IN_DT between '20100315060000' and  '20100316060000'
GROUP BY SUBSTRING(IN_DT,1,10)

2......................
SELECT SUBSTRING(IN_DT,1,10) Hh,COUNT(*) TOT_OUT_CNT
FROM E_PROG_RPT_IF
WHERE  EQUIP_ID LIKE 'OP220%'
AND IN_DT between '20100315060000' and  '20100316060000'
GROUP BY SUBSTRING(IN_DT,1,10)


1+2 =...........................................
SELECT *
FROM
( SELECT  SUBSTRING(IN_DT,1,10) Hh, COUNT(*) TOT_IN_CNT
 FROM E_PROG_RPT_IF
 WHERE  EQUIP_ID LIKE 'OP040%'
 AND IN_DT between '20100315060000' and  '20100316060000'
 GROUP BY SUBSTRING(IN_DT,1,10)
) A
FULL OUTER JOIN
( SELECT SUBSTRING(IN_DT,1,10) Hh, COUNT(*) TOT_OUT_CNT
 FROM E_PROG_RPT_IF
 WHERE  EQUIP_ID LIKE 'OP220%'
 AND IN_DT between '20100315060000' and  '20100316060000'
 GROUP BY SUBSTRING(IN_DT,1,10)
) B on A.Hh = B.Hh
order by A.Hh

저작자표시 (새창열림)