SQL Server 2008 - Field counts are different

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.

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

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

Select allOpen 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

Select allOpen 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

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

Select allOpen 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

Select allOpen in new window

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

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.

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.

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

Select allOpen 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

Select allOpen 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.

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

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.

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.

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.

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

Can you post here the execution plans for each query? It's the only way that I can verify what the engine is doing.

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.

Please no points just leave for learning