CODEWITHSUNDEEP
google-bigquery
Tam
Tam

Reputation: 11

How do you pull find the min value of a specific column, when left joined to another table, in Google BigQuery SQL?

I'm struggling to pull back min and max values on a transaction level basis. What I am trying to do is pair one type of transaction to another through left join. However, since there's multiple transactions that could be matched to my exception population, I'm pulling many duplicates. I've tried to followed the existing question and implement those tips but still not successful.

Here's an example of my query:

SELECT *

FROM ( Select DISTINCT 
        B.POS_TRANS_ID,
        B.SLS_DT,
        (MAX(B.SLS_DT) < H.BUS_DT) AS MAX_SLS_DT,
        (MIN(DATE_DIFF(H.BUS_DT,B.SLS_DT,DAY))) AS MIN_DAY_DIFF, 
        B.NET_EXT_RETL_AMT,
        H.STR_NBR,
        H.MKUP_MKDN_CTRL_NBR,
        H.MKDN_RSN_TXT,
        H.BUS_DT,
        H.CURR_RETL_AMT,
        H.TRANS_ID

FROM `EXAMPLE_TABLE` H
      left join `miami` B
      on H.SKU_NBR = B.SKU_NBR
      and H.STR_NBR = B.STR_NBR
      and (H.UNT_CNT*-1) = B.QTY_SOLD
      and (H.NET_MUMD_AMT*-1) = (B.CURR_RETL_AMT)

where 
      B.QTY_SOLD < 0
      and cast(MKUP_MKDN_CTRL_NBR as string) not like ('99%')
      AND H.TRANS_ID IN ('FLORIDA_TRANS')


GROUP BY 
        B.POS_TRANS_ID,
        B.SLS_DT, 
        B.NET_EXT_RETL_AMT,
        H.STR_NBR,
        H.MKUP_MKDN_CTRL_NBR,
        H.MKDN_RSN_TXT,
        H.BUS_DT,
        H.CURR_RETL_AMT,
        H.TRANS_ID)Z

WHERE MAX_SLS_DT IS TRUE
      AND Z.MKUP_MKDN_CTRL_NBR = 69308
      AND Z.STR_NBR = '3204'

Here's an example of my results:

 POS_TRANS_ID   SLS_DT    MAX_SLS_DT    MIN_DAY_DIFF    NET_EXT_RETL_AMT    STR_NBR MKUP_MKDN_CTRL_NBR
        4768    10/4/2017   TRUE            566                 -99           3204       69308
        5441    1/3/2017    TRUE            840                 -99           3204       69308
        8824    4/25/2018   TRUE            363                 -99           3204       69308
        8870    3/10/2018   TRUE            409                 -99           3204       69308

when I actually ONLY would like to the min date-diff and max_sls_dt by transaction by store...

Desired results: 

 POS_TRANS_ID   SLS_DT    MAX_SLS_DT    MIN_DAY_DIFF    NET_EXT_RETL_AMT    STR_NBR MKUP_MKDN_CTRL_NBR
        8824    4/25/2018   TRUE            363                 -99           3204       69308

I'm very much still learning SQL so any help would SO appreciated. Thank you in advance!

Upvotes: 0

Views: 64

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

ROW_NUMBER is one option here:

WITH cte AS (
    SELECT
        B.POS_TRANS_ID,
        B.SLS_DT,
        MAX(B.SLS_DT) < H.BUS_DT AS MAX_SLS_DT,
        MIN(DATE_DIFF(H.BUS_DT, B.SLS_DT, DAY)) AS MIN_DAY_DIFF, 
        B.NET_EXT_RETL_AMT,
        H.STR_NBR,
        H.MKUP_MKDN_CTRL_NBR,
        H.MKDN_RSN_TXT,
        H.BUS_DT,
        H.CURR_RETL_AMT,
        H.TRANS_ID,
        ROW_NUMBER() OVER (PARTITION BY H.STR_NBR
                           ORDER BY MIN(DATE_DIFF(H.BUS_DT, B.SLS_DT, DAY))) rn
    FROM EXAMPLE_TABLE H
    LEFT JOIN miami B
        ON H.SKU_NBR = B.SKU_NBR AND
           H.STR_NBR = B.STR_NBR AND
           H.UNT_CNT*-1 = B.QTY_SOLD AND
           H.NET_MUMD_AMT*-1 = B.CURR_RETL_AMT AND
           MKUP_MKDN_CTRL_NBR = 69308
    WHERE
        B.QTY_SOLD < 0 AND
        CAST(MKUP_MKDN_CTRL_NBR AS string) NOT LIKE '99%' AND
        H.TRANS_ID = 'FLORIDA_TRANS'
    GROUP BY 
        B.POS_TRANS_ID,
        B.SLS_DT, 
        B.NET_EXT_RETL_AMT,
        H.STR_NBR,
        H.MKUP_MKDN_CTRL_NBR,
        H.MKDN_RSN_TXT,
        H.BUS_DT,
        H.CURR_RETL_AMT,
        H.TRANS_ID
    HAVING
        MAX(B.SLS_DT) < H.BUS_DT IS TRUE
)

SELECT *
FROM cte
WHERE rn = 1;

Upvotes: 1

Related Questions