MSSQL PIVOT 휴무정보 로직

USE PRAC
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

;WITH CO_SO AS
(
SELECT 'ITEM_0001' ITEM_CD, 24000 SO_QTY, 1 PRIORITY UNION ALL
SELECT 'ITEM_0002', 26000, 2 UNION ALL
SELECT 'ITEM_0003', 25000, 3 UNION ALL
SELECT 'ITEM_0010', 15000, 6 UNION ALL
SELECT 'ITEM_0011', 40000, 4 UNION ALL
SELECT 'ITEM_0012', 25000, 5 
)
, CO_LINE_HOL AS
(
SELECT 'ALL' LINE_CD, 'REGULAR' HOL_TYP, '2017-07-24 8:00' START_DT_TIME, '2017-07-31 8:00' END_DT_TIME, '휴가' HOL_TXT UNION ALL
SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' UNION ALL
SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', ''
)
, CO_LINE_CAPA AS
(
SELECT 'L1' LINE_CD, 'ITEM_0001' ITEM_CD, 3000 CAPA_QTY UNION ALL
SELECT 'L1', 'ITEM_0002', 4000 UNION ALL
SELECT 'L1', 'ITEM_0003', 5000 UNION ALL
SELECT 'L2', 'ITEM_0010', 3000 UNION ALL
SELECT 'L2', 'ITEM_0011', 4000 UNION ALL
SELECT 'L2', 'ITEM_0012', 5000 
)
, CO_CALD_MAT AS
(
SELECT '20170717' DT, '201729' YW UNION ALL
SELECT '20170718', '201729'  UNION ALL
SELECT '20170719', '201729'  UNION ALL
SELECT '20170720', '201729'  UNION ALL
SELECT '20170721', '201729'  UNION ALL
SELECT '20170722', '201729'  UNION ALL
SELECT '20170723', '201729'  UNION ALL
SELECT '20170724', '201730'  UNION ALL
SELECT '20170725', '201730'  UNION ALL
SELECT '20170726', '201730'  UNION ALL
SELECT '20170727', '201730'  UNION ALL
SELECT '20170728', '201730'  UNION ALL
SELECT '20170729', '201730'  UNION ALL
SELECT '20170730', '201730'  UNION ALL
SELECT '20170731', '201731A' UNION ALL
SELECT '20170801', '201731B' UNION ALL
SELECT '20170802', '201731B' UNION ALL
SELECT '20170803', '201731B' UNION ALL
SELECT '20170804', '201731B' UNION ALL
SELECT '20170805', '201731B' UNION ALL
SELECT '20170806', '201731B' UNION ALL
SELECT '20170807', '201732'  UNION ALL
SELECT '20170808', '201732'  UNION ALL
SELECT '20170809', '201732'  UNION ALL
SELECT '20170810', '201732'  UNION ALL
SELECT '20170811', '201732'  UNION ALL
SELECT '20170812', '201732'  UNION ALL
SELECT '20170813', '201732'  UNION ALL
SELECT '20170814', '201733' 
)
SELECT A.LINE_CD  
     , A.ITEM_CD
   , SUM(CASE WHEN A.YW = '201729'  THEN SO_CAPA_QTY ELSE 0 END) AS [201729]
   , SUM(CASE WHEN A.YW = '201730'  THEN SO_CAPA_QTY ELSE 0 END) AS [201730]
   , SUM(CASE WHEN A.YW = '201731A' THEN SO_CAPA_QTY ELSE 0 END) AS [201731A]
   , SUM(CASE WHEN A.YW = '201731B' THEN SO_CAPA_QTY ELSE 0 END) AS [201731B]
     , SUM(CASE WHEN A.YW = '201732'  THEN SO_CAPA_QTY ELSE 0 END) AS [201732]
     , SUM(CASE WHEN A.YW = '201733'  THEN SO_CAPA_QTY ELSE 0 END) AS [201733]
  FROM (
        SELECT A.PRIORITY
             , A.LINE_CD
           , A.ITEM_CD
           , B.DT
       , B.YW
           , A.SO_QTY
           , A.CAPA_QTY
           , B.LV
           , A.START_NO
           , PRAC.DBO.FN_LEAST(A.CAPA_QTY, A.SO_QTY - (B.LV - A.START_NO) * A.CAPA_QTY) AS SO_CAPA_QTY -- *
          FROM (
                SELECT A.PRIORITY
                     , B.LINE_CD
                   , B.ITEM_CD
                   , A.SO_QTY
                   , B.CAPA_QTY
                   , SUM(CEILING(CONVERT(NUMERIC(15, 3), A.SO_QTY) / B.CAPA_QTY)) OVER(PARTITION BY B.LINE_CD ORDER BY A.PRIORITY)
                   - CEILING(CONVERT(NUMERIC(15, 3), A.SO_QTY) / B.CAPA_QTY) + 1                                                   AS START_NO -- *
                   , SUM(CEILING(CONVERT(NUMERIC(15, 3), A.SO_QTY) / B.CAPA_QTY)) OVER(PARTITION BY B.LINE_CD ORDER BY A.PRIORITY) AS END_NO   -- *
                  FROM CO_SO A
                       INNER JOIN CO_LINE_CAPA B ON 1=1
                            AND A.ITEM_CD = B.ITEM_CD
               ) A
             INNER JOIN (
                           SELECT RANK() OVER (PARTITION BY A.LINE_CD ORDER BY DT) AS LV
                                , A.LINE_CD
                                , B.DT
                                , B.YW
                             FROM (
                                   SELECT DISTINCT LINE_CD
                                   FROM CO_LINE_CAPA
                                  ) A
                               FULL OUTER JOIN CO_CALD_MAT B ON 1=1
                               LEFT OUTER JOIN CO_LINE_HOL C ON 1=1
                                           AND A.LINE_CD = CASE WHEN C.LINE_CD = 'ALL' THEN A.LINE_CD ELSE C.LINE_CD END
                                     AND B.DT >= LEFT(REPLACE(C.START_DT_TIME, '-', ''), 8)
                                     AND B.DT <  LEFT(REPLACE(C.END_DT_TIME,   '-', ''), 8)
                            WHERE C.LINE_CD IS NULL
                        ) B ON 1=1
                AND A.LINE_CD = B.LINE_CD
                AND B.LV BETWEEN A.START_NO AND A.END_NO
       ) A
 GROUP BY A.LINE_CD, A.ITEM_CD