데이터베이스 질문과 답변
안녕하세요.
현재 일부터 7일 이전 데이터를 가져 오려고 합니다.
일별 3교대 시간이며 (시작 06:00:00 ~ 다음날 05:59:59 까지 데이터입니다.)
무식하게 union all 을써서 했는데 먼가 좀 간단하게 쿼리를 짰으면 하는데 도움 부탁드리겠습니다.
감사합니다.
SELECT TO_CHAR(SYSDATE - 1, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 1, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 2, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 2, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 1, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 3, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 2, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 4, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 4, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 3, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 5, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 5, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 4, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 6, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 6, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 5, 'YYYYMMDD') || '055959'
UNION ALL
SELECT TO_CHAR(SYSDATE - 7, 'YYYYMMDD') AS WORK_DATE,
SUM(Q_QTY) AS PROD_QTY
FROM TBL_MCC_CARD
WHERE D_PRINT >= TO_CHAR(SYSDATE - 7, 'YYYYMMDD') || '060000'
AND D_PRINT <= TO_CHAR(SYSDATE - 6, 'YYYYMMDD') || '055959'
by 마농
[2018.04.09 13:06:55]
SELECT TO_CHAR(TO_DATE(d_print, 'yyyymmddhh24miss') - 6/24, 'yyyymmdd') AS work_date
, SUM(q_qty) AS prod_qty
FROM tbl_mcc_card
WHERE d_print >= TO_CHAR(sysdate - 7, 'yyyymmdd') || '060000'
AND d_print <= TO_CHAR(sysdate , 'yyyymmdd') || '055959'
GROUP BY TO_CHAR(TO_DATE(d_print, 'yyyymmddhh24miss') - 6/24, 'yyyymmdd')
ORDER BY work_date
;
by 우리집아찌
[2018.04.09 14:00:51]
데이터가 없어도 7개 로우가 나와야하지 않나요?
) 버튼을 클릭하여 작성 하시면 됩니다.