insert loop get slower and slower

[padinka]

I have a table that I build monthy that contains 22 million rows. I use a while loop that inserts a month of data at at time which is about 400 thousand rows to 500 thousand rows for each month. Takes about 4 minutes for the first loop and increasingly takes longer with each loop. It takes about 30 minutes for the last loop even it is only 100 thousand rows bigger than the first.

Any tips?

Trisha
padinka@yahoo.com

 

[markros]

Is it possible to avoid WHILE LOOP at all? Can you post your SP to look at and suggest some optimization ideas?

 

[padinka]

Yes, I can post it but it's 2200 rows. That's why I didn't post it. It builds a single data warehouse table from a large number of source tables. I inherited this monster and I've sped it up considerably in that 4 mins to build and insert the first month is a huge improvement. But, now it slows more with each insert.

It builds by date (month) as that is the smallest common denominator among all the source tables. I suspect it is slowing down because the batches are so large. hmmm, maybe I could batch by week. That would reduce the batch size to approx 100,000 rows. Anyone think that would help or is it still way to large?

Trisha

 

[padinka]

Ok, I've taken out the data manipulation and left the main loop inserts.


----------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[DEV_AL_ECL_TEST_PROC] AS

DECLARE @DATE_BEG DATETIME, @DATE_END DATETIME,
@DATE_MID DATETIME, @DATE_NAME VARCHAR(20),
@time char(50), @START_TIME AS DATETIME, @RUN_MONTH AS DATETIME,
@run_time as datetime,
@startdate datetime , @enddate datetime


SET @RUN_MONTH = (SELECT MAX(CAST(DT_BEG AS DATETIME))
FROM AL_ECL_DATES )
set @run_time = getdate()

SET DEADLOCK_PRIORITY 'HIGH'



BEGIN -- MAIN
CREATE TABLE #AL_ECL_STAGING
(AUTH_ID varchar(20), ANALYSISGRP varchar(20), ASSIGNED_MDC_CD varchar(6),
ASSIGNED_MDC_DESC varchar(100), BED_TYPE_CD varchar(8), BEN_CD varchar(10),
BEN_PLAN_CD varchar(15), CAP_LINE_FLG varchar(2), CAPACITY_CD varchar(10),
CASE_ID varchar(10), CAT_CLM_AMT money DEFAULT 0, CAT_MEM_AMT money DEFAULT 0,
CATGY_CD varchar(10), CI_CD varchar(2), CLAIM_STATUS varchar(2),
CLM_ADJUST_CD varchar(20), CLM_ID varchar(30), CLM_LINE_NO varchar(20),
CLM_NO varchar(20), CLM_TYPE_CD varchar(4), COB_AMT money DEFAULT 0,
COIN_AMT money DEFAULT 0, COMP_ID varchar(4), COPAY_AMT money DEFAULT 0,
DED_AMT money DEFAULT 0, DELETECOL varchar(1), DENIED_CLM varchar(1),
DIAG_1_GROUP1_DESC varchar(50), DIAG_1_GROUP2_DESC varchar(50), DIAG_GROUPER varchar(10),
DIAG1_GROUP1_CD varchar(10), DIAG1_GROUP2_CD varchar(10), DISC_AMT money DEFAULT 0,
DISCHRG_STAT_CD varchar(4), DIVISION_ID varchar(12), DOS_DT datetime,
DOS_DT_AGE int, DOS_THRU_DT datetime, DX_1_CD varchar(6),
DX_1_DESC varchar(40), DX_2_CD varchar(6), DX_2_DESC varchar(40),
DX_3_CD varchar(6), DX_3_DESC varchar(40), DX_4_CD varchar(6),
DX_4_DESC varchar(40), DX_5_CD varchar(6), DX_5_DESC varchar(40),
DX_6_CD varchar(6), DX_6_DESC varchar(40), DX_7_CD varchar(6),
DX_7_DESC varchar(40), DX_8_CD varchar(6), DX_8_DESC varchar(40),
DX_9_CD varchar(6), DX_9_DESC varchar(40), ECL_ID uniqueidentifier,
FACILITY_NM varchar(100), FEE_SCHD_ID varchar(10), GEO_REGION varchar(30) DEFAULT 'UNASSIGNED',
GL2_1 varchar(3) DEFAULT NULL, GL2_1_DESC varchar(50), GL2_2 varchar(3) DEFAULT NULL,
GL2_2_DESC varchar(50), GL2_3 varchar(3) DEFAULT NULL, GL2_3_DESC varchar(50),
GL2_ALL varchar(12), GL2_ALL_DESC varchar(50), GRP_ID varchar(15) DEFAULT 'X',
GRP_NM varchar(100) DEFAULT 'UNASSIGNED', HCFA_POS_CD varchar(10), INC_BILLED_AMT money DEFAULT 0,
INC_PAID_AMT money DEFAULT 0, INC_TOT_PAID_AMT money DEFAULT 0, LOB_DESC varchar(50),
LOB_ID varchar(10) DEFAULT 'X', MEM_COUNTY varchar(30) DEFAULT 'Z OTHER', MEM_GENDER_CD varchar(3),
MEM_ID varchar(20), MEM_SOC_SEC_NO varchar(20), MIDMONTH datetime,
MOD_CD varchar(5), ORIG_BEN_PLAN_CD varchar(15), ORIG_GRP_ID varchar(15),
ORIG_HCFA_POS_CD varchar(10), ORIG_LOB_ID varchar(10), ORIG_PCP_ID varchar(20),
ORIG_PORG_ID varchar(10), ORIG_PROV_ID varchar(20), ORIG_SPEC_CD varchar(10),
PAID_AMT money DEFAULT 0, PAID_DT datetime, PAR_FLG varchar(3),
PCP_COUNTY varchar(30), PCP_ID varchar(20) DEFAULT NULL, PCP_NM VARCHAR(100) DEFAULT 'UNASSIGNED',
PCP_LEAD_HOSPITAL varchar(50) DEFAULT 'UNASSIGNED', PCP_POD varchar(50) DEFAULT 'UNASSIGNED',
POPULATIONDATE datetime, PORG_ID varchar(10) DEFAULT 'X',
PORG_DESC varchar(50), PREPAY_AMT money DEFAULT 0, PRICING_DRG_CD varchar(4),
PRICING_DRG_DESC varchar(100), PROCEDURE_GROUP_1 varchar(50), PROCEDURE_GROUP_2 varchar(50),
PROV_ID varchar(20), PROV_NM varchar(100), REF_PROV_ID varchar(16),
REPORTLINE varchar(20), REQ_AMT money DEFAULT 0, REVERSED_FLG varchar(1),
RISK_STRAT varchar(50), SERVICE_PAID_DT datetime, SHORT_GROUP_ID varchar(6),
SOURCE varchar(15), SPEC_CD varchar(10) DEFAULT 'UNASSIGNED', SPEC_DESC varchar(100),
SUBR_ID varchar(20), SUPERCOMP varchar(10), SVC_CD varchar(20),
SVC_DESC varchar(40), SVC_ICD9_CD varchar(10), SVC_QTY int default 0,
TOT_PAID_AMT money DEFAULT 0, US_BILL_TYPE_CD varchar(10), VEND_ID varchar(25),
VEND_TYPE_CD varchar(15), VISIT_NO varchar(10), WTHD_AMT money DEFAULT 0,
PLAN_NAME VARCHAR(15), CATGY_DESC VARCHAR(80), CLASS_CD VARCHAR(10),
CLASS_DESC VARCHAR(80), REF_PROV_NM VARCHAR(100), MEM_ZIP VARCHAR(15),
[DUAL] [varchar](1) NULL CONSTRAINT build2_1a DEFAULT ('N'),
[DUAL_STATUS] [varchar](1) NULL CONSTRAINT build2_2a DEFAULT ('N'),
[ESRD] [varchar](1) NULL CONSTRAINT build2_3a DEFAULT ('N'),
[USS] [varchar](1) NULL CONSTRAINT build2_4a DEFAULT ('N'),
[INST] [varchar](1) NULL CONSTRAINT build2_5a DEFAULT ('N'),
[PROPOSED_PLAN] [varchar](100) NULL CONSTRAINT build2_11a DEFAULT ('N'),
[HOSPICE] [varchar](1) NULL CONSTRAINT build2_6a DEFAULT ('N'),
[TERMED] [varchar](1) NULL CONSTRAINT build2_7a DEFAULT ('N'),
[EXCRISK] [varchar](1) NULL CONSTRAINT build2_8a DEFAULT ('N'),
[PART_D] [varchar](1) NULL CONSTRAINT build2_9a DEFAULT ('N'),
[ECI] [varchar](1) NULL CONSTRAINT build2_10a DEFAULT ('N'),
gl_no varchar(30))
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT


