Correspondence Address or the most recent address

[wsilage]

I am having issues with one of my joins and I am not sure how to fix this. Maybe it is something simple but not sure. I have a query that need to only pull all states and Addresses. However, if there is a correspondence addresses that needs to be pulled first. If there is no correspondence then the most recent address needs to be pulled. Now this is working with my Case statement below. However I am running into a problem that if there is a different state, then it won’t pull because in my “recent address” join I am only pulling for PA and DE. I did this because I was having issue with If there was a provider in PA it was pulling a different address with a different state. I need the provider address to state in the same state. I am not sure how do to this or if I even explained it correctly. Please let me know if you can help me.

/NEW Most recent Address*/

pcsa.pcsa_id1,pcsa.pcsa_offi,pcsa.pcsa_addr1,pcsa.pcsa_addr2,pcsa.pcsa_city,pcsa.pcsa_state,pcsa.pcsa_zip,

/******** Correspondence Address***********/
Case
When corres.correspondenceAddress <> '' or corres.correspondenceAddress IS not Null or corres.correspondenceAddress <> NULL Then corres.correspondenceAddress
else pcsa.pcsa_addr1
End as [Correspondence Address],

Case
When corres.correspondenceSuite <> '' or corres.correspondenceSuite IS not Null or corres.correspondanceSuite <> NULL Then corres.correspondenceSuite
When pcsa.pcsa_addr1 <> '' or pcsa.pcsa_addr1 is not null or pcsa.pcsa_addr1 <> NULL then '' ----double check this because I am having issues with it just being called pcsa_address1 since I added new logic.
else pcsa.pcsa_addr2
End as [Correspondence Address Suite],

Case
When corres.correspondencecity <> '' or corres.correspondencecity IS not Null or corres.correspondencecity <> NULL Then corres.correspondencecity
else pcsa.pcsa_city
End as [Correspondence City],

Case
When corres.correspondenceState <> '' or corres.correspondenceState IS not Null or corres.correspondenceState <> NULL Then corres.correspondenceState
else pcsa.pcsa_state
End as [Correspondence St],

Case
When corres.correspondenceZIP <> '' or corres.correspondenceZIP IS not Null or corres.correspondenceZIP <> NULL Then Left(corres.correspondenceZIP,5)
else Left(pcsa.pcsa_zip,5)
End as [Correspondence ZIP],

Case
When corres.correspondenceCounty <> '' or corres.correspondenceCounty IS not Null or corres.correspondenceCounty <> NULL Then corres.correspondenceCounty
else pcsa.pcsa_cnty
End as [Correspondence County],

FROM IMPACT.dbo.pcsa AS a

INNER JOIN IMPACT.dbo.pcst AS t with (nolock)
ON a.pcsa_id1 = t.pcst_id1

INNER JOIN IMPACT.dbo.pro AS p with (nolock)
ON a.pcsa_pro = p.pro_id1 and a.pcsa_Stat NOT IN ('NOGOOD')

INNER JOIN IMPACT.dbo.pcs as c with (nolock)
ON a.pcsa_id1 = c.pcs_id1 ------------and c.pcs_ctl <> 'X' (taking out because we are using the prop_ctl field now.)

JOIN impact.dbo.prop o with (nolock)
ON p.pro_id1 = o.prop_id1 --AND o.prop_trm is null
and o.prop_prd <> 'PPO'

left join impact.dbo.tmpHumanaEffectiveDates t1
ON t1.pcsp_id1 = pcst_id1

left join impact.dbo.pcst t5
ON t1.pcsp_id1 = t5.pcst_id1
and t5.PCST_WHAT ='RECRED' and t5.pcst_act2 is NULL ----- Future Date

/Corres LOGIC MOST RECENT ADDRESS**/

left join networkDevelopment.dbo.Vw_CorresAddress ca on a.pcsa_id1 = ca.pcs
CROSS APPLY (SELECT TOP 1
pcsa_id1,pcsa_offi,pcsa_addr1,pcsa_addr2,pcsa_city,pcsa_state,pcsa_zip ,pcsa_cnty
FROM impact.dbo.pcsa
inner join impact.dbo.pcsp on pcsa_id1 = pcsp_id1 and pcsa_id2 = pcsp_id2
WHERE pcsa_id1 = a.pcsa_id1 and pcsa_state IN ('PA','DE') and pcsp_trm is null
ORDER BY pcsa_newdt DESC) pcsa

