Solved

SQL Server - Amounts are wrong

Posted on 2015-01-15
11
65 Views
Last Modified: 2015-01-21
Hi Experts,

I have this query lets call it COUNT:
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

I get this as a result:
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.
Now I have done a little bit of changing so that it will do a count(*) and also where the temp tables (see below) are being LEFT JOIN I have instead done an SQL Queries.



 But

 When I run this query that comes from an SP and looking at the same part of the SP that I am using as with the Query above.  There are some differences like: instead of the Count(*) I am doing an Insert to a table: PURGE_STRIP, instead of LEFT JOIN on a SQL Query I am using Temp Tables, lets call it INSERT:
INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1



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


QUERY:
   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


 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?

The only difference is that INSERT does an Insert into a table PURGE_STRIP and
COUNT does a count as a result of an execution.


Note that the INSERT query is getting the data from CLAIM_HISTORY, but is being placed into PURGE_STRIP
Note that the COUNT query is getting the data from CLAIM_HISTORY, but is just a query for counting the number of records.
So therefore I should get the same number of records in PURGE_STRIP table as I do the COUNT query?

So the data in both cases is coming from CLAIM_HISTORY table.
And CLAIM_PURGE is a view over CLAIM_2014_09

Please help and thanks
0
Comment
Question by:Amour22015
  • 7
  • 4
11 Comments
 
LVL 12

Expert Comment

by:FarWest
Comment Utility
it is hard for me to follow the code, but what I can say that it is better to check temp tables create statement maybe the problem is due to truncation issue related to column numeric digits, even if self create temp table because original do not consider very large numbers as individual rows
0
 

Author Comment

by:Amour22015
Comment Utility
fryezz - I don't understand your comment?

As for the temp table:

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


Is the same as:
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


And

This:
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
LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = c.CLM_KY

Open in new window


Is the same as:
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)

Open in new window


The only difference is that I take out the:
WHERE C.[FILE_ID] = @fileId
on the COUNT query because it is using that to count by:
FILE_ID, Count(*)
Group by FILE_ID
Order by FILE_ID

and on the INSERT query the values are being passed to @fileID

So what I am saying is that I should have the same number of records in each group - FILE_ID's in PURGE_STRIP as I do in the COUNT query?

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

Open in new window

0
 

Author Comment

by:Amour22015
Comment Utility
I don't know what is going on with the expert post but I did not add the <> after:
Please help and thanks
INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1

Open in new window

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

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
LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = c.CLM_KY

Open in new window

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)

Open in new window

0
 

Author Comment

by:Amour22015
Comment Utility
It did it again? I did not add the coding </> after the:
Please help and thanks
INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1

Open in new window

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

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
LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = c.CLM_KY

Open in new window

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)

Open in new window

0
 
LVL 12

Expert Comment

by:FarWest
Comment Utility
now I understand the problem, it is really very strange behavior, and I need some time to investigate,
did you try to take the part from SP and make a function with @fileid parameter and execute it unchanged with your FILE_ID?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Amour22015
Comment Utility
You can say that again "Really strange behavior"

You mention:
"make a function with @fileid parameter and execute it "

No I have not
I am new to all this
How do you do that?

Please help and thanks
0
 
LVL 12

Expert Comment

by:FarWest
Comment Utility
will you send a text file containing the full SP
and just mark the code that you use from it
0
 

Author Comment

by:Amour22015
Comment Utility
Ok,

