안녕하십니까?

다름이 아니오라, 시간대별 합계 구하는 쿼리를 여기서 찾아서 작성하였습니다.

쿼리는 이상이 없으나, 오늘 날짜 기준이

아침 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') 변경을 해도

원하는 기준(오늘)으로 나오질 안네요....

검토 부탁드립니다.

수고하세요