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
Solved

SQL Server 2008 - Field counts are different

Posted on 2015-01-15
17
84 Views
Last Modified: 2015-01-21
Hi Experts,

I have this query:
select ch.file_ID, COUNT (*) 
from CLAIM_HISTORY ch
       INNER JOIN 
       (
       SELECT DISTINCT CLM_KY
				FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
				WHERE HST_FG = 1
				) ct
        on ch.CLM_KY = ct.CLM_KY
        LEFT JOIN CLAIM_2014_09 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
            LEFT JOIN MEMBER m WITH(NOLOCK) ON ch.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
group by ch.FILE_ID
Order by ch.FILE_ID

Open in new window


What I come up with:
file_ID      (No column name)
41552      10830
41559      8050
41562      4537
Total:      23417

When I run this query I come up with the needed amount of records in the File_ID and this is correct.


But

When I run this SP and looking at the same part of the SP that I am using the Query above:
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 
                   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,    
                  0
            FROM CLAIM_HISTORY ch WITH(NOLOCK)
            INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY
            LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
            LEFT JOIN MEMBER m WITH(NOLOCK) ON ch.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
            LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = ch.CLM_KY
            

Open in new window

The only difference is that this does an Insert into a table.

FILE_ID      (No column name)
41552      8402
41559      8086
41562      6929
Total:     23417

Notice that I come up with the same total amount of records, it is just that the amount are not correct in the FILE_ID's and this is the problem?

Please help and thanks
0
Comment
Question by:Amour22015
  • 9
  • 7
17 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40551209
The 2 queries are not at all the same, only some of the derived tables that you join in them. In the top query you group by 2 columns while in the second you don't. Also you join some temporary tables in the second while in the first you use permanent tables.

The fact that you expect the same results is irrelevant. You have to compare queries that are at least equivalent logically. From the info you provided it is impossible to actually come up with a solution.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551211
The SELECT's aren't completely the same.
In the 2nd one has a join with a temporary table (LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = ch.CLM_KY).

Plus, in the 1st the following joins:
INNER JOIN
       (SELECT DISTINCT CLM_KY
            FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
            WHERE HST_FG = 1) ct
                  on ch.CLM_KY = ct.CLM_KY
      LEFT JOIN CLAIM_2014_09 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY


was replaced in the 2nd by the following joins:
     INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY
    LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
0
 

Author Comment

by:Amour22015
ID: 40551234
Ok,

I forgot to add the temp table:
First query I have this:
INNER JOIN 
       (
       SELECT DISTINCT CLM_KY
				FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
				WHERE HST_FG = 1
				) ct
        on ch.CLM_KY = ct.CLM_KY

Open in new window


On the Second query I have this:
INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1


AND:

INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY

Open in new window


They are the same only I took out the:
WHERE [FILE_ID] = @fileId and HST_FG = 1

and replaced with:
WHERE HST_FG = 1

Because the first query is already dealing with the:
WHERE [FILE_ID] = @fileId
by using the: File_ID, Count(*)

So they are the same.

Logically they are the same, only one is doing a count(*) and the other is doing a Insert.

All I did was to use the same query from the SP and I took out the insert and replaced it with a count(*), also I took out some of the Left Join's of some temp tables which again do not make any difference.

Please help and thanks
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Amour22015
ID: 40551243
I think there is something missing in the SP query that is causing the amounts to show up in the wrong FILE_ID's otherwise I would not get the same amount of totals?
INNER JOIN 
       (
       SELECT DISTINCT CLM_KY
				FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
				WHERE HST_FG = 1
				) ct
        on ch.CLM_KY = ct.CLM_KY

Open in new window

INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1


AND:

INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY

Open in new window

0
 

Author Comment

by:Amour22015
ID: 40551306
LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY

Claim_Purge is a Index of CLAIM_2014_09

LEFT JOIN CLAIM_2014_09 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY

Both are the same, there is no difference.
There must be something wrong with the second query?

Please help and thanks
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551320
They are the same only I took out the:
 WHERE [FILE_ID] = @fileId and HST_FG = 1

 and replaced with:
 WHERE HST_FG = 1

 Because the first query is already dealing with the:
 WHERE [FILE_ID] = @fileId
 by using the: File_ID, Count(*)
The File_ID aren't from the same table.  One is from PURGE_STRIP and the other one from CLAIM_HISTORY.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551324
LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY

 Claim_Purge is a Index of CLAIM_2014_09
What you an index? You can't join with indexes but tables.
0
 

Author Comment

by:Amour22015
ID: 40551340
Ok,

I put back the temp table

and replaced it with a Left Join query

still the same results

So you all agree that there is a major difference between queries, keeping in mind that the only difference is that in one I am using a Count(*) instead of an Insert and a Index table instead of Claim_2014_09 and (Sql queries) instead of the temp tables?

select ch.file_ID, COUNT (*) 
from CLAIM_HISTORY ch
       INNER JOIN 
       (
       SELECT DISTINCT CLM_KY
				FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
				WHERE HST_FG = 1
				) ct
        on ch.CLM_KY = ct.CLM_KY
        LEFT JOIN CLAIM_2014_09 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
            LEFT JOIN MEMBER m WITH(NOLOCK) ON ch.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
            LEFT JOIN (SELECT DISTINCT POTENTIAL.PRIM_CLM_KY AS CLM_KY
            FROM CLAIM_2014_09 C WITH(NOLOCK)
            JOIN POTENTIAL ON POTENTIAL.PRIM_CLM_KY = C.CLM_KY
            UNION
            SELECT DISTINCT POTENTIAL.SEC_CLM_KY AS CLM_KY
            FROM CLAIM_2014_09 C WITH(NOLOCK)
            JOIN POTENTIAL ON POTENTIAL.SEC_CLM_KY = C.CLM_KY) pc ON pc.CLM_KY = ch.CLM_KY
group by ch.FILE_ID
Order by ch.FILE_ID

Open in new window


And:
INSERT INTO #POTENTIAL_CHECK (CLM_KY)
            SELECT DISTINCT POTENTIAL.PRIM_CLM_KY AS CLM_KY
            FROM CLAIM_PURGE C WITH(NOLOCK)
            JOIN POTENTIAL ON POTENTIAL.PRIM_CLM_KY = C.CLM_KY
            WHERE C.[FILE_ID] = @fileId
            UNION
            SELECT DISTINCT POTENTIAL.SEC_CLM_KY AS CLM_KY
            FROM CLAIM_PURGE C WITH(NOLOCK)
            JOIN POTENTIAL ON POTENTIAL.SEC_CLM_KY = C.CLM_KY
            WHERE C.[FILE_ID] = @fileId


And:
   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 
                   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,    
                  0
            FROM CLAIM_HISTORY ch WITH(NOLOCK)
            INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY
            LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
            LEFT JOIN MEMBER m WITH(NOLOCK) ON ch.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
            LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = ch.CLM_KY

Open in new window


They are both the same, if not show me the difference(s), maybe that is why the second is not correct?

Please help and thanks.
0
 

Author Comment

by:Amour22015
ID: 40551368
Ok,

On the second query.
PURGE_STRIP is a result of Claim_History, I am Inserting from Claim_History into PURGE_STRIP.  So they are the same.  Only in the First query I am selecting out of Claim_History and on the second query I a Inserting into the Purge_Strip table.  

Are you saying the one can not JOIN an index table?  So if the Index was on multiple tables how would the JOIN happen?
Not for now but in the future if I had
Claim_2014_09
Claim_2014_10
Claim_2014_11
in the Index was on all 3 tables, how would you know and JOIN all the 3 tables.  This can not be:
LEFT JOIN Claim_2014_09 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
LEFT JOIN Claim_2014_10 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
LEFT JOIN Claim_2014_11 c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY

First there would be no way of knowing when each table comes up in the DB, they are made after every month


Please help and thanks
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551382
Are you saying the one can not JOIN an index table?  So if the Index was on multiple tables how would the JOIN happen?
It's better you tell us your definition of index because I'm talking about table indexes.
0
 

Author Comment

by:Amour22015
ID: 40551414
I have to agree that I am new to all this.

If this was true:
You can't join with indexes but tables
LEFT JOIN CLAIM_PURGE
and Claim_Purge being an Table index over Claim_2014_09

Then my company would be in major trouble.  Because all the Developers are using:
LEFT JOIN (Table index's)

Basically a Table Index is a pointer to one or more tables.

Maybe that is the problem? in which case then it is time to go back far into the company and change everything.  There are thousands of SP's that are using LEFT JOIN's on a Table Index in my company and others that I have worked for in the past including the government.

Please help and thanks.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551430
Then my company would be in major trouble.  Because all the Developers are using:
 LEFT JOIN (Table index's)

 Basically a Table Index is a pointer to one or more tables.
Maybe it's only a definition problem. Technically do you know how is defined the object that you call Table index? As I see it may be a view or a partitioned table.
0
 

Author Comment

by:Amour22015
ID: 40551450
I am sorry, see this shows I am using the wrong wording.

Claim_Purge is a VIEW over table Claim_2014_09

Index would be an example of:

Before an insert to the table a index (query) that was attached to the table would do some kind of check to the incoming record, maybe for some validation reason.

So now back to the original question?
Please help and thanks
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40553005
Can you post here the execution plans for each query? It's the only way that I can verify what the engine is doing.
0
 

Accepted Solution

by:
Amour22015 earned 0 total points
ID: 40554287
Well, I finally got this correct.

 And it was so easy.

 notice in the query that is having problems:
 --Insert into purge strip table from CLAIM_HISTORY
            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 
                   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,    
                  0
            FROM CLAIM_HISTORY ch WITH(NOLOCK)
            INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY
            LEFT JOIN CLAIM_PURGE c WITH(NOLOCK) ON c.CLM_KY = ch.CLM_KY
            LEFT JOIN MEMBER m WITH(NOLOCK) ON ch.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
            LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = ch.CLM_KY

Open in new window


Notice that the Select of File_ID is written as c.File_ID?
 Then notice that the From table is Claim_History ch?
 So just changing the c.File_ID to ch.File_ID put the correct amount into each group.  Note that the c.File_ID is from a Left Join: Left Join Claim_Purge c

 So that was the problem, strange that no one picked up on this.

 Thanks
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40557165
So that was the problem, strange that no one picked up on this.
Not strange at all. You didn't provide the data so we was working in completely darkness and tried to guess only.
0
 

Author Closing Comment

by:Amour22015
ID: 40561580
Please no points just leave for learning
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

809 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