Solved

SQL Server 2008 - Field counts are different

Posted on 2015-01-15
17
81 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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Amour22015
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
Please no points just leave for learning
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Powershell SMO script not working. 18 97
Access Date Query 28 27
SQL JOIN 6 27
Convert int to military time 8 20
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now