CODEWITHSUNDEEP
sqlsql-server-2014
Nick
Nick

Reputation: 372

Unpivot cross apply issue

I have the following query that uses CROSS APPLY to unpivot three columns into one that I am using with the ROW_NUMBER() function to assign a unique VOUCHER_LINE_NUM to it.

The problem I am facing is when I have an INVOICE_ID that has multiple VOUCHER_LINE_NUM's associated with it then my unpivoted rows are getting duplicated for every VOUCHER_LINE_NUM. I only want to unpivot the 'Freight' and 'Tax' rows once per INVOICE_ID. Here is an example from the output below that has the duplication with INVOICE_ID 1100000721021 :

Current Output:

INVOICE_ID       VOUCHER_LINE_NUM   LineType      Value
1100000720667    1                  Merchandise   17480.820
1100000720667    2                  Freight       59.500
1100000721021    1                  Merchandise   82.640
1100000721021    2                  Freight       129.010
1100000721021    2                  Merchandise   542.120
1100000721021    3                  Freight       129.010
1100000721021    3                  Merchandise   126.890
1100000721021    4                  Freight       129.010
1100000721021    4                  Merchandise   1022.030
1100000721021    5                  Freight       129.010
1100000721196    5                  Merchandise   4680.200
1100000721196    6                  Freight       55.500
1100000721196    7                  Tax           512.890

In the above example, I am getting duplicate 'Freight' rows (with Value 129.010) for each VOUCHER_LINE_NUM associated with INVOICE_ID 1100000721021. Here is the CREATE TABLE and INSERT scripts and the current query:

CREATE TABLE #TempVOUCHER_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4), MERCHANDISE_AMT decimal (8,3))    

CREATE TABLE #TempDISTRIB_LINE (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), VOUCHER_LINE_NUM varchar (4)) 
  
CREATE TABLE #TempVOUCHER (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), FREIGHT_AMT decimal (7,3), SALETX_AMT decimal (7,3), INVOICE_DT datetime, ACCOUNTING_DT datetime, CLOSE_STATUS varchar(4) )    

CREATE TABLE #TempPYMNT_VCHR_XREF (BUSINESS_UNIT varchar(10), VOUCHER_ID varchar(16), PYMNT_ID varchar (10))  
    
INSERT INTO #TempVOUCHER_LINE 
 (BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM, MERCHANDISE_AMT)
 VALUES ('11000', '00720667', '1', 17480.820),
        ('11000', '00721196', '5', 4680.200),
        ('11000', '00721021', '1', 82.64),
        ('11000', '00721021', '2', 542.12),
        ('11000', '00721021', '3', 126.89),
        ('11000', '00721021', '4', 1022.03)    

INSERT INTO #TempDISTRIB_LINE
 (BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM)
 VALUES ('11000', '00720667', '1'),
        ('11000', '00721196', '5'),
        ('11000', '00721021', '1'),
        ('11000', '00721021', '2'),
        ('11000', '00721021', '3'),
        ('11000', '00721021', '4')      

INSERT INTO #TempVOUCHER
(BUSINESS_UNIT, VOUCHER_ID, FREIGHT_AMT, SALETX_AMT, INVOICE_DT, ACCOUNTING_DT, CLOSE_STATUS)
VALUES ('11000', '00720667', 59.50, 0.00, '05-03-2019', '05-14-2014', 'A'),
       ('11000', '00721196', 55.50, 512.890, '08-10-2020', '08-12-2020', 'A'),
       ('11000', '00721021', 129.01, 0.00, '12-13-2019', '12-16-2019', 'A')    

INSERT INTO #TempPYMNT_VCHR_XREF
(BUSINESS_UNIT, VOUCHER_ID, PYMNT_ID)
VALUES ('11000', '00720667', ''),
       ('11000', '00721196', ''),
       ('11000', '00721021', '')    