PRINT '-------------------------------------------- TEMP TABLES SECTION ----------------------------------------------'
PRINT GETDATE()
PRINT 'BUILDING TEMP GL TABLE'
SELECT GL2_1_CODE, GL2_1_DESCR, GL2_2_CODE, GL2_2_DESCR, GL2_3_CODE, GL2_3_DESCR
INTO #AL_R_CUST_WD_CLAIM_LINE_GL
FROM DEV.DBO.R_CUST_WD_CLAIM_LINE_GL


---Find All Rehab Auths for AL LOBS---
select b.Member_Number,b.Members_LOB,a.precert_id,b.Authorization_Number,a.procedure_from_date,
a.procedure_to_date,a.procedure_decision,a.procedure_bed_type,
a.decision_reason,b.Referring_Physician,b.Primary_Code_Provider_Code
INTO #AL_REHAB_AUTHS

from HSSQL2.STAGING.dbo.precert4 a, HSSQL2.STAGING.dbo.AUTHORIZATION1 b

where a.precert_id = b.Authorization_Number
and b.Members_LOB in (select lobcode from lineofbusinessdim where operationalmarket = 'al' and [grouping] = 'm')
and a.procedure_bed_type in ('19')
and procedure_decision = 'a'


select providerid, min(sourcedatakey) sourcedatakey
into #firstprovider
from providerdim
group by providerid

select p.providerid, fullname, firstname, lastname
into #providerdim
from providerdim p
join #firstprovider f
on p.providerid = f.providerid
and p.sourcedatakey = f.sourcedatakey
group by p.providerid, fullname, firstname, lastname

drop table #firstprovider

PRINT 'BUILDING TEMP SPEC TABLE'
SELECT GL2_2_ASSIGNMENT, GL2_3, SPEC_CD, GL2_3_FOR_PCP, DATA_ELEMENT, herbs_desc
INTO #AL_SPECIALTY
FROM DEV.DBO.R_CUST_SPECIALTY

PRINT 'BUILDING TEMP NDC TABLE'
SELECT MI_NDC, MI_CATGY_CODE, MI_CATGY_DESC, MI_CLASS_CODE, MI_CLASS_DESC
INTO #AL_GROUPER
FROM RX_NDC_GROUPER

