안녕하십니까?
다름이 아니오라, 시간대별 합계 구하는 쿼리를 여기서 찾아서 작성하였습니다.
쿼리는 이상이 없으나, 오늘 날짜 기준이
아침 06:00 부터 다음날 05:59까지 가 오늘로 보고 있습니다.
아래 쿼리는 00 시부터 24시 까지 시간대별로 나오는 입니다.
기준을 06:00 ~ 다음날 05:59으로 변경해서 시간대별로 나오게 할수 있는지요?
- 아래 -
WITH A AS
(
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 23 AS QTY, '20121029000000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029010000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 240 AS QTY, '20121029020000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 12 AS QTY, '20121029030000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 33 AS QTY, '20121029040000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 235 AS QTY, '20121029050000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 560 AS QTY, '20121029050000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 780 AS QTY, '20121029060000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 980 AS QTY, '20121029070000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 100 AS QTY, '20121029070000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029070000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029080000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 220 AS QTY, '20121029090000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 222 AS QTY, '20121029090000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029090000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029100000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029110000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 110 AS QTY, '20121029120000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 43 AS QTY, '20121029130000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 567 AS QTY, '20121029130000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'FA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 789 AS QTY, '20121029140000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'FA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 675 AS QTY, '20121029140000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 21 AS QTY, '20121029150000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 342 AS QTY, '20121029150000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 909 AS QTY, '20121029160000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 565 AS QTY, '20121029170000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029170000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029180000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029190000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'BA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 342 AS QTY, '20121029200000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 180 AS QTY, '20121029210000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 332 AS QTY, '20121029220000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 21 AS QTY, '20121029220000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 232 AS QTY, '20121029230000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 225 AS QTY, '20121029240000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 980 AS QTY, '20121029240000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 76 AS QTY, '20121029240000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 123 AS QTY, '20121029240000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DD' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 654 AS QTY, '20121030000000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DD' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 123 AS QTY, '20121030010000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'DD' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 675 AS QTY, '20121030010000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 563 AS QTY, '20121030020000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 123 AS QTY, '20121030020000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 667 AS QTY, '20121030030000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'AA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 123 AS QTY, '20121030030000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 345 AS QTY, '20121030040000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 211 AS QTY, '20121030050000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 123 AS QTY, '20121030060000' AS STARTTIME FROM DUAL UNION ALL
SELECT 'CA' AS MODEL_NUMBER ,'20121024' AS OUT_DATE, 222 AS QTY, '20121030060000' AS STARTTIME FROM DUAL
)
SELECT HH24
,MODEL_NUMBER
,cnt
,QTY
,STARTTIME
FROM (
SELECT b.hh24
, A.MODEL_NUMBER
, COUNT(a.STARTTIME) cnt
, SUM(A.QTY) QTY
, A.STARTTIME
FROM a
, (SELECT LPAD(ROWNUM-1,2,'0') hh24 FROM a WHERE ROWNUM <= 24) b
WHERE a.STARTTIME LIKE TO_CHAR(SYSDATE -6/24 ,'yyyymmdd') || b.hh24 || '%'
GROUP BY b.hh24, A.MODEL_NUMBER, A.STARTTIME
)
WHERE 1=1
-- AND HH24 IN ('06','07')
ORDER BY HH24
조건에 'SYSDATE -6/24' 를 줘도 변함이 없고 LPAD(ROWNUM-1,2,'0')을 LPAD(ROWNUM-5,2,'0') 변경을 해도
원하는 기준(오늘)으로 나오질 안네요....
검토 부탁드립니다.
수고하세요 |