SQL Sub-Query to Replace a Value from 1 Dataset into 2nd Dataset based on Match Condition-CodePudding

I currently have a Union between 2 subsets of data. The subsets are exactly the same with the exception of the filter criteria for data selection (sales vs returns). However, for the returns data, I need to replace the PO with the original PO number (from sales subset data) so that I can align returns with the sales qty for the reporting.

I have a unique key to reference them together, but am not sure how to replace the returns PO with the original sale PO (where these unique keys match), so that it can be aligned. This is all done with a SQL sub-query. I know what I want to do, but now sure how to do this using SQL syntax.

Please note, I cannot create temp tables because this is SQL logic to be used for Power BI dashboard so this logic will have to leverage sub-query inner selects (if feasible).

Here's code I currently have:

SELECT * FROM
 (Select * From 
    (SELECT
     concat(b.[Invc_Nbr],b.[Material_Nbr]) as Unique_Key,
     o.[DOC_NUMBER_ITEM_KEY],o.[Hist_Cust_PO_Nbr],o.[Ord_Qty],    
     SUM(b.[Credits_Qty]) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Credits_Qty,
     MIN(b.Invc_Date) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Invc_Date
     from [processed_abdc_operations].[SAP_Orders] o
     left join [processed_abdc_operations].[sap_Billing] b
     on o.[DOC_NUMBER_ITEM_KEY]=b.[Ord_Nbr_Ord_Line_Nbr]
     and b.[Invc_Type_cd]='ZF2'
     where o.[Ord_Type_CD] = 'ZASP') **sales**
UNION
Select * From
    (SELECT
     concat(b.[Orig_Invc_Nbr],b.[Material_Nbr]) as Unique_Key,
     o.[DOC_NUMBER_ITEM_KEY],o.[Hist_Cust_PO_Nbr],o.[Ord_Qty],    
     SUM(b.[Credits_Qty]) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Credits_Qty,
     MIN(b.Invc_Date) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Invc_Date
     from [processed_abdc_operations].[SAP_Orders] o
     left join [processed_abdc_operations].[sap_Billing] b
     on o.[DOC_NUMBER_ITEM_KEY]=b.[Ord_Nbr_Ord_Line_Nbr]
     and b.[Invc_Type_cd]='ZRE'
     where o.[Ord_Type_CD] = 'ZRCM') **creditmemo**) a

Returns results as follows: Returned Results

Desired Result: Replace the [Hist_Cust_PO_Nbr] from the creditmemo data with value '00000000000002011274' since the Unique_Keys match.

CodePudding user response：

use a windowing function to get the min hist cust po nbr per unique key

 min(Hist_Cust_Po_Nbr) over (partition by unique_key order by Hist_cust_Po_nbr)

full query

   SELECT unique_key, DOC_NUMBER_ITEM_KEY, 
        min(Hist_Cust_Po_Nbr) over (partition by unique_key order by Hist_cust_Po_nbr)
        as Hist_Cust_Po_Nbr, ord_qty, credits_qty, invc_date FROM
         (Select * From 
            (SELECT
             concat(b.[Invc_Nbr],b.[Material_Nbr]) as Unique_Key,
             o.[DOC_NUMBER_ITEM_KEY],o.[Hist_Cust_PO_Nbr],o.[Ord_Qty],    
             SUM(b.[Credits_Qty]) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Credits_Qty,
             MIN(b.Invc_Date) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Invc_Date
             from [processed_abdc_operations].[SAP_Orders] o
             left join [processed_abdc_operations].[sap_Billing] b
             on o.[DOC_NUMBER_ITEM_KEY]=b.[Ord_Nbr_Ord_Line_Nbr]
             and b.[Invc_Type_cd]='ZF2'
             where o.[Ord_Type_CD] = 'ZASP') **sales**
        UNION
        Select * From
            (SELECT
             concat(b.[Orig_Invc_Nbr],b.[Material_Nbr]) as Unique_Key,
             o.[DOC_NUMBER_ITEM_KEY],o.[Hist_Cust_PO_Nbr],o.[Ord_Qty],    
             SUM(b.[Credits_Qty]) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Credits_Qty,
             MIN(b.Invc_Date) OVER(PARTITION BY b.[Ord_Nbr_Ord_Line_Nbr]) AS Invc_Date
             from [processed_abdc_operations].[SAP_Orders] o
             left join [processed_abdc_operations].[sap_Billing] b
             on o.[DOC_NUMBER_ITEM_KEY]=b.[Ord_Nbr_Ord_Line_Nbr]
             and b.[Invc_Type_cd]='ZRE'
             where o.[Ord_Type_CD] = 'ZRCM') **creditmemo**) a

also you mentioned you were using power bi. looks like maybe you could leave your query as is and do the partitioning directly in power bi using DAX https://community.powerbi.com/t5/DAX-Commands-and-Tips/COUNT-OVER-PARTITION-in-DAX/m-p/1036124