데이터베이스 질문과 답변
안녕하세요. sql 하나 질문드리겠습니다, 고수님들 답변부탁드립니다.
실투입일 과 실철수일을 가지고 sql 활용하여 소계와 월별 투입%을 표현 할 수 있을까요??
| 실투입일 | 실철수일 | 소계 | 2014년 | 2015년 | ||||||||||||||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
| 2014-10-29 | 2015-04-15 | 5.6 | 0.1 | 1 | 1 | 1 | 1 | 1 | 0.5 | |||||||||||||||||
| 2014-11-17 | 2015-04-15 | 5 | 0.5 | 1 | 1 | 1 | 1 | 0.5 | ||||||||||||||||||
| 2014-11-17 | 2015-01-16 | 2 | 0.5 | 1 | 0.5 | |||||||||||||||||||||
| 2014-11-12 | 2015-01-15 | 2.2 | 0.7 | 1 | 0.5 | |||||||||||||||||||||
| 2014-11-17 | 2015-04-15 | 5 | 0.5 | 1 | 1 | 1 | 1 | 0.5 | ||||||||||||||||||
| 2014-10-29 | 2015-04-15 | 5.6 | 0.1 | 1 | 1 | 1 | 1 | 1 | 0.5 | |||||||||||||||||
| 2014-11-17 | 2015-04-15 | 5 | 1 | 1 | 1 | 1 | 1 | 0.5 | ||||||||||||||||||
| 2014-10-29 | 2015-04-15 | 5.6 | 0.1 | 1 | 1 | 1 | 1 | 1 | 0.5 | |||||||||||||||||
| 2014-11-17 | 2015-04-15 | 5 | 0.5 | 1 | 1 | 1 | 1 | 0.5 | ||||||||||||||||||
| 2014-11-17 | 2015-01-15 | 2 | 0.5 | 1 | 0.5 | |||||||||||||||||||||
| 2014-11-01 | 2015-04-15 | 5.5 | 1 | 1 | 1 | 1 | 1 | 0.5 | ||||||||||||||||||
| 2014-10-29 | 2015-04-15 | 5.6 | 0.1 | 1 | 1 | 1 | 1 | 1 | 0.5 | |||||||||||||||||
| 54.1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.4 | 9.2 | 12 | 10.5 | 9 | 9 | 4.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
by 마농
[2014.11.17 17:47:18]
WITH t AS
(
SELECT 1 pk, '2014-10-29' sdt, '2015-04-15' edt FROM dual
UNION ALL SELECT 2, '2014-11-17', '2015-04-15' FROM dual
UNION ALL SELECT 3, '2014-11-17', '2015-01-16' FROM dual
UNION ALL SELECT 4, '2014-11-12', '2015-01-15' FROM dual
UNION ALL SELECT 5, '2014-11-17', '2015-04-15' FROM dual
UNION ALL SELECT 6, '2014-10-29', '2015-04-15' FROM dual
UNION ALL SELECT 7, '2014-11-17', '2015-04-15' FROM dual
UNION ALL SELECT 8, '2014-10-29', '2015-04-15' FROM dual
UNION ALL SELECT 9, '2014-11-17', '2015-04-15' FROM dual
UNION ALL SELECT 10, '2014-11-17', '2015-01-15' FROM dual
UNION ALL SELECT 11, '2014-11-01', '2015-04-15' FROM dual
UNION ALL SELECT 12, '2014-10-29', '2015-04-15' FROM dual
)
SELECT pk, sdt, edt
, SUM(v) v
, SUM(DECODE(ym, '201401', v)) "201401"
, SUM(DECODE(ym, '201402', v)) "201402"
, SUM(DECODE(ym, '201403', v)) "201403"
, SUM(DECODE(ym, '201404', v)) "201404"
, SUM(DECODE(ym, '201405', v)) "201405"
, SUM(DECODE(ym, '201406', v)) "201406"
, SUM(DECODE(ym, '201407', v)) "201407"
, SUM(DECODE(ym, '201408', v)) "201408"
, SUM(DECODE(ym, '201409', v)) "201409"
, SUM(DECODE(ym, '201410', v)) "201410"
, SUM(DECODE(ym, '201411', v)) "201411"
, SUM(DECODE(ym, '201412', v)) "201412"
, SUM(DECODE(ym, '201501', v)) "201501"
, SUM(DECODE(ym, '201502', v)) "201502"
, SUM(DECODE(ym, '201503', v)) "201503"
, SUM(DECODE(ym, '201504', v)) "201504"
, SUM(DECODE(ym, '201505', v)) "201505"
, SUM(DECODE(ym, '201506', v)) "201506"
, SUM(DECODE(ym, '201507', v)) "201507"
, SUM(DECODE(ym, '201508', v)) "201508"
, SUM(DECODE(ym, '201509', v)) "201509"
, SUM(DECODE(ym, '201510', v)) "201510"
, SUM(DECODE(ym, '201511', v)) "201511"
, SUM(DECODE(ym, '201512', v)) "201512"
FROM (SELECT a.pk
, TO_CHAR(a.sdt, 'yyyy-mm-dd') sdt
, TO_CHAR(a.edt, 'yyyy-mm-dd') edt
, TO_CHAR(b.sdt, 'yyyymm') ym
, ROUND( (LEAST(a.edt, b.edt) - GREATEST(a.sdt, b.sdt) + 1)
/ TO_CHAR(b.edt, 'dd')
, 1) v
FROM (SELECT pk
, TO_DATE(sdt, 'yyyy-mm-dd') sdt
, TO_DATE(edt, 'yyyy-mm-dd') edt
FROM t
) a
, (SELECT ADD_MONTHS(TO_DATE('201401', 'yyyymm'), LEVEL-1) sdt
, ADD_MONTHS(TO_DATE('201401', 'yyyymm'), LEVEL)-1 edt
FROM dual
CONNECT BY LEVEL <= 24
) b
WHERE a.sdt <= b.edt
AND a.edt >= b.sdt
)
GROUP BY ROLLUP((pk, sdt, edt))
ORDER BY pk
;
by 하치
[2014.11.18 09:24:28]
마뇽님 감사합니다 ㅠㅠ. 마뇽님의 쿼리를 보고 또 감격을하네요..
) 버튼을 클릭하여 작성 하시면 됩니다.