with tmp as
(
SELECT 'A' AS 작업, '2008-01-01' AS 처리예정일, '2008-01-01' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-02' AS 처리예정일, '2008-01-03' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-03' AS 처리예정일, '2008-01-03' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-04' AS 처리예정일, '2008-01-05' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-06' AS 처리예정일, '2008-01-07' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-08' AS 처리예정일, '2008-01-08' AS 처리일 UNION ALL
SELECT 'A' AS 작업, '2008-01-08' AS 처리예정일, '2008-01-09' AS 처리일 UNION ALL
SELECT 'B' AS 작업, '2008-01-03' AS 처리예정일, '2008-01-03' AS 처리일 UNION ALL
SELECT 'B' AS 작업, '2008-01-04' AS 처리예정일, '2008-01-05' AS 처리일 UNION ALL
SELECT 'B' AS 작업, '2008-01-05' AS 처리예정일, '2008-01-05' AS 처리일 UNION ALL
SELECT 'B' AS 작업, '2008-01-05' AS 처리예정일, '2008-01-06' AS 처리일
)
SELECT * FROM tmp;
위와같은 데이터가 있습니다.
'작업별 처리율'을 구하려고 합니다.
처리율은
작업별 처리일이 처리예정일보다 같거나 이전인 수 (A의 경우 4)
------------------------------------------------------- X 100
작업별 전체수 (A의 경우 7)
이렇게 구할려고 합니다.
마지막 쿼리결과는
작업 처리율
---------------
A 57.1
B 50.0
이렇게 보여주려고 합니다.
처음엔 여러번 쿼리해서 조합하려고 했는데 한번의 쿼리로 가능할것 같은데 쉽지가 않네요^^ |