Solved

SQL Server 2008 - Query for a Left Join

Posted on 2015-01-08
23
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 70

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 70

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 70

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 70

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 70

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

724 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