고객 주문 시, 상품 입고 시점에 따라 매입가를 자동 추출하고 싶습니다(완전 초보입니다) 0 2 176

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;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입