Here is the full SP:
ALTER PROCEDURE [dbo].[Purge_By_FileId] 
      @FileId int, -- File_Id from Database_Tables CLAIM
      @TBLNM varchar(40), -- Table Name from Database_Tables
      @rerunFlag bit

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      --Drop temp tables if they already exist from previous attempt
      IF OBJECT_ID('tempdb..#POTENTIAL_CHECK') IS NOT NULL
            DROP TABLE #POTENTIAL_CHECK
      
      IF OBJECT_ID('tempdb..#CLAIM_TEMP') IS NOT NULL
            DROP TABLE #CLAIM_TEMP

      --Create temp table for POTENTIAL records
      CREATE TABLE #POTENTIAL_CHECK (
            CLM_KY bigint
            )

      --Create and populate temp table of CLM_KY
      CREATE TABLE #CLAIM_TEMP (
            CLM_KY bigint
            )


      DECLARE 
            @rc int, 
            @claimCommitCount int = 1000,
            @claimItemCommitCount int = 1000,
            @claimProviderRelationshipCommitCount int = 1000, 
            @paymentCommitCount int = 1000,
            @claimHistoryCommitCount int = 1000, 
            @claimItemHistoryCommitCount int = 1000,
            @claimFacilityDetailHistoryCommitCount int = 1000,
            @claimFacilityDetailCommitCount int = 1000,
            @sql nvarchar(4000)

      
      IF @rerunFlag = 0 --Only save to purge strip if not a re-run
      BEGIN
            -- Save off data to the strip table first
            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

            --Insert into purge strip table from CLAIM
            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 (
  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 = c.CLM_KY
            WHERE c.FILE_ID = @fileId
            
            
            INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1
            
            

            --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
            
            
            
            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)
            LEFT JOIN CLAIM 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
            Where ch.FILE_ID = @fileId and ct.CLM_KY is null

      END
      -- Get ready to delete Claim Table(s)      

      DROP TABLE #POTENTIAL_CHECK

      --CLEAN CLAIM_HISTORY records
      PRINT 'Start Purge of CLAIM_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)
      --Clean 1
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimHistoryCommitCount)
                  FROM CLAIM_HISTORY
                  WHERE CLM_KY in (SELECT #CLAIM_TEMP.CLM_KY
                                     FROM #CLAIM_TEMP
                                     WHERE #CLAIM_TEMP.CLM_KY = CLAIM_HISTORY.CLM_KY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      
      --Clean 2
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimHistoryCommitCount)
                  FROM CLAIM_HISTORY
                  WHERE CLM_HST_KY in (SELECT 
											ch.CLM_HST_KY
										FROM 
											CLAIM_HISTORY ch WITH(NOLOCK)
											LEFT JOIN CLAIM ct on ch.CLM_KY = ct.CLM_KY
										Where 
										ch.FILE_ID = @fileId and 
										ct.CLM_KY is null)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      PRINT 'End Purge of CLAIM_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)

      CHECKPOINT;

      --CLEAN CLAIM_ITEM_HISTORY records
      PRINT 'Start Purge of CLAIM_ITEM_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)
      
      --CLEAN 1
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimItemHistoryCommitCount)
                  FROM CLAIM_ITEM_HISTORY
                  WHERE CLM_KY in (SELECT #CLAIM_TEMP.CLM_KY
                                     FROM #CLAIM_TEMP
                                     WHERE #CLAIM_TEMP.CLM_KY = CLAIM_ITEM_HISTORY.CLM_KY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      
      
      --CLEAN 2
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimItemHistoryCommitCount)
                  FROM CLAIM_ITEM_HISTORY
                  WHERE CLM_ITM_HST_KY in (SELECT 
												ch.CLM_ITM_HST_KY
											FROM 
												CLAIM_ITEM_HISTORY ch WITH(NOLOCK)
												LEFT JOIN CLAIM_ITEM ct on ch.CLM_KY = ct.CLM_KY
											Where 
												ch.FILE_ID = @fileId and 
												ct.CLM_KY is null)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      PRINT 'End Purge of CLAIM_ITEM_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)

      CHECKPOINT;

      --CLEAN CLAIM_FACILITY_DETAIL_HISTORY records
      PRINT 'Start Purge of CLAIM_FACILITY_DETAIL_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)
      
      --CLEAN 1
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimFacilityDetailHistoryCommitCount)
                  FROM CLAIM_FACILITY_DETAIL_HISTORY
                  WHERE CLM_KY in (SELECT #CLAIM_TEMP.CLM_KY
                                     FROM #CLAIM_TEMP
                                     WHERE #CLAIM_TEMP.CLM_KY = CLAIM_FACILITY_DETAIL_HISTORY.CLM_KY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      
      
      --CLEAN 2
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimFacilityDetailHistoryCommitCount)
                  FROM CLAIM_FACILITY_DETAIL_HISTORY
                  WHERE CLM_KY not in (Select  
											CLM_KY 
										From	
											CLAIM
										union
										Select
											CLM_KY
										From
											CLAIM_HISTORY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      
      PRINT 'End Purge of CLAIM_FACILITY_DETAIL_HISTORY - ' + CONVERT(VARCHAR, GETDATE(), 120)

      CHECKPOINT;
      
      
      --CLEAN CLAIM_PROVIDER_RELATIONSHIP records
      PRINT 'Start Purge of CLAIM_PROVIDER_RELATIONSHIP - ' + CONVERT(VARCHAR, GETDATE(), 120)
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@claimProviderRelationshipCommitCount)
                  FROM CLAIM_PROVIDER_RELATIONSHIP
                  WHERE CLM_KY not in (Select  
											CLM_KY 
										From	
											CLAIM
										union
										Select
											CLM_KY
										From
											CLAIM_HISTORY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      PRINT 'End Purge of CLAIM_PROVIDER_RELATIONSHIP - ' + CONVERT(VARCHAR, GETDATE(), 120)

      CHECKPOINT;

      --CLEAN PAYMENT records
      PRINT 'Start Purge of PAYMENT - ' + CONVERT(VARCHAR, GETDATE(), 120)
      SET @rc = 1
      WHILE @rc > 0
      BEGIN
            BEGIN TRAN
                  DELETE TOP(@paymentCommitCount)
                  FROM PAYMENT
                  WHERE CLM_KY not in (Select  
											CLM_KY 
										From	
											CLAIM
										union
										Select
											CLM_KY
										From
											CLAIM_HISTORY)
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN
      END;
      PRINT 'End Purge of PAYMENT  - ' + CONVERT(VARCHAR, GETDATE(), 120)

      CHECKPOINT;
      DROP TABLE #CLAIM_TEMP

END 

Open in new window


Here is the part in question:
   --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


Please help and thanks
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 12

Expert Comment

by:FarWest
Comment Utility
nice job and thanks for you posting,
sorry I just got a hard flu last two days, I could not follow this case,
0
 

Author Closing Comment

by:Amour22015
Comment Utility
Please no points just leave for learning...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 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

18 Experts available now in Live!

Get 1:1 Help Now