February 19, 2010 at 3:16 am
/*****************************************************************
SP Name : P_groupprofilessubmeasureefficiency
Revision History:
Date Reason
------------------------------------------------------------------
28-Aug-09 Part 8 - Calculate Normalized Risk Score for Each Group
31-Aug-09 Part 9 - Calculate Overall and Submeasure Efficiency
Indexes for Each Group
01-Sep-09 Part 10 - Calculate Normalized DCG Score for Each "Subgroup"
03-Sep-09 Part 11 - Calculate Overall and Submeasure Efficiency
Indexes for ##total_client_average_pmpm_trimmed_inpatientEach "Subgroup"
18-Jan-10 RAF integration- Calculate Overall and Submeasure Efficiency
*******************************************************************/
-- Drop stored procedure if it already exists
--IF EXISTS (
-- SELECT *
-- FROM INFORMATION_SCHEMA.ROUTINES
-- WHERE SPECIFIC_SCHEMA = N'dbo'
-- AND SPECIFIC_NAME = N'p_GroupProfilesSubmeasureEfficiency'
--)
-- DROP PROCEDURE dbo.p_GroupProfilesSubmeasureEfficiency
--GO
--
ALTER PROCEDURE [dbo].[p_GroupProfilesSubmeasureEfficiency_temp]
@batch_id INT,
@level_num INT,
@hierarchy_key INT,
@end_date DATETIME
AS
--Code added for Part 8 on 26-Aug-09 begins (All)
IF Object_id('tempdb..##pcp_type_weighted_average_risk') > 0
DROP TABLE ##pcp_type_weighted_average_risk
--Fetch PCP_TYPE_WEIGHTED_AVERAGE_DCG for the 3 PCP_Types
SELECT /* Commented as part of RAF integration
dbo.f_Val_Division(Sum(dcg_raw * mem_mm), Sum(mem_mm), 0) AS weighted_average,*/
dbo.F_val_division(Sum(medicalriskscore * mem_mm),Sum(mem_mm),0) AS weighted_medicalrisk,
dbo.F_val_division(Sum(pharmacyriskscore * mem_mm),Sum(mem_mm),0) AS weighted_pharmacyrisk,
pcp_type
INTO ##pcp_type_weighted_average_risk
FROM member_profile_summary
WHERE batch_id = @batch_id
GROUP BY pcp_type
--Fetch GROUP_NORMALIZED_AVERAGE_DCG to replace column AVG_DCG on the table group_profile_summary
IF Object_id('tempdb..##group_normalized_average_risk') > 0
DROP TABLE ##group_normalized_average_risk
SELECT /* Commented as part of RAF integration
dbo.f_Val_Division(grp_weighted_avg_risk.group_weighted_average_dcg, (dbo.f_Val_Division(Sum(pcptypesum),summmforgp, 0)), 0) AS group_normalized_average_dcg, */
dbo.f_Val_Division(grp_weighted_avg_risk.group_weighted_medicalrisk, dbo.f_Val_Division(Sum(medical_sums),summmforgp, 0) , 0) AS group_normalized_average_medicalrisk,
dbo.f_Val_Division(grp_weighted_avg_risk.group_weighted_pharmacyrisk, dbo.f_Val_Division(Sum(pharmacy_sums),summmforgp, 0) , 0) AS group_normalized_average_pharmacyrisk,
clientavgdcg.level_key,
clientavgdcg.placement_cd
INTO ##group_normalized_average_risk
FROM (SELECT /* Commented as part of RAF integration
Sum(mempsum.mem_mm) * avgrisks.weighted_average AS pcptypesum, */
Sum(mempsum.mem_mm) * avgrisks.weighted_medicalrisk as medical_sums,
Sum(mempsum.mem_mm) * avgrisks.weighted_pharmacyrisk as pharmacy_sums,
mempsum.batch_id,
vh.level_key,
avgrisks.pcp_type,
mempsum.placement_cd
FROM member_profile_summary mempsum
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (mempsum.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND mempsum.placement_cd = vh.placement_cd )--Added as part of provider being in multiple groups
INNER JOIN ##pcp_type_weighted_average_risk avgrisks
ON mempsum.pcp_type = avgrisks.pcp_type
WHERE mempsum.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,
batch_id,
avgrisks.pcp_type,
mempsum.placement_cd,
avgrisks.weighted_medicalrisk,
avgrisks.weighted_pharmacyrisk) clientavgdcg
INNER JOIN (SELECT Sum(memprofsum.mem_mm) AS summmforgp,
vh.level_key,
mempsum.placement_cd
FROM member_profile_summary memprofsum
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (memprofsum.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND mempsum.placement_cd = vh.placement_cd )--Added as part of provider being in multiple groups
WHERE memprofsum.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,mempsum.placement_cd) summmforallpcpingroup
ON clientavgdcg.level_key = summmforallpcpingroup.level_key
AND clientavgdcg.placement_cd = summmforallpcpingroup.placement_cd
INNER JOIN (SELECT /* Commented as part of RAF integration
dbo.f_Val_Division(Sum(dcg_raw * mem_mm), Sum(mem_mm), 0) AS group_weighted_average_dcg,*/ dbo.F_val_division(Sum(medicalriskscore * mem_mm),Sum(mem_mm),0) ASgroup_weighted_medicalrisk
, dbo.F_val_division(Sum(pharmacyriskscore * mem_mm),Sum(mem_mm),0) ASgroup_weighted_pharmacyrisk
,vh.level_key
,memprofsum,placement_cd
FROM member_profile_summary memprofsum
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (memprofsum.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND memprofsum.placement_cd = vh.placement_cd )--Added as part of provider being in multiple groups
WHERE memprofsum.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,memprofsum.placement_cd) grp_weighted_avg_risk
ON grp_weighted_avg_risk.level_key = summmforallpcpingroup.level_key
and grp_weighted_avg_risk.placement_cd = summmforallpcpingroup.placement_cd
GROUP BY clientavgdcg.level_key,
clientavgdcg.placement_cd,
summmforgp,
grp_weighted_avg_risk.group_normalized_average_medicalrisk,
grp_weighted_avg_risk.group_normalized_average_pharmacyrisk
--Code added for Part 8 on 26-Aug-09 ends
--Code added for Part 9 on 26-Aug-09 begins (All)
--Calculate Efficiency index for Group_Profile_Summary
IF Object_id('tempdb..#average_pmpm_trimmed_sum') > 0
DROP TABLE #average_pmpm_trimmed_sum
-- Fetch PCP_TYPE_AVERAGE_PMPM_TRIMMED for each pcp_type
SELECT dbo.f_Val_Division(Sum(summ.total_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS total_pcp_type_average_pmpm_trimmed,
summ.pcp_type
INTO #average_pmpm_trimmed_sum
FROM member_profile_inpatient inpt
INNER JOIN member_profile_summary summ
ON inpt.mem_key = summ.mem_key
AND inpt.batch_id = summ.batch_id
WHERE summ.batch_id = @batch_id
GROUP BY summ.pcp_type
IF Object_id('tempdb..##total_client_average_pmpm_trimmed_summary') > 0
DROP TABLE ##total_client_average_pmpm_trimmed_summary
-- Fetch value for eff columns in Group_Profile_Summary
SELECT dbo.F_val_division(total_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(pcptypesum), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS totaleffindex,
pcptypelevelsum.level_key
INTO ##total_client_average_pmpm_trimmed_summary
FROM (SELECT Sum(memprof.mem_mm) * trimsum.total_pcp_type_average_pmpm_trimmed AS pcptypesum,
memprof.batch_id,
vh.level_key,
trimsum.pcp_type,
memprof.placement_cd --Added as part of provider being in multiple groups
FROM member_profile_summary memprof
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (memprof.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND memprof.placement_cd = vh.placement_cd) --Added as part of provider being in multiple groups
INNER JOIN #average_pmpm_trimmed_sum trimsum
ON memprof.pcp_type = trimsum.pcp_type
WHERE memprof.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,
memprof.placement_cd, --Added as part of provider being in multiple groups
batch_id,
trimsum.pcp_type,
trimsum.total_pcp_type_average_pmpm_trimmed) pcptypelevelsum
INNER JOIN (SELECT Sum(memsum.mem_mm) AS summmforgp,
dbo.f_Val_Division(Sum(total_pmpm_trimmed * mem_mm), Sum(mem_mm), 0) AS total_group_average_pmpm_trimmed,
vh.level_key,memsum.placement_cd
FROM member_profile_summary memsum
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (memsum.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND memsum.placement_cd = vh.placement_cd) --Added as part of provider being in multiple groups
WHERE memsum.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,memsum.placement_cd) effgrpavgtrim
ON pcptypelevelsum.level_key = effgrpavgtrim.level_key
and pcptypelevelsum.placement_cd = effgrpavgtrim.placement_cd
INNER JOIN ##group_normalized_average_risk g
ON g.level_key = effgrpavgtrim.level_key
and g.placement_cd = effgrpavgtrim.placement_cd
GROUP BY pcptypelevelsum.level_key,
pcptypelevelsum.placement_cd,
summmforgp,
total_group_average_pmpm_trimmed,
g.group_normalized_average_medicalrisk
--group_normalized_average_dcg //Commented as part of RAF integration
--Calculate Efficiency index for Group_Profile_Inpatient
IF Object_id('tempdb..#average_pmpm_trimmed_in') > 0
DROP TABLE #average_pmpm_trimmed_in
-- Fetch PCP_TYPE_AVERAGE_PMPM_TRIMMED for each pcp_type
SELECT dbo.f_Val_Division(Sum(inpt.medsurg_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS medsurg_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.ob_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS ob_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.nonacute_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS nonacute_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.prof_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS prof_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.total_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS total_pcp_type_average_pmpm_trimmed,
summ.pcp_type
INTO #average_pmpm_trimmed_in
FROM member_profile_inpatient inpt
INNER JOIN member_profile_summary summ
ON inpt.mem_key = summ.mem_key
AND inpt.batch_id = summ.batch_id
WHERE summ.batch_id = @batch_id
GROUP BY pcp_type
IF Object_id('tempdb..##total_client_average_pmpm_trimmed_inpatient') > 0
DROP TABLE ##total_client_average_pmpm_trimmed_inpatient
-- Fetch value for eff columns in Group_Profile_Inpatient
SELECT dbo.F_val_division(medsurg_group_average_pmpm_trimmed, dbo.f_Val_Division(Sum(medsurgsums), summmforgp, 0) * g.group_normalized_average_medicalrisk, 0) AS medsurgeffindex,
dbo.F_val_division(ob_group_average_pmpm_trimmed,dbo.f_Val_Division(Sum(obsums), summmforgp, 0) * g.group_normalized_average_medicalrisk, 0) AS obeffindex,
dbo.F_val_division(nonacute_group_average_pmpm_trimmed,dbo.f_Val_Division(Sum(nonacutesums), summmforgp, 0) *g.group_normalized_average_medicalrisk, 0) AS nonacuteeffindex,
dbo.F_val_division(prof_group_average_pmpm_trimmed,dbo.f_Val_Division(Sum(profsums), summmforgp, 0) * g.group_normalized_average_medicalrisk, 0) AS profeffindex,
dbo.F_val_division(total_group_average_pmpm_trimmed,dbo.f_Val_Division(Sum(totalsums), summmforgp, 0) * g.group_normalized_average_medicalrisk, 0) AS totaleffindex,
summmmforpcptype.level_key,
summmmforpcptype.placement_cd --Added as part of provider being in multiple groups
INTO ##total_client_average_pmpm_trimmed_inpatient
FROM (SELECT Sum(inpt.mem_mm) * avgtrim.medsurg_pcp_type_average_pmpm_trimmed AS medsurgsums,
Sum(inpt.mem_mm) * avgtrim.ob_pcp_type_average_pmpm_trimmed AS obsums,
Sum(inpt.mem_mm) * avgtrim.nonacute_pcp_type_average_pmpm_trimmed AS nonacutesums,
Sum(inpt.mem_mm) * avgtrim.prof_pcp_type_average_pmpm_trimmed AS profsums,
Sum(inpt.mem_mm) * avgtrim.total_pcp_type_average_pmpm_trimmed AS totalsums,
inpt.batch_id,
vh.level_key,
avgtrim.pcp_type,
memsumm.placement_cd --Added as part of provider being in multiple groups
FROM member_profile_summary memsumm
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (memsumm.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND memsumm.placement_cd = vh.placement_cd)--Added as part of provider being in multiple groups
INNER JOIN #average_pmpm_trimmed_in avgtrim
ON memsumm.pcp_type = avgtrim.pcp_type
INNER JOIN member_profile_inpatient inpt
ON inpt.mem_key = memsumm.mem_key
AND inpt.batch_id = memsumm.batch_id
WHERE memsumm.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,
memsumm.placement_cd, --Added as part of provider being in multiple groups
inpt.batch_id,
avgtrim.pcp_type,
avgtrim.medsurg_pcp_type_average_pmpm_trimmed,
avgtrim.ob_pcp_type_average_pmpm_trimmed,
avgtrim.nonacute_pcp_type_average_pmpm_trimmed,
avgtrim.prof_pcp_type_average_pmpm_trimmed,
avgtrim.total_pcp_type_average_pmpm_trimmed) summmmforpcptype
INNER JOIN (SELECT Sum(m.mem_mm) AS summmforgp,
dbo.f_Val_Division(Sum(inpt.medsurg_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS medsurg_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.ob_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS ob_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.nonacute_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS nonacute_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.prof_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS prof_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(inpt.total_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) AS total_group_average_pmpm_trimmed,
vh.level_key,
m.placement_cd --Added as part provider being multiple groups
FROM member_profile_summary m
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (m.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND m.placement_cd = vh.placement_cd ) --Added as part provider being multiple groups
INNER JOIN member_profile_inpatient inpt
ON inpt.mem_key = m.mem_key
AND inpt.batch_id = m.batch_id
WHERE m.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,m.placement_cd) avgpmpm
ON summmmforpcptype.level_key = avgpmpm.level_key
and summmmforpcptype.placement_cd = avgpmpm.placement_cd
INNER JOIN ##group_normalized_average_risk g
ON g.level_key = avgpmpm.level_key
and g.placement_cd = avgpmpm.placement_cd
GROUP BY summmmforpcptype.level_key,
summmmforpcptype.placement_cd, --Added as part of provider being in multiple groups
summmforgp,
g.group_normalized_average_medicalrisk,
medsurg_group_average_pmpm_trimmed,
ob_group_average_pmpm_trimmed,
nonacute_group_average_pmpm_trimmed,
prof_group_average_pmpm_trimmed,
total_group_average_pmpm_trimmed
--Calculate Efficiency index for Group_Profile_Outpatient
IF Object_id('tempdb..#average_pmpm_trimmed') > 0
DROP TABLE #average_pmpm_trimmed
-- Fetch PCP_TYPE_AVERAGE_PMPM_TRIMMED for each pcp_type
SELECT dbo.f_Val_Division(Sum(outpt.ed_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS ed_visits_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.pcp_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS pcp_visits_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.scp_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS scp_visits_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.lab_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS lab_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.diagrad_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS diagrad_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.radther_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS radther_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.ambsurg_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS ambsurg_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.diagproc_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS diagproc_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.dme_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS dme_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.ptot_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS ptot_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.speech_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS speech_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.other_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS other_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.total_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS total_pcp_type_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(outpt.pharm_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) AS pharm_pcp_type_average_pmpm_trimmed,
summ.pcp_type
INTO #average_pmpm_trimmed
FROM member_profile_outpatient outpt
INNER JOIN member_profile_summary summ
ON outpt.mem_key = summ.mem_key
AND outpt.batch_id = summ.batch_id
WHERE summ.batch_id = @batch_id
GROUP BY pcp_type
IF Object_id('tempdb..##total_client_average_pmpm_trimmed_outpatient') > 0
DROP TABLE ##total_client_average_pmpm_trimmed_outpatient
SELECT dbo.F_val_division(ed_visits_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(ed_visitssums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS ed_visitseffindex,
dbo.F_val_division(pcp_visits_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(pcp_visitssums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS pcp_visitseffindex,
dbo.F_val_division(scp_visits_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(scp_visitssums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS scp_visitseffindex,
dbo.F_val_division(lab_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(labsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS labeffindex,
dbo.F_val_division(diagrad_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(diagradsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS diagradeffindex,
dbo.F_val_division(radther_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(radthersums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS radthereffindex,
dbo.F_val_division(ambsurg_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(ambsurgsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS ambsurgeffindex,
dbo.F_val_division(diagproc_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(diagprocsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS diagproceffindex,
dbo.F_val_division(dme_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(dmesums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS dmeeffindex,
dbo.F_val_division(ptot_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(ptotsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS ptoteffindex,
dbo.F_val_division(speech_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(speechsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS speecheffindex,
dbo.F_val_division(other_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(othersums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS othereffindex,
dbo.F_val_division(total_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(totalsums), summmforgp, 0)) * g.group_normalized_average_medicalrisk),
0) AS totaleffindex,
dbo.F_val_division(pharm_group_average_pmpm_trimmed,((dbo.f_Val_Division(Sum(pharmsums), summmforgp, 0)) * g.group_normalized_average_pharmacyrisk),
0) AS pharmeffindex,
efficiencysum.level_key,
efficiencysum.placement_cd --Added as part of provider being in multiple groups
INTO ##total_client_average_pmpm_trimmed_outpatient
FROM (SELECT Sum(i.mem_mm) * a.ed_visits_pcp_type_average_pmpm_trimmed AS ed_visitssums,
Sum(i.mem_mm) * a.pcp_visits_pcp_type_average_pmpm_trimmed AS pcp_visitssums,
Sum(i.mem_mm) * a.scp_visits_pcp_type_average_pmpm_trimmed AS scp_visitssums,
Sum(i.mem_mm) * a.lab_pcp_type_average_pmpm_trimmed AS labsums,
Sum(i.mem_mm) * a.diagrad_pcp_type_average_pmpm_trimmed AS diagradsums,
Sum(i.mem_mm) * a.radther_pcp_type_average_pmpm_trimmed AS radthersums,
Sum(i.mem_mm) * a.ambsurg_pcp_type_average_pmpm_trimmed AS ambsurgsums,
Sum(i.mem_mm) * a.diagproc_pcp_type_average_pmpm_trimmed AS diagprocsums,
Sum(i.mem_mm) * a.dme_pcp_type_average_pmpm_trimmed AS dmesums,
Sum(i.mem_mm) * a.ptot_pcp_type_average_pmpm_trimmed AS ptotsums,
Sum(i.mem_mm) * a.speech_pcp_type_average_pmpm_trimmed AS speechsums,
Sum(i.mem_mm) * a.other_pcp_type_average_pmpm_trimmed AS othersums,
Sum(i.mem_mm) * a.total_pcp_type_average_pmpm_trimmed AS totalsums,
Sum(i.mem_mm) * a.pharm_pcp_type_average_pmpm_trimmed AS pharmsums,
i.batch_id,
vh.level_key,
a.pcp_type,
m.placement_cd --Added as part of provider being in multiple groups
FROM member_profile_summary m
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (m.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND m.placement_cd = vh.placement_cd )
INNER JOIN #average_pmpm_trimmed a
ON m.pcp_type = a.pcp_type
INNER JOIN member_profile_outpatient i
ON i.mem_key = m.mem_key
AND i.batch_id = m.batch_id
WHERE m.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,
m.placement_cd, --Added as part of provider being in multiple groups
i.batch_id,
a.pcp_type,
a.ed_visits_pcp_type_average_pmpm_trimmed,
a.pcp_visits_pcp_type_average_pmpm_trimmed,
a.scp_visits_pcp_type_average_pmpm_trimmed,
a.lab_pcp_type_average_pmpm_trimmed,
a.diagrad_pcp_type_average_pmpm_trimmed,
a.radther_pcp_type_average_pmpm_trimmed,
a.ambsurg_pcp_type_average_pmpm_trimmed,
a.diagproc_pcp_type_average_pmpm_trimmed,
a.dme_pcp_type_average_pmpm_trimmed,
a.ptot_pcp_type_average_pmpm_trimmed,
a.speech_pcp_type_average_pmpm_trimmed,
a.other_pcp_type_average_pmpm_trimmed,
a.total_pcp_type_average_pmpm_trimmed,
a.pharm_pcp_type_average_pmpm_trimmed) efficiencysum
INNER JOIN (SELECT Sum(m.mem_mm) AS summmforgp,
dbo.f_Val_Division(Sum(i.ed_visits_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS ed_visits_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.pcp_visits_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS pcp_visits_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.scp_visits_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS scp_visits_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.lab_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS lab_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.diagrad_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS diagrad_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.radther_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS radther_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.ambsurg_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS ambsurg_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.diagproc_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS diagproc_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.dme_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS dme_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.ptot_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS ptot_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.speech_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS speech_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.other_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS other_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.total_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS total_group_average_pmpm_trimmed,
dbo.f_Val_Division(Sum(i.pharm_pmpm_trimmed * i.mem_mm), Sum(i.mem_mm), 0) AS pharm_group_average_pmpm_trimmed,
vh.level_key,
m.placement_cd --Added as part of provider being in multiple groups
FROM member_profile_summary m
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (m.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND m.placement_cd = vh.placement_cd )
INNER JOIN member_profile_outpatient i
ON i.mem_key = m.mem_key
AND i.batch_id = m.batch_id
WHERE m.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,m.placement_cd) group_average_pmpm
ON efficiencysum.level_key = group_average_pmpm.level_key
and efficiencysum.placement_cd = group_average_pmpm.placement_cd
INNER JOIN ##group_normalized_average_risk g
ON g.level_key = group_average_pmpm.level_key
and g.placement_cd = group_average_pmpm.placement_cd
GROUP BY efficiencysum.level_key,
efficiencysum.placement_cd, --Added as part of provider being in multiple groups
summmforgp,
g.group_normalized_average_medicalrisk,
g.group_normalized_average_pharmacyrisk,
ed_visits_group_average_pmpm_trimmed,
pcp_visits_group_average_pmpm_trimmed,
scp_visits_group_average_pmpm_trimmed,
lab_group_average_pmpm_trimmed,
diagrad_group_average_pmpm_trimmed,
radther_group_average_pmpm_trimmed,
ambsurg_group_average_pmpm_trimmed,
diagproc_group_average_pmpm_trimmed,
dme_group_average_pmpm_trimmed,
ptot_group_average_pmpm_trimmed,
speech_group_average_pmpm_trimmed,
other_group_average_pmpm_trimmed,
total_group_average_pmpm_trimmed,
pharm_group_average_pmpm_trimmed
--Code added for Part 10 on 26-Aug-09 begins
--Calculate SUBGROUP_NORMALIZED_DCG for PCP_Types other than 'ALL' to replace AVG_DCG
IF Object_id('tempdb..##subgroup_normalized_risk') > 0
DROP TABLE ##subgroup_normalized_risk
SELECT /* Commented as part of RAF integration
dbo.f_Val_Division(dbo.f_Val_Division(Sum(dcg_raw * mem_mm), Sum(mem_mm), 0), weighted_average, 0) ASsubgroup_normalized_dcg, */
dbo.F_val_division((dbo.F_val_division(Sum(mps.medicalriskscore * mps.mem_mm),Sum(mps.mem_mm),0)),
d.weighted_medicalrisk,0) AS subgroup_norm_medicalrisk
,dbo.F_val_division((dbo.F_val_division(Sum(mps.pharmacyriskscore * mps.mem_mm),Sum(mps.mem_mm),0)),
d.weighted_pharmacyrisk,0) AS subgroup_norm_pharmacyrisk,
mps.pcp_type,
vh.level_key,
mps.placement_cd --Added as part of provider being in multiple groups
INTO ##subgroup_normalized_risk
FROM dbo.member_profile_summary mps
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (mps.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND mps.placement_cd = vh.placement_cd)
INNER JOIN ##pcp_type_weighted_average_risk d
ON d.pcp_type = mps.pcp_type
WHERE mps.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY vh.level_key,
mps.placement_cd,--Added as part of provider being in multiple groups
mps.pcp_type,
d.weighted_medicalrisk,
d.weighted_pharmacyrisk
--weighted_average
--Code added for Part 10 on 26-Aug-09 ends
--Code added for Part 11 on 26-Aug-09 begins
--Calculate SSUBGROUP_AVERAGE_PMPM_TRIMMED for group_profile_inpatient
IF Object_id('tempdb..#pmpm_trimmed_denom') > 0
DROP TABLE #pmpm_trimmed_denom
SELECT dbo.f_Val_Division(Sum(inpt.medsurg_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) * subgroup_norm_medicalrisk AS medsurg_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(inpt.ob_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) * subgroup_norm_medicalrisk AS ob_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(inpt.nonacute_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) * subgroup_norm_medicalrisk AS nonacute_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(inpt.prof_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) * subgroup_norm_medicalrisk AS prof_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(inpt.total_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0) * subgroup_norm_medicalrisk AS total_pmpm_trimmed_denom,
summ.pcp_type,
d.level_key,
d.placement_cd --Added as part of provider being in multiple groups
INTO #pmpm_trimmed_denom
FROM member_profile_inpatient inpt
INNER JOIN member_profile_summary summ
ON inpt.mem_key = summ.mem_key
AND inpt.batch_id = summ.batch_id
INNER JOIN ##subgroup_normalized_risk d -- Changed to a global table
ON d.pcp_type = summ.pcp_type
WHERE summ.batch_id = @batch_id
GROUP BY summ.pcp_type,
d.level_key,
d.placement_cd,--Added as part of provider being in multiple groups
subgroup_norm_medicalrisk
--Calculate efficiency index for group_profile_inpatient
IF Object_id('tempdb..##effinserts') > 0
DROP TABLE ##effinserts
SELECT dbo.f_Val_Division(dbo.f_Val_Division(Sum(inpt.medsurg_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0), den.medsurg_pmpm_trimmed_denom, 0) AS medsurg_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(inpt.ob_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0), den.ob_pmpm_trimmed_denom, 0) AS ob_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(inpt.nonacute_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0), den.nonacute_pmpm_trimmed_denom, 0) AS nonacute_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(inpt.prof_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0), den.prof_pmpm_trimmed_denom, 0) AS prof_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(inpt.total_pmpm_trimmed * inpt.mem_mm), Sum(inpt.mem_mm), 0), den.total_pmpm_trimmed_denom, 0) AS total_effinserts,
summ.pcp_type,
vh.level_key,
summ.placement_cd --Added as part of provider being in multiple groups
INTO ##effinserts
FROM member_profile_inpatient inpt
INNER JOIN member_profile_summary summ
ON inpt.mem_key = summ.mem_key
AND inpt.batch_id = summ.batch_id
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (summ.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND summ.placement_cd = vh.placement_cd)
INNER JOIN #pmpm_trimmed_denom den
ON den.level_key = vh.level_key
AND den.pcp_type = summ.pcp_type
AND den.placement_cd = summ.placement_cd
WHERE summ.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY summ.pcp_type,
vh.level_key,
summ.placement_cd,--Added as part of provider being in multiple groups
den.medsurg_pmpm_trimmed_denom,
den.ob_pmpm_trimmed_denom,
den.nonacute_pmpm_trimmed_denom,
den.prof_pmpm_trimmed_denom,
den.total_pmpm_trimmed_denom
--Calculate SSUBGROUP_AVERAGE_PMPM_TRIMMED for group_profile_outpatient
IF Object_id('tempdb..#pmpm_trimmed_denom_outpatient') > 0
DROP TABLE #pmpm_trimmed_denom_outpatient
SELECT dbo.f_Val_Division(Sum(outpt.ed_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS ed_visits_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.pcp_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS pcp_visits_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.scp_visits_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS scp_visits_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.lab_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS lab_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.diagrad_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS diagrad_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.radther_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS radther_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.ambsurg_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS ambsurg_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.diagproc_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS diagproc_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.dme_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS dme_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.ptot_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS ptot_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.speech_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS speech_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.other_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS other_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.total_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_medicalrisk AS total_pmpm_trimmed_denom,
dbo.f_Val_Division(Sum(outpt.pharm_pmpm_trimmed * outpt.mem_mm), Sum(outpt.mem_mm), 0) * subgroup_norm_pharmacyrisk AS pharm_pmpm_trimmed_denom,
summ.pcp_type,
d.level_key,
d.placement_cd --Added as part of provider being in multiple groups
INTO #pmpm_trimmed_denom_outpatient
FROM member_profile_outpatient outpt
INNER JOIN member_profile_summary summ
ON outpt.mem_key = summ.mem_key
AND outpt.batch_id = summ.batch_id
INNER JOIN ##subgroup_normalized_risk d -- Changed to a global table
ON d.pcp_type = summ.pcp_type
WHERE summ.batch_id = @batch_id
GROUP BY summ.pcp_type,
d.level_key,
d.placement_cd, --Added as part of provider being in multiple groups
subgroup_norm_medicalrisk,
subgroup_norm_pharmacyrisk
--Calculate efficiency index for group_profile_outpatient
IF Object_id('tempdb..##effinsertsout') > 0
DROP TABLE ##effinsertsout
SELECT dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.ed_visits_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.ed_visits_pmpm_trimmed_denom, 0) AS ed_visits_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.pcp_visits_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.pcp_visits_pmpm_trimmed_denom, 0) AS pcp_visits_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.scp_visits_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.scp_visits_pmpm_trimmed_denom, 0) AS scp_visits_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.lab_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.lab_pmpm_trimmed_denom, 0) AS lab_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.diagrad_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.diagrad_pmpm_trimmed_denom, 0) AS diagrad_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.radther_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.radther_pmpm_trimmed_denom, 0) AS radther_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.ambsurg_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.ambsurg_pmpm_trimmed_denom, 0) AS ambsurg_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.diagproc_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.diagproc_pmpm_trimmed_denom, 0) AS diagproc_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.dme_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.dme_pmpm_trimmed_denom, 0) AS dme_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.ptot_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.ptot_pmpm_trimmed_denom, 0) AS ptot_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.speech_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.speech_pmpm_trimmed_denom, 0) AS speech_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.other_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.other_pmpm_trimmed_denom, 0) AS other_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.total_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.total_pmpm_trimmed_denom, 0) AS total_effinserts,
dbo.f_Val_Division(dbo.f_Val_Division(Sum(outpt.pharm_pmpm_trimmed * outpt.mem_mm) , Sum(outpt.mem_mm), 0), den.pharm_pmpm_trimmed_denom, 0) AS pharm_effinserts,
summ.pcp_type,
vh.level_key,
summ.placement_cd --Added as part of provider being in multiple groups
INTO ##effinsertsout
FROM member_profile_outpatient outpt
INNER JOIN member_profile_summary summ
ON outpt.mem_key = summ.mem_key
AND outpt.batch_id = summ.batch_id
INNER JOIN dbo.F_tbl_gethierarchy(@level_num) vh
ON (summ.pcp_prov_key = vh.prov_key
AND @end_date BETWEEN vh.start_dt AND Isnull(vh.end_dt,'12/31/2199')
AND summ.placement_cd = vh.placement_cd)
INNER JOIN #pmpm_trimmed_denom_outpatient den
ON den.level_key = vh.level_key
AND den.pcp_type = summ.pcp_type
and den.placement_cd = summ.placement_cd --Added as part of provider being in multiple groups
WHERE summ.batch_id = @batch_id
AND vh.hierarchy_key = @hierarchy_key
AND vh.level_key IS NOT NULL
GROUP BY summ.pcp_type,
vh.level_key,
summ.placement_cd, --Added as part of provider being in multiple groups
den.ed_visits_pmpm_trimmed_denom,
den.pcp_visits_pmpm_trimmed_denom,
den.scp_visits_pmpm_trimmed_denom,
den.lab_pmpm_trimmed_denom,
den.diagrad_pmpm_trimmed_denom,
den.radther_pmpm_trimmed_denom,
den.ambsurg_pmpm_trimmed_denom,
den.diagproc_pmpm_trimmed_denom,
den.dme_pmpm_trimmed_denom,
den.ptot_pmpm_trimmed_denom,
den.speech_pmpm_trimmed_denom,
den.other_pmpm_trimmed_denom,
den.total_pmpm_trimmed_denom,
den.pharm_pmpm_trimmed_denom
--Code added for Part 11 on 26-Aug-09 ends
-- Drop the Temp table created
IF Object_id('tempdb..#pmpm_trimmed_denom_outpatient') > 0
DROP TABLE #pmpm_trimmed_denom_outpatient
IF Object_id('tempdb..#pmpm_trimmed_denom') > 0
DROP TABLE #pmpm_trimmed_denom
IF Object_id('tempdb..#average_pmpm_trimmed') > 0
DROP TABLE #average_pmpm_trimmed
IF Object_id('tempdb..#average_pmpm_trimmed_in') > 0
DROP TABLE #average_pmpm_trimmed_in
IF Object_id('tempdb..#average_pmpm_trimmed_sum') > 0
DROP TABLE #average_pmpm_trimmed_sum
GO
February 19, 2010 at 3:50 am
Hi,
Post the error message, when you’re got during compile the procedure.
February 19, 2010 at 3:58 am
I didnt get any error message. It got compiled successfully. My doubt is this. Why it got successfully compiled eventhough it has issues.
February 19, 2010 at 4:04 am
So what is the error you get at run time ?
February 19, 2010 at 5:17 am
only on stored procedures, you can reference a table that does not exist, and the proc will still compile successfully; they call it delayed name resolution.
so you'd be able to compile the proc, and then add the tables it references later. could that be the issue you are refering to?
btw, this is the reason people will tell you that the sysdepends table can be inaccurate, because if you add a proc like that, and the table didn't exist at the time of creation, there is no dependency added for reference.
Lowell
February 19, 2010 at 5:32 am
To expand Lowell's post, here is the Books Online entry on Deferred Name Resolution and Compilation.
Paul
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply