Solved

SQL Server 2008 - Query for a Left Join

Posted on 2015-01-08
23
301 Views
Last Modified: 2015-01-08
Hi Experts,

First I would like to say that I am new to all this.

I have this bit of T-SQL that I would like to run a query using a Left Join but would need some help with:
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window


The problem I am having is that when it comes to the "RELATIONSHIP" Table it causes a doubling of the records and I only want one as a result.

Here is my attempt to correct it:
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN (Select CLM_KY, PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY, PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'SERVICING') T Where Record_Seq = 1)
            LEFT JOIN (Select CLM_KY,PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,CLM_KY,
            PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'BILLING') T Where Record_Seq = 1)
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window


But as you can see I would be getting syntax errors and the error is:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'LEFT'.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


ALSO,
doesn't each Left Join results need to be in some kind of result to determine which one is the newest and give that one a record result?

Please help and thanks
0
Comment
Question by:Amour22015
  • 10
  • 5
  • 4
  • +3
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40537771
I think you should read up this article in regards to "duplicates":
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
with that one, you should be able to implement the query simply and correctly withing a couple of minutes
0
 
LVL 25

Expert Comment

by:SStory
ID: 40537772
Generally to determine newest you sort something by Date order DESC first.  Have you tried the Distinct keyword at the start of the SELECT? Select Distinct .....
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40537776
All of the WHEREs should be at the end, e.g.

Select COUNT(*)
FROM Table1 WITH(NOLOCK)
            LEFT JOIN Table Table1 ON Table1.field = Table2.field
            LEFT JOIN ...
            WHERE Table1.Field = 'HITHERE' and Table2.Field = 'HITHEREASWELL' and...

Open in new window


Personally, I would go back to your original code, and replace lines 4 and 5 with:

            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or bcpr.PRVDR_TYP_CD = 'BILLING')

Open in new window


or, as I said, put all of the WHEREs at the end
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Amour22015
ID: 40537804
Ok,

Phillip Burton I tried:
LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or scpr.PRVDR_TYP_CD = 'BILLING')

Open in new window

But I received the same number of records (doubling), so that did not work?

SStory yes I tried the Select Distinct and again same (doubling) amounts.

Please help and thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40537812
It probably has worked - it's just that one field in one table is mentioned twice in another table.

However, what is unique that you want to count? Is there a primary key field you want to count?

If so, instead of Select COUNT(*), use

Select COUNT(DISTINCT mytable.myfield)
0
 

Author Comment

by:Amour22015
ID: 40537827
The count is just for this post in the original it has this:
SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

Open in new window


But I did not want to put all that in the post.


The result is the same amount of records as the count would be.  I am just trying to make it less confusing but this is the whole query within the SP I am using:
INSERT INTO PURGE_STRIP
            (CLM_KY, [FILE_ID], CLI_PRF_KY, CLM_NR_ID, c.SUBSCR_MBR_KY,PATNT_MBR_KY,GRP_NR,
			CLM_CHRG_AM,CLM_PD_AM,CLM_PRSSD_DT,SUBSCR_FRST_NM, SUBSCR_MID_NM, SUBSCR_LAST_NM,
             CLNT_SUBSCR_ID, FRST_DT_OF_SRVC_DT, LAST_DT_OF_SRVC_DT, PRVDR_ORG_NM, INV_FG,
             PURG_DT, ACCENT_RCPT_DT, BILLG_PRVDR_KY,SERV_PRVDR_KY, HST_FG)      
            SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1
            FROM CLAIM_PURGE c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY
            LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = c.CLM_KY
            WHERE c.FILE_ID = @fileId

Open in new window


Please help and thanks
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 50 total points
ID: 40537902
This is the number of records you want to count?

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

Open in new window


In which case, start with

With myTable as (

Open in new window


and end with

)
Select count(*) from myTable

Open in new window


Making this:

With myTable as (

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

)
Select count(*) from myTable

Open in new window

0
 

Author Comment

by:Amour22015
ID: 40538036
Maybe I am not clear.

So let me say this in a different way.

This table:
CLAIM_2014_09

When I run a count query:
Select COUNT(*) 
FROM CLAIM_2014_09

Open in new window


I get: 1583953 records and that is how many records are in that table.

But when I run this query adding the Left Join in:
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window


I get: 1683779 records and that is more records than there is in the table.

That is the problem I am having.

Please help and thanks
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40538052
But that's because there is either:

1. A duplicate m.MBR_KY, or
2. A duplicate scpr.CLM_KY, or
3. A duplicate bcpr.CLM_KY, or
4. A duplicate p.PRVDR_KY.

That's why you are getting more answers.
0
 

Author Comment

by:Amour22015
ID: 40538096
Ok then I am trying to understand your new query so it would be this:
With CLAIM_2014_09 as (

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

)
Select count(*) from CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or scpr.PRVDR_TYP_CD = 'BILLING')
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window


But I am getting this error:
could not be bound. on all field(s)

Please help and thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40538121
Obviously the data in CLAIM_PROVIDER_RELATIONSHIP are not unique for (PRVDR_TYP_CD, CLM_KY). The brute force method is
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
LEFT JOIN (select distinct PRVDR_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'SERVICING') scpr
       ON scpr.CLM_KY = c.CLM_KY