PRINT 'BUILDING TEMP ENROLLMENT TABLE'
SELECT ELIG_EFF_DT, ELIG_EXP_DT, MEM_GENDER_CD, MEM_COUNTY, PCP_COUNTY, MEM_ID, LOB_ID, COMP_ID, GRP_ID, REL_CD, MEM_SOC_SEC_NO,
MEM_ZIP, SUPERCOMP, LOB_DESC
INTO #AL_ENROLLMENT2
FROM AL_ENROLLMENT

PRINT '#WD_CLAIM_LINE_GL'
PRINT GETDATE()
SELECT GL2_3_CODE, GL2_1_CODE, GL2_2_CODE, GL2_1_DESCR, GL2_2_DESCR, GL2_3_DESCR
INTO #WD_CLAIM_LINE_GL
FROM dev.dbo.R_CUST_WD_CLAIM_LINE_GL

PRINT 'MEM COUNTY AND ZIP SECTION AND MAX ENROLLMENT INFO SECTION'
SELECT MEM_ID, MAX(ELIG_EFF_DT)MAX_ELIG
INTO #MAX_DT
FROM #AL_ENROLLMENT2
GROUP BY MEM_ID

SELECT T1.MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, LOB_DESC
INTO #MAX_AL_ENROLLMENT
FROM #AL_ENROLLMENT2 T1, #MAX_DT T2
WHERE T1.MEM_ID = T2.MEM_ID
AND T1.ELIG_EFF_DT = T2.MAX_ELIG
GROUP BY T1.MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, LOB_DESC

PRINT 'CREATING TEMP CAP_GL_CODES TABLE'
SELECT GL2_1_CODE,GL2_2_CODE,GL2_3_CODE,GL2_1_DESCR,GL2_2_DESCR,GL2_3_DESCR,GL2_ALL,GL2_ALL_DESCR,CAPTYPE
INTO #AL_R_CUST_ENH_CAP_GL_CODES
FROM R_CUST_ENH_CAP_GL_CODES


PRINT 'CREATING TEMP R_PROVIDER FILE'
SELECT DISTINCT PROV_ID MHC_PROV_ID, PRIM_SPEC_CD MHC_SPEC_CD
INTO #AL_PROVIDER
FROM EIS.DBO.R_PROVIDER

PRINT 'CREATING TEMP R_CUST_ICD9_TO_MDC_DISTINCT'
SELECT ICD9_DEC_CD, ICD9_DESC
INTO #AL_R_CUST_ICD9_TO_MDC_DISTINCT
FROM EIS.DBO.R_ICD9_TO_MDC_DISTINCT

CREATE INDEX ICD9_INDEX ON #AL_R_CUST_ICD9_TO_MDC_DISTINCT
(ICD9_DEC_CD)

--PRINT 'BHAM_GROUP'
SELECT LEFT(GRP_ID,20)AL_GRP_ID, LEFT(GRP_NM,100)GRP_NM_MSTR
INTO #BHAM_GROUP
FROM BHAM_GROUP
GROUP BY LEFT(GRP_ID,20), LEFT(GRP_NM,100)

PRINT 'AL_MM'
SELECT LOB_ID, COMP_ID, SUPERCOMP, BEN_PLAN_CD, MEM_ID, MIDMONTH,
PCP_LEAD_HOSPITAL, MEM_COUNTY, PCP_COUNTY, PCP_ID, GRP_ID,
GEO_REGION, RISK_STRAT, LOB_DESC, MEM_SOC_SEC_NO, GRP_NM,
PLAN_NAME, PORG_ID, PCP_POD, MEM_ZIP, PCP_NM,
DUAL ,DUAL_STATUS ,ESRD ,USS ,INST ,PROPOSED_PLAN ,HOSPICE ,
TERMED ,EXCRISK ,PART_D ,ECI
INTO #AL_MM_ECL
FROM AL_MM



PRINT 'PROVIDER NAME TABLES'
SELECT DISTINCT pcp_id PROV_ID, pcp_nm PROV_NM
INTO #MSTR_PROV_NM
FROM #AL_MM_ECL
where pcp_id is not null

PRINT 'PROVIDER NAME TABLES'
SELECT PROV_ID, PROV_LAST_NM + ', ' + PROV_FIRST_NM AS PROV_NM
INTO #MSTR_PROV_NM2
FROM EIS.DBO.R_PROVIDER

PRINT '#AL_R_CUST_DX_HS_CODING'
select *
INTO #AL_R_CUST_DX_HS_CODING
from DEV.DBO.R_CUST_DX_HS_CODING

PRINT '#AL_R_CUST_HCPCS_PROCEDURE_GROUPER'
select *
INTO #AL_R_CUST_HCPCS_PROCEDURE_GROUPER
from DEV.dbo.R_CUST_HCPCS_PROCEDURE_GROUPER

PRINT '#AL_R_CUST_ENH_LOB'
select *
INTO #AL_R_CUST_ENH_LOB
FROM EIS.DBO.R_CUST_ENH_LOB

PRINT '#AL_R_CUST_DIAG_TO_MDC'
SELECT ASSIGNED_MDC_CD, ICD9_DEC_CD
INTO #AL_R_CUST_DIAG_TO_MDC
FROM DEV.DBO.R_CUST_DIAG_TO_MDC

PRINT '#AL_R_CUST_ASSIGNED_MDC_FULL_DESC'
SELECT ASSIGNED_MDC_DESC, ASSIGNED_MDC_CD
INTO #AL_R_CUST_ASSIGNED_MDC_FULL_DESC
FROM DEV.DBO.R_CUST_ASSIGNED_MDC_FULL_DESC

