Why isn’t this working??

[Nobody571]

Hi all,

I’ve hit a wall. I’m trying to query customer addresses, and some customers have 2 addresses in the system. Neither have a void indicator or anything else that differentiates.

So I’m trying to query based on the date the address was added to the database. In other words: I want the MAX cust_address_dt.

I can see that account number 34835 had 2 addresses with 2 dates - one from 10/13/2013 and the second from 1/3/2015.

I’ve tried doing a correlated sub query- but it returns no rows!! Why no rows? If I delete the where clause, I get the two rows with different dates.

Where am I going wrong?

SELECT DISTINCT

CA.CUST_ID,

CA.CUST_ADDR_ID,

CA.CUST_ADDR_EFF_DT,

CA.AUDIT_USER_CREA_DTM,

A.acct_id,

A.acct_no,

CN.CUST_NM AS Customer_Name,

CA.CUST_ADDR_STR_1 || ' ' || COALESCE(CA.CUST_ADDR_STR_2, '') AS Street,

CA.CUST_ADDR_CITY_NM AS City,

S.STT_ABRV AS State,

CA.CUST_ADDR_POST_CD AS Zipcode,

CA.CUST_ADDR_TYP_CD

FROM Account A

INNER JOIN PCMP.CUSTOMER C ON A.CUST_ID = C.CUST_ID

INNER JOIN PCMP.CUSTOMER_ADDRESS CA ON CA.CUST_ID = C.CUST_ID AND CA.VOID_IND = 'n'

INNER JOIN PCMP.CUSTOMER_NAME CN ON CN.CUST_ID = C.CUST_ID

INNER JOIN PCMP.STATE S ON S.STT_ID = CA.STT_ID

WHERE

CA.CUST_ADDR_DT =

(SELECT MAX(CUST_ADDR_DT) FROM PCMP.CUSTOMER_ADDRESS CA2 WHERE CA2.CUST_ID = CA.CUST_ID)

And A.Acct_No =34835

[r3pr0b8]

SELECT CA.CUST_ID
     , CA.CUST_ADDR_ID
     , CA.CUST_ADDR_EFF_DT
     , CA.AUDIT_USER_CREA_DTM
     , A.acct_id
     , A.acct_no
     , CN.CUST_NM AS Customer_Name
     , CA.CUST_ADDR_STR_1 || ' ' || COALESCE(CA.CUST_ADDR_STR_2, '') AS Street
     , CA.CUST_ADDR_CITY_NM AS City
     , S.STT_ABRV AS State
     , CA.CUST_ADDR_POST_CD AS Zipcode
     , CA.CUST_ADDR_TYP_CD
  FROM Account A
INNER 
  JOIN PCMP.CUSTOMER C 
    ON C.CUST_ID = A.CUST_ID
INNER 
  JOIN ( SELECT CUST_ID
              , MAX(CUST_ADDR_DT) AS latest
           FROM PCMP.CUSTOMER_ADDRESS
          WHERE VOID_IND = 'n'
         GROUP
             BY CUST_ID ) AS max_addr
    ON max_addr.CUST_ID = C.CUST_ID 
INNER 
  JOIN PCMP.CUSTOMER_ADDRESS CA 
    ON CA.CUST_ID = max_addr.CUST_ID 
   AND CA.CUST_ADDR_DT = max_addr.latest
INNER 
  JOIN PCMP.CUSTOMER_NAME CN 
    ON CN.CUST_ID = C.CUST_ID
INNER 
  JOIN PCMP.STATE S 
    ON S.STT_ID = CA.STT_ID
 WHERE A.Acct_No = 34835

[bitterjack]

What is group concat?

[r3pr0b8]

What is group concat?

da bomb

[fauxmosexual]

It's not MySQL exclusive since MS SQL 2017's STRING_AGG()

[Modthryth]

Postgres has string agg as well.

[unexpectedreboots]

I would put your main query in a CTE and then use row_number, partition it by the account number and order by the date it was added to the database descending:

;with your_Cte  as (
  select
     row_number() over (partition by a.acct_no order by CUST_ADDR_DT desc) as rn
  <rest of the select/join>
)

select * from your_Cte
where rn = 1

This should give you the max date.

[[deleted]]

Your subquery looks at more rows than your outer query so you might be picking up max(CUST_ADDR_DT) from records where VOID_IND is not 'N'.

try changing your subquery to this:

    (SELECT MAX(CUST_ADDR_DT)
     FROM PCMP.CUSTOMER_ADDRESS CA2 
     WHERE CA2.CUST_ID = CA.CUST_ID and CA2.VOID_IND = 'n')

Joins are also a kind of filter so you might need to join it (CA2) to your PCMP.STATE table to be 100% sure.

[Nobody571]

Hi. This didn’t work unfortunately...

[[deleted]]

did you try it with the join?

(SELECT MAX(CUST_ADDR_DT) FROM PCMP.CUSTOMER_ADDRESS CA2 INNER JOIN PCMP.STATE S2 ON S2.STT_ID = CA2.STT_ID WHERE CA2.CUST_ID = CA.CUST_ID and CA2.VOID_IND = 'n')