Блог

У меня есть следующий CTE, который выполняет суммирование в поле MERCHANDISE_AMT, отдельно от двух приведенных ниже запросов, а затем я объединяю эти результаты вместе в окончательном выборе. У меня возникла проблема с Group By либо во внутренних запросах, либо в окончательном операторе Select. Я хочу иметь возможность группировать LINE_NBR и PO_DIST_LINE_NUM вместе, чтобы в конечном итоге у меня было 2 строки. Мне нужно, чтобы эти столбцы были выбраны, хотя для того, чтобы ссылаться на них в объединении в конечном запросе.

 WITH CTE AS (
SELECT 
        A.BUSINESS_UNIT, 
        A.PO_ID,  
        A.PO_TYPE, 
        A.PO_STATUS, 
        (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT, 
        A.VENDOR_SETID, 
        A.VENDOR_ID, 
        A.BUYER_ID, 
        D.DEPTID, 
        D.LINE_NBR, 
        D.SCHED_NBR, 
        D.DISTRIB_LINE_NUM,
        SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,  
        E.SETID   '_'   E.DEPTID AS REQUESTOR, 
        H.ROLEUSER_SUPR
    FROM 
        PS_PO_LINE_DISTRIB D 
            INNER JOIN PS_PO_LINE C 
                ON  D.BUSINESS_UNIT = C.BUSINESS_UNIT 
                AND D.PO_ID = C.PO_ID 
                AND D.LINE_NBR = C.LINE_NBR 
                INNER JOIN PS_PO_HDR A 
                    ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
                    AND C.PO_ID = A.PO_ID 
                    AND A.PO_TYPE IN ('AGR','BO')
                    AND A.PO_STATUS IN ('A','D','O')
            INNER JOIN PS_DEPT_TBL E 
                ON D.DEPTID = E.DEPTID 
                AND D.BUSINESS_UNIT_GL = E.SETID 
                AND E.EFFDT = (SELECT MAX(A_ED.EFFDT) 
                                    FROM PS_DEPT_TBL A_ED
                                    WHERE E.SETID = A_ED.SETID
                                        AND E.DEPTID = A_ED.DEPTID
                                        AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))


                INNER JOIN PS_ROLEXLATOPR H 
                    ON E.SETID   '_'     E.DEPTID = H.ROLEUSER 
    WHERE 
            D.PO_ID = 'J010000185'
        AND D.BUSINESS_UNIT = '50000'
    GROUP BY 
        A.BUSINESS_UNIT,
        A.PO_ID, 
        A.PO_TYPE, 
        A.PO_STATUS, 
        (CONVERT(CHAR(10), A.PO_DT, 121)), 
        A.VENDOR_SETID, 
        A.VENDOR_ID, 
        A.BUYER_ID, 
        D.DEPTID, 
        E.SETID   '_'   E.DEPTID, 
        H.ROLEUSER_SUPR, 
        D.LINE_NBR, 
        D.SCHED_NBR, 
        D.DISTRIB_LINE_NUM ), 