PRINT '#GRP_LOB'
SELECT GRP_ID, LOB_ID, LOB_DESC, COMP_ID, SUPERCOMP
INTO #GRP_LOB
FROM #AL_MM_ECL
GROUP BY GRP_ID, LOB_ID, LOB_DESC, COMP_ID, SUPERCOMP

PRINT '#AL_R_CUST_DRG_WEIGHTS_2009'
SELECT *
INTO #AL_R_CUST_DRG_WEIGHTS_2009
FROM EIS.dbo.R_CUST_DRG_WEIGHTS_2009

PRINT '#AL_R_CUST_GRP_ID_XWALK_AL_POST_06'
select *
INTO #AL_R_CUST_GRP_ID_XWALK_AL_POST_06
FROM EIS.dbo.R_CUST_GRP_ID_XWALK_AL_POST_06

PRINT '#AL_R_POS_ORIG'
select *
INTO #AL_R_POS_ORIG
from eis.dbo.R_POS_ORIG

PRINT 'TEMP PCP_POD'
SELECT UPIN, POD_NM, POD_EFF_DT, POD_EXP_DT
INTO #AL_R_CUST_PCP_POD
FROM bham.dbo.R_CUST_PCP_POD

PRINT 'PCP PER MEMBER NOT CURRECNTLY ENROLLED'
SELECT MEM_ID, ELIG_EFF_DT, PCP_ID
INTO #AL_ENROLLMENT2_TMP
FROM BHAM.DBO.AL_ENROLLMENT

SELECT MEM_ID, MAX(ELIG_EFF_DT)MAX_DT, CONVERT(VARCHAR(30),NULL)PCP_ID
INTO #MEM_MAX_DT_PCP
FROM #AL_ENROLLMENT2_TMP
GROUP BY MEM_ID

UPDATE T1
SET T1.PCP_ID = T2.PCP_ID
FROM #MEM_MAX_DT_PCP T1, #AL_ENROLLMENT2_TMP T2
WHERE T1.MEM_ID = T2.MEM_ID
AND MAX_DT = ELIG_EFF_DT

DROP TABLE #AL_ENROLLMENT2_TMP


PRINT 'TEMP MEM_ID TO SOCIAL'
SELECT MEM_ID, MEM_SOC_SEC_NO
INTO #AL_SOCIAL
FROM AL_ENROLLMENT
WHERE MEM_SOC_SEC_NO IS NOT NULL
GROUP BY MEM_ID, MEM_SOC_SEC_NO

PRINT 'TEMP ENROL LOB TABLE'
SELECT MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, GRP_ID, LOB_DESC, MAX(ELIG_EFF_DT)MAX_ELIG_DT
INTO #ENROLL_LOB
FROM AL_ENROLLMENT
GROUP BY MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, GRP_ID, LOB_DESC
-------------------------------------------------------------------------------------------------------------------

TRUNCATE TABLE DEV_AL_ECL_TEST


CREATE INDEX ECL1_INDEX ON #AL_ECL_STAGING
(SOURCE)
CREATE INDEX ECL2_INDEX ON #AL_ECL_STAGING
(ORIG_GRP_ID)
CREATE INDEX ECL3_INDEX ON #AL_ECL_STAGING
(ORIG_LOB_ID)
CREATE INDEX ECL4_INDEX ON #AL_ECL_STAGING
(SVC_CD)
CREATE INDEX ECL5_INDEX ON #AL_ECL_STAGING
(MEM_ID)
CREATE INDEX ECL6_INDEX ON #AL_ECL_STAGING
(ORIG_SPEC_CD)
CREATE INDEX ECL7_INDEX ON #AL_ECL_STAGING
(ORIG_HCFA_POS_CD)
CREATE INDEX ECL8_INDEX ON #AL_ECL_STAGING
(CLM_NO)
CREATE INDEX ECL9_INDEX ON #AL_ECL_STAGING
(GRP_ID)



-- BEGIN BUILDING LOOP
while @startdate < = @enddate
BEGIN

BEGIN TRANSACTION

SELECT @DATE_BEG = DT_BEG, @DATE_END = DT_END, @DATE_MID = DT_MID, @DATE_NAME = DT_NAME
FROM AL_ECL_DATES
WHERE DT_BEG = @STARTDATE

PRINT '--------------------------- NEW MONTH ------------------------------'
PRINT @DATE_NAME
PRINT GETDATE()

SET @time= CAST( @DATE_MID AS VARCHAR(10))+ ' AT ' + convert (varchar(30), getdate(), 8)
RAISERROR ('STARTING MONTH ', 0, 1, @time) WITH NOWAIT
SET @START_TIME = GETDATE()


