데이터베이스 질문과 답변
고객 주문 시, 상품 입고 시점에 따라 매입가를 자동 추출하고 싶습니다(완전 초보입니다)
0
2
474
by 김평산 [SQL Query] 매입 주문 이력 초보 [2020.06.01 14:32:18]
월말 정산과정에서 고객 주문 시의 상품 매입가를 주문 시의 매입가격으로 자동으로 추출하여 기록하고 싶습니다.
발주(입고) 테이블
| id | 품목 | 단가 | 입고일시 |
| 1 | 사과 | 1200 | 2020.04.02 10:00 |
| 2 | 배 | 2000 | 2020.04.05 12:00 |
| 3 | 사과 | 1300 | 2020.04.12 14:00 |
| 4 | 딸기 | 1100 | 2020.04.10 11:00 |
사후 기록하는 거라 입고시점에 따라 시간순으로 정렬이 아닌 상태일 수 있습니다.
고객주문 테이블
| id | 품목 | 판매가 | 주문일시 | 적용매입가(자동추출) | 정산 |
| 1 | 사과 | 2000 | 2000.04.03 13:00 | 1200 | 800 |
| 2 | 배 | 2000 | 2000.04.07 11:00 | 2000 | 0 |
| 3 | 사과 | 2300 | 2020.04.12 09:00 | 1200 | 1100 |
| 4 | 딸기 | 2000 | 2000.04.13 10:00 | 1100 | 900 |
| 5 | 사과 | 2200 | 2020.04.14 10:00 | 1300 | 900 |
3번 고객주문건에 대해 사과 주문시점이 2020.04.12 09:00 이므로 발주(입고) 테이블에서 사과의 매입단가는 4/2일 10시 ~ 4/12일 14시 사이에 위치하니 1200원을 적용하여 고객주문 테이블에 입력하고 주문건에 대한 정산액은 1100원 적용되는 구조를 만들고 싶습니다.
여기서 제가 도움을 얻고 싶은 부분은 1200원을 추출해내는 부분입니다. 쿼리문 작성을 어찌 해야 하는지요...
이제 데이터베이스에 대해 기초 개념만 알게 되어 공부중인데, 마음이 급하다 보니, 먼저 여쭙습니다.
검색을 뭘로 해야 할 지도 잘 모르겠어요... 도움 부탁드립니다. 감사합니다.
by 마농
[2020.06.01 16:07:13]
SELECT a.id
, a.품목
, a.판매가
, a.주문일시
, b.단가 적용매입가
, a.판매가 - b.단가 정산
FROM 주문 a
INNER JOIN 입고 b
ON b.품목 = a.품목
AND b.입고일시 <= a.주문일시
LEFT OUTER JOIN 입고 c
ON c.품목 = b.품목
AND c.입고일시 <= a.주문일시
AND c.입고일시 > b.입고일시
WHERE c.입고일시 IS NULL
ORDER BY id
;
by 생각
[2020.06.01 16:53:24]
WITH T1(ID,ITEM,AMT,IN_DATE) AS (
SELECT '1', '사과', '1200', to_DATE('202004021000', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '2', '배' , '2000', to_DATE('202004051200', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '3', '사과', '1300', to_DATE('202004121400', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '4', '딸기', '1100', to_DATE('202004101100', 'yyyymmddhh24miss') FROM DUAL
), T2(ID,ITEM,SEL,Ord_DATE) AS(
SELECT '1', '사과', '2000', to_DATE('202004031300', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '2', '배' , '2000', to_DATE('202004071100', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '3', '사과', '2300', to_DATE('202004120900', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '4', '딸기', '2000', to_DATE('202004131000', 'yyyymmddhh24miss') FROM DUAL
UNION ALL
SELECT '5', '사과', '2200', to_DATE('202004141000', 'yyyymmddhh24miss') FROM DUAL
)
SELECT ID
,ITEM
,SEL
,ORD_DATE
,AMT
,AMT_SEL
FROM (
SELECT T2.ID
,T2.ITEM
,T2.SEL
,T2.ORD_DATE
,T1.AMT
,Nvl((T2.SEL - T1.AMT),0) AMT_SEL
,Row_Number() OVER(PARTITION BY T2.ID ORDER BY T1.IN_DATE DESC) RN
FROM T1
,T2
WHERE T1.ITEM = T2.ITEM(+)
AND T1.IN_DATE <= T2.ORD_DATE
)
WHERE RN = 1
ORDER BY ID;
) 버튼을 클릭하여 작성 하시면 됩니다.