CTE2 AS (
SELECT 
        A.BUSINESS_UNIT, 
        A.PO_ID,  
        A.PO_TYPE, 
        A.PO_STATUS, 
        (CONVERT(CHAR(10), A.PO_DT, 121)) AS PO_DT, 
        A.VENDOR_SETID, 
        A.VENDOR_ID, 
        A.BUYER_ID, 
        G.DEPTID, 
        H.ROLEUSER_SUPR, 
        SUM(G.MERCHANDISE_AMT) AS SUM_MERCH, 
        G.BUSINESS_UNIT_PO, 
        G.SCHED_NBR, 
        G.LINE_NBR, 
        G.PO_DIST_LINE_NUM
    FROM 
        PS_DISTRIB_LINE G
            INNER JOIN  PS_PO_LINE C 
                ON  G.BUSINESS_UNIT = C.BUSINESS_UNIT 
                AND G.PO_ID = C.PO_ID 
                AND G.LINE_NBR = C.LINE_NBR
                INNER JOIN PS_PO_HDR A 
                    ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
                    AND C.PO_ID = A.PO_ID
            INNER JOIN PS_DEPT_TBL E 
                ON  G.DEPTID = E.DEPTID
                AND G.BUSINESS_UNIT_GL = E.SETID 
                AND E.EFFDT = ( SELECT MAX(A_ED.EFFDT) 
                                    FROM PS_DEPT_TBL A_ED
                                    WHERE E.SETID = A_ED.SETID
                                    AND E.DEPTID = A_ED.DEPTID
                                    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
                INNER JOIN PS_ROLEXLATOPR H 
                    ON E.SETID   '_'   E.DEPTID = H.ROLEUSER
    WHERE 
            G.BUSINESS_UNIT = '50000'
        AND G.PO_ID = 'J010000185'
    GROUP BY 
        A.BUSINESS_UNIT,
        A.PO_ID, 
        A.PO_TYPE, 
        A.PO_STATUS, 
        (CONVERT(CHAR(10),A.PO_DT,121)), 
        A.VENDOR_SETID, 
        A.VENDOR_ID, 
        A.BUYER_ID, 
        G.DEPTID,  
        H.ROLEUSER_SUPR,
        G.BUSINESS_UNIT_PO,
        G.SCHED_NBR, 
        G.PO_DIST_LINE_NUM, 
        G.LINE_NBR,  
        E.SETID   '_'   E.DEPTID, 
        G.PO_DIST_LINE_NUM, 
        G.LINE_NBR )


SELECT DISTINCT  
        D.REQUESTOR, 
        D.BUSINESS_UNIT, 
        D.PO_ID, 
        D.PO_TYPE, 
        D.PO_STATUS, 
        (CONVERT(CHAR(10),D.PO_DT,121)), 
        D.VENDOR_SETID, 
        D.VENDOR_ID, 
        D.BUYER_ID, 
        D.DEPTID, 
        D.ROLEUSER_SUPR, 
        NULLIF((G.SUM_MERCH / D.SUM_MERCH),0) AS 'Threshold'
    FROM 
        CTE D
            LEFT OUTER JOIN CTE2 G 
                ON D.PO_ID = G.PO_ID
                AND D.BUSINESS_UNIT = G.BUSINESS_UNIT_PO 
                AND D.SCHED_NBR = G.SCHED_NBR 
                AND D.LINE_NBR = G.LINE_NBR 
                AND D.DISTRIB_LINE_NUM = G.PO_DIST_LINE_NUM 
  

Вот текущий результат:

 REQUESTOR   BUSINESS_UNIT   PO_ID       PO_TYPE  PO_STATUS  (No column name)    VENDOR_SETID    VENDOR_ID   BUYER_ID    DEPTID  ROLEUSER_SUPR   Threshold
11000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     TVORHIS         0.557487
11000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     TVORHIS         0.750000
41000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     MHOPSON         0.557487
41000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     MHOPSON         0.750000
  

Желаемый конечный результат был бы следующим:

 REQUESTOR   BUSINESS_UNIT   PO_ID       PO_TYPE  PO_STATUS  (No column name)    VENDOR_SETID    VENDOR_ID   BUYER_ID    DEPTID  ROLEUSER_SUPR  Threshold
11000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     TVORHIS       .7209
41000_744   50000           J010000185  AGR      D          2017-05-30          SHARE           90238A      EDARPINO    744     MHOPSON       .7094
  

Проблема в том, что вычисление поля ‘Threshold’ выполняется отдельно для каждого ЗАПРАШИВАЮЩЕГО, я полагаю, из-за поля DISTRIBUT_LINE_NUM из 1-го запроса CTE (псевдоним ‘CTE’). Если я удалю столбец DISTRIBUT_LINE_NUM из 1-го запроса, я получу вывод из 2 строк, но для выполнения объединения в конечном запросе оказывается, что мне нужно выполнить select в поле в 1-м запросе, чтобы иметь возможность ссылаться на него в соединении в окончательном запросе select (если только нет способа обойти это).

Я не верю, что есть способ выбрать неагрегированный столбец, а затем не использовать его в Group By, поэтому я ищу решение таким образом. Есть ли способ применить Group By к окончательному выбору, чтобы четыре строки были сгруппированы в две, с помощью REQUESTOR или, альтернативно, путем изменения внутренней группы запросов By?

Ниже должно сработать. Идея в том, что вы объединяете свои файлы, затем суммируете уровень группы и получаете свои значения в одной строке, затем находите пороговое значение для результата. Обратите внимание — создайте псевдоним для вашего значения PO_DT после преобразования.

 Select REQUESTOR, BUSINESS_UNIT, PO_ID, PO_TYPE, PO_STATUS, PO_DT, VENDOR_SETID, VENDOR_ID, BUYER_ID, DEPTID, ROLEUSER_SUPR, NULLIF((SUM_MERCHA / SUM_MERCHB),0) AS 'Threshold' from
(
Select REQUESTOR, BUSINESS_UNIT, PO_ID, PO_TYPE, PO_STATUS, PO_DT, VENDOR_SETID, VENDOR_ID, BUYER_ID, DEPTID, ROLEUSER_SUPR, SUM(SUM_MERCHA) as SUM_MERCHA, SUM(SUM_MERCHB) as SUM_MERCHB from (
SELECT DISTINCT  D.REQUESTOR, D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS, (CONVERT(CHAR(10),D.PO_DT,121)) as PO_DT, D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR, G.MERCHANDISE_AMT as SUM_MERCHA,  D.MERCHANDISE_AMT as SUM_MERCHB
FROM CTE D
LEFT OUTER JOIN CTE2 G ON G.PO_ID = D.PO_ID AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT  AND G.SCHED_NBR = D.SCHED_NBR AND G.LINE_NBR = D.LINE_NBR AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM)
Group by D.REQUESTOR, D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS, PO_DT, D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR) E ) F