PRINT 'CLAIMS INSERT INTO DEV_AL_ECL_TEST'
INSERT INTO #AL_ECL_STAGING
(AUTH_ID, ECL_ID, CLM_ID, VEND_TYPE_CD, CLM_ADJUST_CD, SUBR_ID, ORIG_PCP_ID, ORIG_BEN_PLAN_CD, ORIG_HCFA_POS_CD, CAPACITY_CD, ORIG_PORG_ID,
ORIG_GRP_ID, ORIG_PROV_ID, MOD_CD, CLM_NO, CLM_LINE_NO, CASE_ID, VISIT_NO, VEND_ID, MEM_ID, ORIG_SPEC_CD, BEN_CD, BED_TYPE_CD,
PAR_FLG, DX_1_CD, DX_2_CD, DX_3_CD, DX_4_CD, DX_5_CD, DOS_DT, DOS_THRU_DT, SVC_CD, CAP_LINE_FLG, SVC_QTY, FEE_SCHD_ID, REQ_AMT,
COPAY_AMT, COIN_AMT, DED_AMT, WTHD_AMT, COB_AMT, PAID_AMT, PAID_DT, PREPAY_AMT, SOURCE, POPULATIONDATE, SHORT_GROUP_ID,
DIVISION_ID, MIDMONTH, DIAG1_GROUP1_CD, DIAG_1_GROUP1_DESC, DIAG1_GROUP2_CD, DIAG_1_GROUP2_DESC, CATGY_CD, DOS_DT_AGE, ORIG_LOB_ID,
COMP_ID, SUPERCOMP, MEM_GENDER_CD, PRICING_DRG_CD, CLM_TYPE_CD, SVC_ICD9_CD, US_BILL_TYPE_CD, DISCHRG_STAT_CD, SERVICE_PAID_DT,
REF_PROV_ID, DIAG_GROUPER, DX_1_DESC, DX_2_DESC, DX_3_DESC, DX_4_DESC, DX_5_DESC, SVC_DESC, DX_6_CD, DX_7_CD, DX_8_CD, DX_9_CD,
REPORTLINE, GRP_NM, CI_CD, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, CAT_MEM_AMT, CAT_CLM_AMT, RISK_STRAT,
PORG_ID, PROV_NM, REF_PROV_NM, CATGY_DESC, CLASS_DESC, gl_no)
SELECT AUTH_ID, NEWID(), LEFT(CLM_ID,30), VEND_TYPE_CD, CLM_ADJUST_CD, LEFT(SUBR_ID,20), LEFT(PCP_ID,20), BEN_PLAN_CD, HCFA_POS_CD, CAPACITY_CD,
PORG_ID, LEFT(GRP_ID,10), LEFT(PROV_ID,20), MOD_CD, LEFT(CLM_NO,20), CLM_LINE_NO, CASE_ID, VISIT_NO, LEFT(VEND_ID,25),
LEFT(MEM_ID,20), SPEC_CD, BEN_CD, BED_TYPE_CD, PAR_FLG, DX_1_CD, DX_2_CD, DX_3_CD, DX_4_CD, DX_5_CD, DOS_DT, DOS_THRU_DT, SVC_CD,
CAP_LINE_FLG, SVC_QTY, LEFT(FEE_SCHD_ID,10), REQ_AMT, COPAY_AMT, COIN_AMT, DED_AMT, WTHD_AMT, COB_AMT, PAID_AMT, PAID_DT,
PREPAY_AMT, 'CLAIMS', GETDATE(), NULL, LEFT(GRP_ID,15), @DATE_MID, 'CLAIMS', 'CLAIMS', 'CLAIMS', 'CLAIMS', 'CLAIMS',
DATEDIFF(YEAR, DOB_DT, DOS_DT), LOB_ID, COMP_ID, 'UNASSIGNED', MEM_GENDER_CD, LEFT(PRICING_DRG_CD,3), CLM_TYPE_CD, SVC_ICD9_CD,
US_BILL_TYPE_CD, DISCHRG_STAT_CD, SERVICE_PAID_DT, LEFT(REF_PROV_ID,16), DIAG_GROUPER, DX_1_DESC, DX_2_DESC, DX_3_DESC, DX_4_DESC,
DX_5_DESC, SVC_DESC, DX_6_CD, DX_7_CD, DX_8_CD, DX_9_CD, '4 CLAIMSINCR', 'UNASSIGNED', CI_CD, 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED',
'UNASSIGNED', 'UNASSIGNED', CAT_MEM_AMT, CAT_CLM_AMT, 'UNASSIGNED', PORG_ID, 'UNASSIGNED', 'UNASSIGNED', 'CLAIMS', 'CLAIMS', gl_no
FROM AL_SOURCE_CLAIMS
WHERE DOS_DT BETWEEN @STARTDATE AND @ENDDATE






---------------------------------------- CAP INSERT ------------------------------------------------
PRINT 'CAP INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(ECL_ID, SOURCE, MEM_ID, SUBR_ID, DOS_DT, DOS_THRU_DT, PAID_DT, VEND_ID, ORIG_PROV_ID, SVC_CD, BEN_CD, REQ_AMT, PAID_AMT,
ORIG_GRP_ID, ORIG_PCP_ID, ORIG_PORG_ID, SVC_QTY, POPULATIONDATE, CAP_LINE_FLG, FEE_SCHD_ID, MIDMONTH, INC_BILLED_AMT, PROV_ID,
INC_TOT_PAID_AMT, INC_PAID_AMT, ORIG_LOB_ID, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG_1_GROUP1_DESC, DIAG1_GROUP2_CD,
DIAG_1_GROUP2_DESC, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, CATGY_CD, ORIG_BEN_PLAN_CD, COMP_ID, MEM_GENDER_CD, SUPERCOMP, DX_1_CD,
REPORTLINE, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PORG_ID, PROV_NM,
REF_PROV_NM, CATGY_DESC, CLASS_DESC, VEND_TYPE_CD, PRICING_DRG_CD, PCP_POD)
SELECT NEWID(), 'CAP', MEM_ID, MEM_ID, CAP_MO, CAP_MO, CAP_MO, CAP_VENDOR, CAP_VENDOR, LEFT(CAP_TYPE,20), LEFT(CAP_TYPE,20), PAID_AMT,
PAID_AMT, LEFT(GRP_ID,10), PCP_ID, LEFT(CAP_MEM_REG,10), 1, GETDATE(), 'Y', 'CAPITATION', @DATE_MID, PAID_AMT, 'CAP', PAID_AMT,
PAID_AMT, LOB, 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', '5 CAPITATION', 'CAP',
'CAP', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'CAP', LEFT(CAP_MEM_REG,10), 'UNASSIGNED', 'UNASSIGNED', 'CAP', 'CAP',
'CAP', 'CAP', 'UNASSIGNED'
FROM AL_SOURCE_CAPDNLD
WHERE CAP_MO BETWEEN @DATE_BEG AND @DATE_END