LEFT JOIN (select distinct PRVDR_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'BILLING') bcpr 
       ON bcpr.CLM_KY = c.CLM_KY 
LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

0
 

Author Comment

by:Amour22015
ID: 40538175
Qlemo,

yes this is what I have been trying to say.

But I also have to keep in mind that the user wants the newest of what comes out of the table "Relationship"

also when I did the updated query I got this as a error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'CLM_KY'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'CLM_KY'.
0
 

Author Comment

by:Amour22015
ID: 40538184
I was told to use the query within the Left Join as well like:
Servicing
Select
      CLM_KY,
      PRVDR_KY
From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'SERVICING')T
Where
      Record_Seq = 1









Billing
Select
      CLM_KY,
      PRVDR_KY
From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'BILLING')T
Where
      Record_Seq = 1

Open in new window


Please help and thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40538192
Sorry, I need to "publish" the join columns from the inner selects, of course:
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
LEFT JOIN (select distinct PRVDR_KY, CLM_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'SERVICING') scpr
       ON scpr.CLM_KY = c.CLM_KY
LEFT JOIN (select distinct PRVDR_KY, CLM_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'BILLING') bcpr 
       ON bcpr.CLM_KY = c.CLM_KY 
LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40538199
Taking your new pieces, those should work if you replace my "(select distinct ...)" with "(select CLM_KY, PRVDR_KY from ...)" exactly as you posted in http:#a40538184.
0
 

Author Comment

by:Amour22015
ID: 40538209
Qlemo

That just makes the execute run and run without a stop.

also keep in mind that the user want the lastest values coming out of "Relationship:" table.  I think that is why there is a "as Record_Seq" within the query above?

Please help and thanks...
0
 

Author Comment

by:Amour22015
ID: 40538240
So do you mean this:
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN (Select CLM_KY, PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY, PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'SERVICING') T Where Record_Seq = 1)
            LEFT JOIN (Select CLM_KY,PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,CLM_KY,
            PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'BILLING') T Where Record_Seq = 1)
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window


That is what I posted in ID: 28592901 above...

And I got errors:
 Msg 156, Level 15, State 1, Line 7
 Incorrect syntax near the keyword 'LEFT'.
 Msg 156, Level 15, State 1, Line 9
 Incorrect syntax near the keyword 'LEFT'.
 Msg 319, Level 15, State 1, Line 9
 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 40538280
Sigh. If you use FROM (select ...) in MSSQL, you need to provide an alias after the closing bracket to identify that result (and all results in the select need to be named if an expression - no issue here).
Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
LEFT JOIN (
  Select
      CLM_KY,
      PRVDR_KY
  From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'SERVICING') T
  Where
      Record_Seq = 1
) scpr
       ON scpr.CLM_KY = c.CLM_KY
LEFT JOIN (
  Select
      CLM_KY,
      PRVDR_KY
  From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'BILLING')T
  Where
      Record_Seq = 1
) bcpr 
       ON bcpr.CLM_KY = c.CLM_KY 
LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 200 total points
ID: 40538286
You don't need ROW_NUMBER() or (obscure) LEFT JOINs -- in fact, you especially don't want LEFT JOINs, as that will still leave you with the "extra rows" problem!

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
                          c.PATNT_MBR_KY,
                          c.GRP_NR,
                          c.CLM_CHRG_AM,
                          c.CLM_PD_AM,
                          c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT,
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                 (SELECT TOP (1) PRVDR_KY
                   FROM CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK)
                   WHERE scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
                   ORDER BY CLM_PRVDR_RLSHP_KY DESC) AS PRVDR_KY,
                  (SELECT TOP (1) PRVDR_KY
                   FROM CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK)
                   WHERE bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
                   ORDER BY CLM_PRVDR_RLSHP_KY DESC) AS PRVDR_KY,
                 1
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            --LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING' --DELETED!!
            --LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING' --DELETED!!

            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY
0
 

Author Comment

by:Amour22015
ID: 40538316
Qlemo,
That seems to be working at least I am now getting the correct number of records.  But how does it know to take the latest record and place that information in the record?

ScottPletcher,
Does that mean I may have problems later? and I should use what you mentioned?

Still confused on how it would take the latest even with the Select Top (1) I know that it takes the latest at that point being the top 1 but the query mentions that twice?

Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40538371
Qlemo's query will work, just less efficiently.

There are two "Top (1)" because you have to do two different lookups:
one for 'SERVICING' and
one for 'BILLING'
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40538412
The subselects you provided select the first row for all CLM_KY, so we have a lot of keys with one match each. Then we just join the specific CLM_KY. I intentionally did not change the formatting to make that visible.
Whether you need the left joins depends on your data. If there is always a relationship defined, a inner join is better. If there might be relations missing, and you still want to get results, you need the outer join.
0
 

Author Closing Comment

by:Amour22015
ID: 40538652
Thank you both for helping me.  I had to give some points too: Phillip Burton after all he tried to help.  Thanks all
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question