/-----=========================================================
correspondence – Addresses W
-----=========================================================/

Left join (Select Distinct pcsa_id1 as correspondenceID, a2.pcsa_addr1 as correspondenceAddress, a2.pcsa_addr2 as correspondenceSuite,a2.pcsa_city as correspondenceCity, a2.pcsa_state as correspondenceState,
a2.pcsa_zip as correspondenceZIP,
a2.pcsa_cnty as correspondenceCounty,a2.pcsa_phone as CorrespondencePhone, a2.pcsa_fax as CorrespondenceFax , a2.pcsa_tax1 as CorrespondenceTAX
From impact.dbo.pcsa a2
where pcsa_trst = 'CORRES' and (pcsa_trm is null or pcsa_trm = '' or pcsa_trm = NULL)) corres on a.pcsa_id1 = corres.correspondenceID and a.pcsa_tax1 = corres.CorrespondenceTAX

[jeffw8713]

I am not sure I am following everything - if you can provide sample data (as insert statements) and expected results it would help. It would also help if you could format the code to be more readable...

With that said - try changing the CROSS APPLY to an OUTER APPLY. The CROSS APPLY will work similar to an INNER join and restrict the rows to only those that match.

[Kristen]

If you have a Correspndence Address is it guaranteed to have data in EVERY address column?

Case
When corres.correspondenceSuite <> '' or corres.correspondenceSuite IS not Null or corres.correspondanceSuite <> NULL 
    Then corres.correspondenceSuite
When pcsa.pcsa_addr1 <> '' or pcsa.pcsa_addr1 is not null or pcsa.pcsa_addr1 <> NULL 
    then '' ----double check this because I am having issues with it just being called pcsa_address1 since I added new logic.
    else pcsa.pcsa_addr2
End as [Correspondence Address Suite],

If the correspondence Address doesn't have any data for [correspondenceSuite] (but there is a record which has data for some / all other address fields) then you will get a mix of Correspondence Address and
Recent Address.

I suggest you based your CASE on a PKey column in [pcsa] instead - that will be NULL if there is no correspondence address (if you change CROSS APPLY to OUTER APPLY as @jeffw8713 said.

When you select the [corres] data I would convert any Blanks to NULLs at that point, to save having all that mucking about with CASE statements for Blank-or-NULL

Left join (
    Select Distinct pcsa_id1 as correspondenceID, 

    NullIf(a2.pcsa_addr1, '') as correspondenceAddress,  --<< Like this
    ...
    From impact.dbo.pcsa a2
    ...
) AS corres 
    on a.pcsa_id1 = corres.correspondenceID 
   and a.pcsa_tax1 = corres.CorrespondenceTAX

and then you can just do

COALESCE(corres.correspondenceAddress, pcsa.pcsa_addr1)

when you want one or the other notwithstanding my earlier comment about getting Addr1 from PCSA when CORRES.correspondenceAddress exists but happens to be blank.

You could take the other route of:

Left join (
    Select Distinct pcsa_id1 as correspondenceID, 

    COALESCE(a2.pcsa_addr1, '') as correspondenceAddress,  --<< Like this
    ...
    From impact.dbo.pcsa a2
    ...
) AS corres 
    on a.pcsa_id1 = corres.correspondenceID 
   and a.pcsa_tax1 = corres.CorrespondenceTAX

which would ensure that NO columns in [corres] were NUL (unless the record did not exist at all), and then you COULD just use

COALESCE(corres.correspondenceAddress, pcsa.pcsa_addr1)

provided that your results can show Blank String for columns in [corres] that were NULL

@wsilage

with (nolock)

Hate to see that in code. From time to time your report WILL miss some rows, or include some rows twice. Unless you are happy with that (i.e. the report is not critical) then don't use it.

@wsilage

Left join (Select Distinct ...
) corres
on a.pcsa_id1 = corres.correspondenceID
and a.pcsa_tax1 = corres.CorrespondenceTAX

That DISTINCT is going to be a performance issue, and won't scale well. Much better to rewrite that sub-query so that SQL doesn't have to sort all the results and throw away the duplicates. Using a SELECT TOP 1 like your CROSS/OUTER APPLY (and an ORDER BY so the first one selected is your preferred row) would be more efficient

[wsilage]

Kristen,

Thanks so much I will try this. Also thank you about the nolock as well.