--------------------------------------------- RX INSERT -----------------------------------------------
PRINT 'RX INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(ECL_ID, SOURCE, CLM_ID, CLM_NO, PROV_ID, DOS_DT, DOS_THRU_DT, COPAY_AMT, REQ_AMT, PAID_AMT, PAID_DT, ORIG_BEN_PLAN_CD, MEM_ID,
SVC_QTY, SVC_CD, VEND_ID, ORIG_LOB_ID, ORIG_PORG_ID, ORIG_GRP_ID, ORIG_PCP_ID, BEN_CD, VEND_TYPE_CD, FEE_SCHD_ID, SUBR_ID,
CAP_LINE_FLG, POPULATIONDATE, MIDMONTH, CATGY_CD, SVC_ICD9_CD, INC_TOT_PAID_AMT, TOT_PAID_AMT, GL2_1, GL2_1_DESC, GL2_2,
GL2_2_DESC, GL2_3, INC_PAID_AMT, COMP_ID, SUPERCOMP, REPORTLINE, ANALYSISGRP, HCFA_POS_CD, SPEC_CD, GEO_REGION,
PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PORG_ID, PROV_NM, REF_PROV_NM, CATGY_DESC, CLASS_DESC, ASSIGNED_MDC_CD,
ASSIGNED_MDC_DESC, DX_1_CD, GL2_3_DESC, MEM_GENDER_CD, PRICING_DRG_CD, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC, DIAG_GROUPER,
DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, INC_BILLED_AMT, PCP_POD)
SELECT NEWID(), 'RX', MI_CLAIM_ID, LEFT(MI_CLAIM_ID,20), 'RX', MI_FILL_DT, MI_FILL_DT, MI_ACTUAL_COPAY_AMT, REQ_AMT, MI_ACTUAL_PAY_AMT,
MI_FILL_DT, MI_BENEFIT_CD, MEM_ID, MI_QTY_SUPPLY, MI_NDC, 'PBM', LOB_ID, PORG_ID, LEFT(GRP_ID,10), PCP_ID, MI_BENEFIT_CD, 'RX',
'RX', LEFT(MEM_ID,9), 'N', GETDATE(), @DATE_MID, MI_CATGY_CD, DRUG_TYPE, (MI_ACTUAL_PAY_AMT+MI_ACTUAL_COPAY_AMT),
(MI_ACTUAL_PAY_AMT+MI_ACTUAL_COPAY_AMT), '500', 'PBM DRUGS', '520', 'PBM GENERIC', 'ESI', MI_ACTUAL_PAY_AMT, 'RX', 'RX',
'6 DRUGCLAIMS', 'G', 'RX', 'RX', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'RX', LEFT(PORG_ID,10), 'UNASSIGNED',
'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', PART_B_FLG, 'RX', 'RX', 'ESI', 'RX', 'RX', 'RX', 'RX', 'RX', 'RX', 'RX', MI_ACTUAL_PAY_AMT,
'UNASSIGNED'
FROM AL_SOURCE_RX
WHERE MI_FILL_DT BETWEEN @DATE_BEG AND @DATE_END

--------------------------------------------- REV INSERT -----------------------------------------------
PRINT 'REVENUE INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(SOURCE, MEM_ID, DOS_DT, PAID_AMT, ORIG_GRP_ID, ORIG_PCP_ID, PORG_ID, ECL_ID, MIDMONTH, PAID_DT, SUBR_ID, INC_BILLED_AMT,
INC_TOT_PAID_AMT, INC_PAID_AMT, CAP_LINE_FLG, GL2_1, GL2_2, GL2_3, GL2_1_DESC, GL2_2_DESC, GL2_3_DESC, TOT_PAID_AMT, ORIG_LOB_ID,
FEE_SCHD_ID, ORIG_HCFA_POS_CD, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, MEM_GENDER_CD, POPULATIONDATE, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC,
DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DX_1_CD, COMP_ID, SUPERCOMP, PROV_ID, MEM_SOC_SEC_NO, REPORTLINE,
ANALYSISGRP, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PROV_NM, REF_PROV_NM,
CATGY_DESC, CLASS_DESC, CATGY_CD, VEND_ID, VEND_TYPE_CD, PRICING_DRG_CD, DOS_THRU_DT, REQ_AMT, SVC_CD)
SELECT 'REV', REV_MEM_ID, REV_BILL_MO, REV_RATE, LEFT(REV_GROUP,10), REV_PCP, 'REV', NEWID(), @DATE_MID, REV_BILL_MO, LEFT(REV_MEM_ID,9),
REV_RATE, REV_RATE, REV_RATE, 'N', '002', '002', '002', 'REV', 'REV', 'REV', REV_RATE, REV_LOB, 'REV', 'REV', 'REV', 'REV', 'REV',
GETDATE(), 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', MEM_SOC_SEC_NO, '3 MONREVENUE', 'REVENUE', 'REV', 'REV',
'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'REV', 'UNASSIGNED', 'UNASSIGNED', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV',
REV_BILL_MO, REV_RATE, REV_FILLER1
FROM AL_SOURCE_REVENUE
WHERE REV_BILL_MO BETWEEN @DATE_BEG AND @DATE_END



