MSSQL 재귀 쿼리 - Line 별 Item 우선순위에 따른 Capa 제약 반영

USE T3_STAGING_HQC

CREATE TABLE TEST_CO_SO
(
  ITEM_CD  NVARCHAR(50)
, SO_QTY   INT
, PRIORITY INT
)

CREATE TABLE TEST_CO_LINE_MST
(
  LINE_CD  NVARCHAR(50)
, ITEM_CD  NVARCHAR(50)
, CAPA_QTY INT
, PRIORITY INT
)
/*  최대값을 구하는 함수 */
CREATE FUNCTION FN_GREATEST (@pVALUE1 NVARCHAR(255), @pVALUE2 NVARCHAR(255)) 
RETURNS NVARCHAR(255) 
AS 
BEGIN 
DECLARE @sRTN NVARCHAR(255) 

SET @sRTN = ''

IF LTRIM(RTRIM(@pVALUE1)) >= LTRIM(RTRIM(@pVALUE2)) 
BEGIN 
SET @sRTN = @pVALUE1 
END ELSE 
BEGIN 
SET @sRTN = @pVALUE2 
END 

RETURN (@sRTN) 
END 

/*  최소값을 구하는 함수 */
CREATE FUNCTION FN_LEAST (@pVALUE1 VARCHAR(255), @pVALUE2 VARCHAR(255)) 
RETURNS varchar(255) 
AS 
BEGIN 
DECLARE @sRTN varchar(255) 

SET @sRTN = ''

IF LTRIM(RTRIM(@pVALUE1)) <= LTRIM(RTRIM(@pVALUE2)) 
BEGIN 
SET @sRTN = @pVALUE1 
END ELSE 
BEGIN 
SET @sRTN = @pVALUE2 
END 

RETURN (@sRTN) 
END 

INSERT INTO TEST_CO_SO
SELECT 'A' ITEM_CD, 5000 SO_QTY, 1 PRIORITY UNION ALL 
SELECT 'B', 9000, 2 

INSERT INTO TEST_CO_LINE_MST
SELECT 'L1' LINE_CD, 'A' ITEM_CD, 3000 CAPA_QTY, 1 PRIORITY UNION ALL 
SELECT 'L1', 'B', 3000, 1 UNION ALL 
SELECT 'L2', 'A', 2000, 2 UNION ALL 
SELECT 'L2', 'B', 2000, 2 

;WITH WITH_CO_RST(LINE_CD, ITEM_CD, CAPA_QTY, LV, SO_QTY, SO_CAPA_QTY, USE_CAPA_QTY, REMAIN_CAPA_QTY, PRIORITY) AS
(
 SELECT B.LINE_CD
      , B.ITEM_CD
	  , B.CAPA_QTY
	  , 1           AS LV
	  , A.SO_QTY
	  , T3_MAIN_HQC.DBO.FN_LEAST(A.SO_QTY, B.CAPA_QTY) AS SO_CAPA_QTY
	  , T3_MAIN_HQC.DBO.FN_LEAST(A.SO_QTY, B.CAPA_QTY) AS USE_CAPA_QTY
	  , B.CAPA_QTY - T3_MAIN_HQC.DBO.FN_LEAST(A.SO_QTY, B.CAPA_QTY) AS REMAIN_CAPA_QTY
	  , A.PRIORITY
   FROM TEST_CO_SO A
        INNER JOIN TEST_CO_LINE_MST B
		        ON 1=1
			   AND A.PRIORITY = 1
			   AND B.PRIORITY = 1
			   AND A.ITEM_CD  = B.ITEM_CD
        UNION ALL
 SELECT B.LINE_CD
      , B.ITEM_CD
	  , B.CAPA_QTY
	  , C.LV + CASE WHEN C.SO_CAPA_QTY = C.SO_QTY THEN 1 ELSE 0 END AS LV -- 하나의 LINE 에 CAPA 를 다 사용하면 LV 를 올려준다.
	  , A.SO_QTY -- 5000
      , CASE WHEN C.ITEM_CD = A.ITEM_CD THEN DBO.FN_LEAST(A.SO_QTY - C.SO_CAPA_QTY, B.CAPA_QTY) + C.SO_CAPA_QTY 
	                                    ELSE DBO.FN_LEAST(A.SO_QTY, CASE WHEN C.LINE_CD = B.LINE_CD THEN C.REMAIN_CAPA_QTY ELSE B.CAPA_QTY END) END AS SO_CAPA_QTY
      , CASE WHEN C.ITEM_CD = A.ITEM_CD THEN DBO.FN_LEAST(A.SO_QTY - C.SO_CAPA_QTY, B.CAPA_QTY)
	                                    ELSE DBO.FN_LEAST(A.SO_QTY, CASE WHEN C.LINE_CD = B.LINE_CD THEN C.REMAIN_CAPA_QTY ELSE B.CAPA_QTY END) END AS USE_CAPA_QTY
      , CASE WHEN C.LINE_CD = B.LINE_CD THEN C.REMAIN_CAPA_QTY ELSE B.CAPA_QTY END 
	  - CASE WHEN C.ITEM_CD = A.ITEM_CD THEN DBO.FN_LEAST(A.SO_QTY - C.SO_CAPA_QTY, B.CAPA_QTY)
	                                    ELSE DBO.FN_LEAST(A.SO_QTY, CASE WHEN C.LINE_CD = B.LINE_CD THEN C.REMAIN_CAPA_QTY ELSE B.CAPA_QTY END) END AS REMAIN_CAPA_QTY
      , A.PRIORITY
   FROM TEST_CO_SO A
        INNER JOIN TEST_CO_LINE_MST B
		        ON 1=1
			   AND A.ITEM_CD  = B.ITEM_CD
        INNER JOIN WITH_CO_RST C
		        ON 1=1
			   AND A.PRIORITY = C.PRIORITY + CASE WHEN C.SO_CAPA_QTY = C.SO_QTY THEN 1 ELSE 0 END
               AND B.LINE_CD  = CASE WHEN C.REMAIN_CAPA_QTY = 0 AND C.LINE_CD  = 'L2' THEN 'L1' -- 마지막 라인의 CAPA 가 0 이 되면 첫 라인으로 복귀
                                     WHEN C.REMAIN_CAPA_QTY = 0 AND C.LINE_CD != 'L2' THEN 'L' + CONVERT(NVARCHAR(1), SUBSTRING(C.LINE_CD, 2, 1) + 1) -- CAPA 가 0 이 되면 남은 라인으로 계속 간다.
                                     ELSE C.LINE_CD END
)
SELECT LINE_CD
     , ITEM_CD
     , CONVERT(NVARCHAR(8), CONVERT(DATETIME, '20170717', 112) + LV - 1, 112) AS CALD_DATE
	 , CAPA_QTY
	 , LV
     , SO_QTY
	 , SO_CAPA_QTY
     , USE_CAPA_QTY
     , REMAIN_CAPA_QTY
	 , PRIORITY
  FROM WITH_CO_RST