데이터베이스 질문과 답변
근속개월 수로 년월 로 나누기 문의 드립니다.
0
1
672
by lgxj20 [2018.07.17 11:18:49]
SELECT TRUNC(MM /12,0) AS YEARS,
TRUNC(MOD(MM/12, 1)*12,0) AS MONS,
TRUNC(TOT_MM /12,0) AS TOT_YEARS,
TRUNC(MOD(TOT_MM/12, 1)*12,0) AS TOT_MONS
FROM(
SELECT '1' AS MM , '31' AS TOT_MM FROM DUAL
UNION ALL
SELECT '6' AS MM , '31' AS TOT_MM FROM DUAL
UNION ALL
SELECT '24' AS MM , '31' AS TOT_MM FROM DUAL
);
여기서 문제는 1개월 일하게 계산이 잘 안되는데 어떤식으로 해야 될까요??
by 마농
[2018.07.17 11:38:55]
WITH t AS
(
SELECT '1' mm, '31' tot_mm FROM dual
UNION ALL SELECT '6', '31' FROM dual
UNION ALL SELECT '24', '31' FROM dual
)
SELECT FLOOR( mm / 12) years
, MOD ( mm , 12) mons
, FLOOR(tot_mm / 12) tot_years
, MOD (tot_mm , 12) tot_mons
FROM t
;
) 버튼을 클릭하여 작성 하시면 됩니다.