/* ECL LOGIC */
-- WHOLE BUNCH OF UPDATE STATEMENTS WERE HERE


PRINT 'INSERTING STAGING DATA INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO DEV_AL_ECL_TEST
(AUTH_ID, ANALYSISGRP, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, BED_TYPE_CD, BEN_CD, BEN_PLAN_CD
, CAP_LINE_FLG, CAPACITY_CD, CASE_ID, CAT_CLM_AMT, CAT_MEM_AMT, CATGY_CD, CATGY_DESC, CLASS_DESC
, CI_CD, CLAIM_STATUS, CLM_ADJUST_CD, CLM_ID, CLM_LINE_NO, CLM_NO, CLM_TYPE_CD, COB_AMT, COIN_AMT
, COMP_ID, COPAY_AMT, DED_AMT, DELETECOL, DENIED_CLM, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC
, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DISC_AMT, DISCHRG_STAT_CD, DIVISION_ID
, DOS_DT, DOS_DT_AGE, DOS_THRU_DT, DX_1_CD, DX_1_DESC, DX_2_CD, DX_2_DESC, DX_3_CD, DX_3_DESC
, DX_4_CD, DX_4_DESC, DX_5_CD, DX_5_DESC, DX_6_CD, DX_6_DESC, DX_7_CD, DX_7_DESC, DX_8_CD
, DX_8_DESC, DX_9_CD, DX_9_DESC, ECL_ID, FACILITY_NM, FEE_SCHD_ID, GEO_REGION, GL2_1, GL2_1_DESC
, GL2_2, GL2_2_DESC, GL2_3, GL2_3_DESC, GL2_ALL, GL2_ALL_DESC, GRP_ID, GRP_NM, HCFA_POS_CD, INC_BILLED_AMT
, INC_PAID_AMT, INC_TOT_PAID_AMT, LOB_DESC, LOB_ID, MEM_COUNTY, MEM_GENDER_CD, MEM_ID, MEM_SOC_SEC_NO
, MIDMONTH, MOD_CD, ORIG_BEN_PLAN_CD, ORIG_GRP_ID, ORIG_HCFA_POS_CD, ORIG_LOB_ID, ORIG_PCP_ID
, ORIG_PORG_ID, ORIG_PROV_ID, ORIG_SPEC_CD, PAID_AMT, PAID_DT, PAR_FLG, PCP_COUNTY, PCP_ID
, PCP_LEAD_HOSPITAL, PCP_POD, PLAN_NAME, POPULATIONDATE, PORG_ID, PORG_DESC, PREPAY_AMT
, PRICING_DRG_CD, PRICING_DRG_DESC, PROCEDURE_GROUP_1, PROCEDURE_GROUP_2, PROV_ID, PROV_NM
, REF_PROV_ID, REF_PROV_NM, REPORTLINE, REQ_AMT, REVERSED_FLG, RISK_STRAT, SERVICE_PAID_DT
, SHORT_GROUP_ID, SOURCE, SPEC_CD, SPEC_DESC, SUBR_ID, SUPERCOMP, SVC_CD, SVC_DESC, SVC_ICD9_CD
, SVC_QTY, TOT_PAID_AMT, US_BILL_TYPE_CD, VEND_ID, VEND_TYPE_CD, VISIT_NO, WTHD_AMT, MEM_ZIP
, PCP_NM, DUAL, DUAL_STATUS, ESRD, USS, INST, PROPOSED_PLAN, HOSPICE, TERMED, EXCRISK, PART_D, ECI)
SELECT AUTH_ID, ANALYSISGRP, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, BED_TYPE_CD, BEN_CD, BEN_PLAN_CD
, CAP_LINE_FLG, CAPACITY_CD, CASE_ID, CAT_CLM_AMT, CAT_MEM_AMT, CATGY_CD, CATGY_DESC, CLASS_DESC
, CI_CD, CLAIM_STATUS, CLM_ADJUST_CD, CLM_ID, CLM_LINE_NO, CLM_NO, CLM_TYPE_CD, COB_AMT, COIN_AMT
, COMP_ID, COPAY_AMT, DED_AMT, DELETECOL, DENIED_CLM, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC
, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DISC_AMT, DISCHRG_STAT_CD, DIVISION_ID
, DOS_DT, DOS_DT_AGE, DOS_THRU_DT, DX_1_CD, DX_1_DESC, DX_2_CD, DX_2_DESC, DX_3_CD, DX_3_DESC
, DX_4_CD, DX_4_DESC, DX_5_CD, DX_5_DESC, DX_6_CD, DX_6_DESC, DX_7_CD, DX_7_DESC, DX_8_CD
, DX_8_DESC, DX_9_CD, DX_9_DESC, ECL_ID, FACILITY_NM, FEE_SCHD_ID, GEO_REGION, GL2_1, GL2_1_DESC
, GL2_2, GL2_2_DESC, GL2_3, GL2_3_DESC, GL2_ALL, GL2_ALL_DESC, GRP_ID, GRP_NM, HCFA_POS_CD, INC_BILLED_AMT
, INC_PAID_AMT, INC_TOT_PAID_AMT, LOB_DESC, LOB_ID, MEM_COUNTY, MEM_GENDER_CD, MEM_ID, MEM_SOC_SEC_NO
, MIDMONTH, MOD_CD, ORIG_BEN_PLAN_CD, ORIG_GRP_ID, ORIG_HCFA_POS_CD, ORIG_LOB_ID, ORIG_PCP_ID
, ORIG_PORG_ID, ORIG_PROV_ID, ORIG_SPEC_CD, PAID_AMT, PAID_DT, PAR_FLG, PCP_COUNTY, PCP_ID
, PCP_LEAD_HOSPITAL, PCP_POD, PLAN_NAME, POPULATIONDATE, PORG_ID, PORG_DESC, PREPAY_AMT
, PRICING_DRG_CD, PRICING_DRG_DESC, PROCEDURE_GROUP_1, PROCEDURE_GROUP_2, PROV_ID, PROV_NM
, REF_PROV_ID, REF_PROV_NM, REPORTLINE, REQ_AMT, REVERSED_FLG, RISK_STRAT, SERVICE_PAID_DT
, SHORT_GROUP_ID, SOURCE, SPEC_CD, SPEC_DESC, SUBR_ID, SUPERCOMP, SVC_CD, SVC_DESC, SVC_ICD9_CD
, SVC_QTY, TOT_PAID_AMT, US_BILL_TYPE_CD, VEND_ID, VEND_TYPE_CD, VISIT_NO, WTHD_AMT, MEM_ZIP
, PCP_NM, isnull(DUAL, 'N'), DUAL_STATUS, ESRD, USS, INST, PROPOSED_PLAN, HOSPICE, TERMED, EXCRISK, PART_D, ECI
FROM #AL_ECL_STAGING

