아래와 같이 데이터가 출력 가능 할지 질문 드립니다.
정확히 결정이 되는 수량은 입고수량, 입고단가, 입고금액, 출고수량 입니다.
일자 | 제품코드 |
당일 기초수량 |
당일 기초단가 |
당일 기초금액 |
입고수량 |
입고단가 | 입고금액 | 출고수량 | 출고단가 | 출고금액 | 기말수량 | 기말단가 | 기말금액 |
20150903 | A | 0 | 0 | 0 | 500 | 120 | 60000 | 45 | 120 | 5400 | 455 | 120 | 54600 |
20150904 | A | 455 | 120 | 54600 | 100 | 130 | 13000 | 100 | 122 | 12180 | 455 | 122 | 55420 |
20150905 | A | 455 | 122 | 55420 | 0 | 0 | 0 | 0 | 0 | 0 | 455 | 122 | 55420 |
20150906 | A | 455 | 122 | 55420 | 300 | 140 | 42000 | 600 | 129 | 77420 | 155 | 129 | 20000 |
20150907 | A | 155 | 129 | 20000 | 0 | 0 | 0 | 0 | 0 | 0 | 155 | 129 | 20000 |
20150908 | A | 155 | 129 | 20000 | 150 | 130 | 19500 | 0 | 0 | 0 | 305 | 130 | 39500 |
20150909 | A | 305 | 130 | 39500 | 0 | 0 | 0 | 130 | 130 | 16836 | 175 | 130 | 22664 |
20150910 | A | 175 | 130 | 22664 | 0 | 0 | 0 | 0 | 0 | 0 | 175 | 130 | 22664 |
20150911 | A | 175 | 130 | 22664 | 0 | 0 | 0 | 0 | 0 | 0 | 175 | 130 | 22664 |
20150912 | A | 175 | 130 | 22664 | 0 | 0 | 0 | 0 | 0 | 0 | 175 | 130 | 22664 |
20150913 | A | 175 | 130 | 22664 | 100 | 80 | 8000 | 150 | 112 | 16726 | 125 | 112 | 13938 |
20150914 | A | 125 | 112 | 13938 | 0 | 0 | 0 | 0 | 0 | 0 | 125 | 112 | 13938 |
20150915 | A | 125 | 112 | 13938 | 0 | 0 | 0 | 0 | 0 | 0 | 125 | 112 | 13938 |
입고수량, 입고단가, 출고수량은 입출고테이블에서 SELECT 된 수량 및 금액이고 그 나머지 필드들은 아래 수식에 따라
계산되어야 합니다. (당일기초수량을 계산해야 출고단가를 계산할 수 있는데 출고단가가 기초금액, 기초수량과 관련되어 순환 참조가 일어나서 좀 난감하네요....ㅡㅡ;)
1. 당일기초수량, 단가, 금액은 전일자 기말수량, 단가, 금액임.
2. 당일출고단가 = (당일기초금액 + 당일입고금액) / (당일기초수량+당일입고수량)
3. 당일출고금액 = 당일출고단가 * 당일출고수량
4. 당일기말수량 = 당일기초수량 + 당일입고수량 - 당일출고수량
5. 당일기말금액 = 당일기초금액 + 당일입고금액 - 당일출고금액
6. 당일기말단가 = 당일기말금액 / 당일기말수량
Procedure에서 LOOP 을 수행하긴 데이터량이 너무 부담되구요....ㅜㅜ
고수님들 조언해 주시면 감사하겠습니다...
위 내용을 엑셀 파일에 정리해 봤습니다.
WITH t AS ( SELECT '20150903' dt, 'A' cd, 500 cnt_i, 120 prc_i, 45 cnt_o FROM dual UNION ALL SELECT '20150904', 'A', 100, 130, 100 FROM dual UNION ALL SELECT '20150905', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150906', 'A', 300, 140, 600 FROM dual UNION ALL SELECT '20150907', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150908', 'A', 150, 130, 0 FROM dual UNION ALL SELECT '20150909', 'A', 0, 0, 130 FROM dual UNION ALL SELECT '20150910', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150911', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150912', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150913', 'A', 100, 80, 150 FROM dual UNION ALL SELECT '20150914', 'A', 0, 0, 0 FROM dual UNION ALL SELECT '20150915', 'A', 0, 0, 0 FROM dual ) , tmp0 AS ( SELECT ROW_NUMBER() OVER(PARTITION BY cd ORDER BY dt) rn , dt, cd, cnt_i, prc_i, cnt_o , cnt_i * prc_i amt_i FROM t a ) , tmp1( rn, dt, cd , cnt_s, prc_s, amt_s , cnt_i, prc_i, amt_i , cnt_o, prc_o, amt_o , cnt_e, prc_e, amt_e ) AS ( SELECT b.rn , b.dt , b.cd , 0 cnt_s , 0 prc_s , 0 amt_s , b.cnt_i , b.prc_i , b.amt_i , b.cnt_o , ROUND((0 + b.amt_i) / (0 + b.cnt_i)) prc_o , b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i)) amt_o , 0 + b.cnt_i - b.cnt_o cnt_e , ROUND( (0 + b.amt_i - b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i))) / (0 + b.cnt_i - b.cnt_o) ) prc_e , 0 + b.amt_i - b.cnt_o * ROUND((0 + b.amt_i) / (0 + b.cnt_i)) amt_e FROM tmp0 b WHERE rn = 1 UNION ALL SELECT b.rn , b.dt , b.cd , a.cnt_e cnt_s , a.prc_e prc_s , a.amt_e amt_s , b.cnt_i , b.prc_i , b.amt_i , b.cnt_o , ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) prc_o , b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) amt_o , a.cnt_e + b.cnt_i - b.cnt_o cnt_e , ROUND( (a.amt_e + b.amt_i - b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i))) / (a.cnt_e + b.cnt_i - b.cnt_o) ) prc_e , a.amt_e + b.amt_i - b.cnt_o * ROUND((a.amt_e + b.amt_i) / (a.cnt_e + b.cnt_i)) amt_e FROM tmp1 a , tmp0 b WHERE a.cd = b.cd AND a.rn + 1 = b.rn ) SELECT * FROM tmp1 ;