마농님, 계산식 질문입니다. 0 4 1,211

by 캘린다 [2019.03.22 17:08:51]



결과값
gubun CODE_N NO_P F_DATE M_DATE M_WEEK QTY TYPE
OLD lg_sam 521101-01 20180101 20190101 20190101 100 A_TYPE
NEW lg_sam 521101-02 20180102 20190107 20190102 100 B_TYPE
NEW lg_sam 521101-03 20180103 20190114 20190103 100 C_TYPE
NEW lg_sam 521101-04 20180104 20190114 20190103 50 C_TYPE
OLD LG-SAM 521102-01 20180105 20190107 20190102 100 A_TYPE
OLD LG-SAM 521102-02 20180106 20190108 20190102 100 A_TYPE
NEW LG-SAM 521102-03 20180107 20190114 20190103 50 B_TYPE
NEW LG-SAM 521102-03 20180107 20190121 20190104 50 B_TYPE
NEW LG-SAM 521102-04 20180108 20190121 20190104 100 B_TYPE


WITH t AS
(
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty, 'A_TYPE' type FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100, 'B_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100, 'C_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100, 'C_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100,'A_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100, 'A_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100, 'B_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100, 'B_TYPE' FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty,'A_TYPE' type FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250, 'B_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150, 'C_TYPE'  FROM dual
UNION ALL SELECT 'LG-SAM', '20190102', 350, 'A_TYPE'  FROM dual
UNION ALL SELECT 'LG-SAM', '20190103',  50, 'B_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '20190104',  150, 'B_TYPE' FROM dual
)
, calendar AS
(
SELECT '20190101' d_date, '20190101' d_week FROM dual
UNION ALL SELECT '20190102', '20190101' FROM dual
UNION ALL SELECT '20190103', '20190101' FROM dual
UNION ALL SELECT '20190104', '20190101' FROM dual
UNION ALL SELECT '20190105', '20190101' FROM dual
UNION ALL SELECT '20190106', '20190101' FROM dual
UNION ALL SELECT '20190107', '20190102' FROM dual
UNION ALL SELECT '20190108', '20190102' FROM dual
UNION ALL SELECT '20190109', '20190102' FROM dual
UNION ALL SELECT '20190110', '20190102' FROM dual
UNION ALL SELECT '20190111', '20190102' FROM dual
UNION ALL SELECT '20190112', '20190102' FROM dual
UNION ALL SELECT '20190113', '20190102' FROM dual
UNION ALL SELECT '20190114', '20190103' FROM dual
)

by 마농 [2019.03.25 09:03:37]

code 가 있는 곳마다 type 추가해 주면 됩니다.
혹시 달력 자료를 추가 안해서 원하는 결과가 안나온게 아닐런지?

WITH t AS
(
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty, 'A_TYPE' type FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100, 'B_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100, 'C_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100, 'C_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100, 'A_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100, 'A_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100, 'B_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100, 'B_TYPE' FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty,'A_TYPE' type FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250, 'B_TYPE' FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150, 'C_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '20190102', 350, 'A_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '20190103',  50, 'B_TYPE' FROM dual
UNION ALL SELECT 'LG-SAM', '20190104', 150, 'B_TYPE' FROM dual
)
, calendar AS
(
SELECT '20190101' d_date, '20190101' d_week FROM dual
UNION ALL SELECT '20190102', '20190101' FROM dual
UNION ALL SELECT '20190103', '20190101' FROM dual
UNION ALL SELECT '20190104', '20190101' FROM dual
UNION ALL SELECT '20190105', '20190101' FROM dual
UNION ALL SELECT '20190106', '20190101' FROM dual
UNION ALL SELECT '20190107', '20190102' FROM dual
UNION ALL SELECT '20190108', '20190102' FROM dual
UNION ALL SELECT '20190109', '20190102' FROM dual
UNION ALL SELECT '20190110', '20190102' FROM dual
UNION ALL SELECT '20190111', '20190102' FROM dual
UNION ALL SELECT '20190112', '20190102' FROM dual
UNION ALL SELECT '20190113', '20190102' FROM dual
UNION ALL SELECT '20190114', '20190103' FROM dual
UNION ALL SELECT '20190115', '20190103' FROM dual
UNION ALL SELECT '20190116', '20190103' FROM dual
UNION ALL SELECT '20190117', '20190103' FROM dual
UNION ALL SELECT '20190118', '20190103' FROM dual
UNION ALL SELECT '20190119', '20190103' FROM dual
UNION ALL SELECT '20190120', '20190103' FROM dual
UNION ALL SELECT '20190121', '20190104' FROM dual
)
SELECT a.code_n
     , a.type
     , a.no_p
     , a.f_date
     , DECODE(gb, 'NEW', c.d_date, a.m_date) m_date
     , a.gb
     , a.m_week
     , SUM(a.use_qty) use_qty
  FROM (SELECT a.code_n, a.type, a.no_p, a.f_date, a.m_date
             , CASE WHEN a.m_week < b.m_week THEN 'NEW'    ELSE 'OLD'    END gb
             , CASE WHEN a.m_week < b.m_week THEN b.m_week ELSE a.m_week END m_week
             , LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    ) use_qty
          FROM (SELECT code_n, type, no_p, f_date, m_date, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_date, f_date, no_p) s_qty
                  FROM t
                ) a
             , (SELECT code_n, type, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_week) s_qty
                  FROM c
                ) b
         WHERE a.code_n = b.code_n
           AND a.type   = b.type
           AND a.s_qty  > b.s_qty - b.qty
           AND b.s_qty  > a.s_qty - a.qty
        ) a
     , (SELECT d_date
             , SUBSTR(d_week, 1, 4) yyyy
             , SUBSTR(d_week, 7, 2) ww
             , ROW_NUMBER() OVER(PARTITION BY SUBSTR(d_week, 1, 4), SUBSTR(d_week, 7, 2) ORDER BY d_date) rn
          FROM calendar
        ) c
 WHERE a.m_week LIKE c.yyyy || '__' || c.ww
   AND c.rn = 1
 GROUP BY a.code_n, a.type, DECODE(a.gb, 'NEW', c.d_date, a.m_date), a.f_date, a.no_p, a.gb, a.m_week
 ORDER BY code_n, type, m_date, f_date, no_p
;

 


by 마농 [2019.03.25 11:02:34]

그럴리가요?
마음대로 판단하면 안되죠.
자동으로 해주는 건 없습니다.
조건이 바뀌면 프로그램도 바껴야죠.
가변적인 상황이라면 프로그래밍은 더욱 더 복잡해질 뿐입니다.
규칙을 최대한 간결하게 가져가야지만 프로그램이 단순화 됩니다.


by 캘린다 [2019.03.25 12:57:47]

흠.. 제가 너무 간단하게 생각했었네요.

만약 그렇다면 

tpye의 data가 lg_sam만 존재한다면 LG-SAM은 결과값이 나오지 않게되는건가요?

lg_sam만 code_n별, type별 조건이라면 LG-SAM은 code_n별만 결과를 나오게하고 싶다면?


by 마농 [2019.03.25 13:13:09]

글쎄요?
말로? 글로? 생각만으로? 걱정 하지 마시고.
직접 엑셀에 표를 만들어 가면서 고민해 보세요.
쿼리 작성도 어렵지만, 쿼리 작성을 하기 위한 기준을 정하는 것이 더 중요합니다.
이런식으로 계속 예외 상황을 추가하고 또 추가하고 하지 마시고.
처음부터 가능한 경우의 수를 모두 따져봐야 할 것 같습니다.
그 속에서 공통적인 규칙을 찾아야 합니다.

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