PRINT 'CLEANUP BEFORE NEXT LOOP'

COMMIT
TRUNCATE TABLE #AL_ECL_STAGING


SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT


INSERT INTO build_RUN_TIME
SELECT @DATE_MID, DATEDIFF(MINUTE, @START_TIME, GETDATE()), 'DEV_AL_ECL_TEST', @RUN_MONTH, @run_time, getdate(), current_user
FETCH NEXT FROM ECL_CURS INTO @DATE_BEG,@DATE_END,@DATE_MID,@DATE_NAME



set @startdate = dateadd(month, 1, @startdate)

end

DROP TABLE #AL_R_CUST_WD_CLAIM_LINE_GL
DROP TABLE #AL_SPECIALTY
DROP TABLE #AL_GROUPER
DROP TABLE #AL_ENROLLMENT2
--DROP TABLE #AL_R_CUST_APCS_HCPCS_CODING
DROP TABLE #AL_R_CUST_ENH_CAP_GL_CODES
DROP TABLE #AL_PROVIDER
DROP TABLE #AL_R_CUST_ICD9_TO_MDC_DISTINCT
--DROP TABLE #MEM_DOB

DROP TABLE #AL_MM_ECL
DROP TABLE #MSTR_PROV_NM
DROP TABLE #MAX_DT
--DROP TABLE #DX_UPDATE
DROP TABLE #AL_ECL_STAGING

--DROP TABLE #AL_ENROLLMENT2_SOC
DROP TABLE #AL_R_CUST_DIAG_TO_MDC
DROP TABLE #AL_R_CUST_ASSIGNED_MDC_FULL_DESC
DROP TABLE #GRP_LOB
DROP TABLE #AL_R_CUST_PCP_POD
DROP TABLE #MEM_MAX_DT_PCP
--DROP TABLE #AL_LEAD_HOSPITAL
DROP TABLE #AL_SOCIAL
DROP TABLE #ENROLL_LOB
--DROP TABLE #AL_ENROLLMENT2_COUNTY
--DROP TABLE #ENROLL_LH
DROP TABLE #MAX_AL_ENROLLMENT
drop table #last_fix
drop table #providerdim
DROP TABLE #AL_REHAB_AUTHS

PRINT 'DEV_AL_ECL_TEST PROCEDURE COMPLETED:'
PRINT GETDATE()

SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT
CREATE NONCLUSTERED INDEX al_ecl1_ind ON dbo.DEV_AL_ECL_TEST
(
COMP_ID ASC,
MIDMONTH ASC,
SUPERCOMP ASC,
GL2_1 ASC,
GL2_2 ASC,
LOB_ID ASC,
MEM_ID ASC,
PCP_ID ASC,
PORG_ID ASC,
PROV_ID ASC,
SOURCE ASC,
SPEC_CD ASC,
SVC_CD ASC
)



PRINT 'CREATE INDEXES:'
PRINT GETDATE()
CREATE NONCLUSTERED INDEX AL_ECL2_IND ON dbo.DEV_AL_ECL_TEST
(
[PCP_POD] ASC,
[ECL_ID] ASC,
[VEND_ID] ASC,
[MEM_ID] ASC,
[COMP_ID] ASC,
[MIDMONTH] ASC,
[SUPERCOMP] ASC,
[SOURCE] ASC,
[GL2_1_DESC] ASC,
[GL2_2_DESC] ASC,
[GL2_3_DESC] ASC
)
INCLUDE ( [AUTH_ID],
[CLM_NO],
[DOS_DT],
[DOS_THRU_DT],
[DX_1_CD],
[DX_1_DESC],
[GRP_ID],
[HCFA_POS_CD],
[INC_PAID_AMT],
[LOB_ID],
[MEM_COUNTY],
[MOD_CD],
[PAID_AMT],
[PAID_DT],
[PCP_ID],
[PORG_ID],
[PRICING_DRG_CD],
[PROV_ID],
[PROV_NM],
[SPEC_DESC],
[SVC_CD],
[SVC_DESC],
[SVC_QTY],
[PCP_NM],
[DUAL]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

PRINT GETDATE()



END /* MAIN */


Trisha

 

[padinka]

oops, ignore the "FETCH NEXT FROM ECL_CURS INTO @DATE_BEG,@DATE_END,@DATE_MID,@DATE_NAME"

That should be commented out. I took all the commented out lines out of the thing and apparently skipped deleting that line.

Trisha