;   

    WITH CTE AS
        (SELECT CONCAT(A.BUSINESS_UNIT, A.VOUCHER_ID) AS INVOICE_ID,
                A.VOUCHER_LINE_NUM,
                A.MERCHANDISE_AMT,
                C.FREIGHT_AMT,
            C.SALETX_AMT
     FROM #TempVOUCHER_LINE A
          LEFT OUTER JOIN #TempDISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT
                                             AND B.VOUCHER_ID = A.VOUCHER_ID
                                             AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
          LEFT OUTER JOIN #TempVOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
                                        AND C.VOUCHER_ID = A.VOUCHER_ID
          LEFT OUTER JOIN #TempPYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT
                                                AND I.VOUCHER_ID = A.VOUCHER_ID
     WHERE C.INVOICE_DT > '01-03-2019'
       AND C.ACCOUNTING_DT < '06-01-2021'
       AND I.PYMNT_ID = ''
       AND C.CLOSE_STATUS <> 'C'
       AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT C.INVOICE_ID,
       C.VOUCHER_LINE_NUM + ROW_NUMBER() OVER (PARTITION BY C.INVOICE_ID, VOUCHER_LINE_NUM ORDER BY VOUCHER_LINE_NUM)  - 1 AS VOUCHER_LINE_NUM,
       V.LineType,
       V.[Value]
FROM CTE C
     CROSS APPLY (VALUES('Merchandise',C.MERCHANDISE_AMT),
                        ('Freight',C.FREIGHT_AMT),
                        ('Tax',C.SALETX_AMT))V (LineType, Value)
WHERE V.[Value] <> 0;

Desired Output:

INVOICE_ID      VOUCHER_LINE_NUM    LineType      Value
1100000720667   1                   Merchandise   17480.820
1100000720667   2                   Freight       59.500    
1100000721021   1                   Merchandise   82.640
1100000721021   2                   Merchandise   542.120
1100000721021   3                   Merchandise   126.890
1100000721021   4                   Merchandise   1022.030
1100000721021   5                   Freight       129.010
1100000721196   5                   Merchandise   4680.200
1100000721196   6                   Freight       55.500    
1100000721196   7                   Tax           512.890

In the above desired output, the Freight line is only occuring once per INVOICE_ID and is assigned to whatever the highest (Max) VOUCHER_LINE_NUM is for that INVOICE_ID + 1, hence this INVOICE_ID (1100000721021) has VOUCHER_LINE_NUM of 1,2,3,4 so the Freight (and/or Tax) is assigned to VOUCHER_LINE_NUM 5 (and 6 if it also would have had Tax <> 0).

EDIT - Duplication with proposed Answer:

enter image description here

Upvotes: 0

Views: 106

Answers (1)

Thom A
Thom A

Reputation: 95557

You need to change the value of FREIGHT_AMT to 0.00 when it's not relevant in your CTE. You can do with with a CASE expression and ROW_NUMBER:

WITH CTE AS
    (SELECT CONCAT(A.BUSINESS_UNIT, A.VOUCHER_ID) AS INVOICE_ID,
            A.VOUCHER_LINE_NUM,
            A.MERCHANDISE_AMT,
            CASE WHEN ROW_NUMBER() OVER (PARTITION BY CONCAT(A.BUSINESS_UNIT, A.VOUCHER_ID) ORDER BY C.FREIGHT_AMT DESC, A.VOUCHER_LINE_NUM ASC) = 1 THEN C.FREIGHT_AMT ELSE 0.00 END AS FREIGHT_AMT,
            C.SALETX_AMT
    FROM #TempVOUCHER_LINE A --"A" is for VOUCHER_LINE? What A?
         LEFT OUTER JOIN #TempDISTRIB_LINE B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT --"B" is for DISTRIB_LINE. I would say "D" is.
                                            AND B.VOUCHER_ID = A.VOUCHER_ID
                                            AND B.VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
         INNER JOIN #TempVOUCHER C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT --"C" is for Voucher? I would say "V" is
                                       AND C.VOUCHER_ID = A.VOUCHER_ID
         INNER JOIN #TempPYMNT_VCHR_XREF I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT --"I" is for PYMNT_VCHR_XREF? What I?
                                               AND I.VOUCHER_ID = A.VOUCHER_ID
     WHERE C.INVOICE_DT > '20190103'
       AND C.ACCOUNTING_DT < '20210601'
       AND I.PYMNT_ID = ''
       AND C.CLOSE_STATUS <> 'C'
       AND A.VOUCHER_ID IN ('00720667', '00721196', '00721021', '00721171', '00326705'))
SELECT C.INVOICE_ID,
       MIN(C.VOUCHER_LINE_NUM) OVER (PARTITION BY C.INVOICE_ID) + ROW_NUMBER() OVER (PARTITION BY C.INVOICE_ID ORDER BY VOUCHER_LINE_NUM) - 1 AS VOUCHER_LINE_NUM,
       V.LineType,
       V.[Value]
FROM CTE C
     CROSS APPLY (VALUES('Merchandise',C.MERCHANDISE_AMT),
                        ('Freight',C.FREIGHT_AMT),
                        ('Tax',C.SALETX_AMT))V (LineType, Value)
WHERE V.[Value] <> 0;

db<>fiddle

Also, I really suggest fixing your aliases. "A" for DISTRIB_LINE makes little sense (it doesn't have any A's in it) and I wouldn't use the letter "C" to define the name VOUCHER ("V" would be far more appropriate).

I've also changed some of your JOINs. Having a LEFT OUTER JOIN to #TempVOUCHER and #TempPYMNT_VCHR_XREF makes no sense when the values of several of their columns cannot be NULL. It is impossible for a column to have a non-NULL value when the row didn't exist.

Finally, I don't udnerstasnd why the table #TempDISTRIB_LINE is even in the query, it's not referenced in the WHERE, the ONs nor the SELECT and as a LEFT OUTER JOIN it doesn't even do anything to filter the data. The "best" it could do would be cause duplicate rows in a one to many relationship. The table should likely be removed from the query.

Upvotes: 1

Related Questions