How to select/populate table with most current records
I have the following query that creates a table from a dataset that contains multiple months worth of data. I want to grab the most current records based on the FileDate.
USE [Kairos]
go
/****** Object: StoredProcedure [dbo].[spFiveStarComparisonReport2] Script Date: 10/6/2016 2:59:38 PM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Spfivestarcomparisonreport2]
-- Add the parameters for the stored procedure here
@kairosComparisonLabel NVARCHAR(256),
@KairosGroupLabel NVARCHAR(256),
@KairosFacilitiesProvnum NVARCHAR(256),
@OtherGroupLabel NVARCHAR(256),
@OtherSelectedProvnum NVARCHAR(256)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;
CREATE TABLE #templist
(
provnum NVARCHAR(255)
);
DECLARE @provnumID VARCHAR(10),
@Pos INT
SET @KairosFacilitiesProvnum = Ltrim(Rtrim(@KairosFacilitiesProvnum))
+ ','
SET @Pos = Charindex(',', @KairosFacilitiesProvnum, 1)
IF Replace(@KairosFacilitiesProvnum, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @provnumID = Ltrim(Rtrim(LEFT(@KairosFacilitiesProvnum,
@Pos
- 1
))
)
IF @provnumID <> ''
BEGIN
INSERT INTO #templist
(provnum)
VALUES (Cast(@provnumID AS INT))
--Use Appropriate conversion
END
SET @KairosFacilitiesProvnum = RIGHT(@KairosFacilitiesProvnum,
Len(
@KairosFacilitiesProvnum) - @Pos)
SET @Pos = Charindex(',', @KairosFacilitiesProvnum, 1)
END
END
--SELECT '@Pos', @Pos;
--SELECT '#TempList', * FROM #TempList;
CREATE TABLE #tblcomparisonreport
(
[provnum] [NVARCHAR](255) NULL,
[comparisonlabel] [NVARCHAR](255) NULL,
[groupsequence] [NVARCHAR](255) NULL,
[grouplabel] [NVARCHAR](255) NULL,
[provname] [NVARCHAR](255) NULL,
[provaddr1] [NVARCHAR](255) NULL,
[provaddr2] [NVARCHAR](255) NULL,
[provaddr3] [NVARCHAR](255) NULL,
[provcity] [NVARCHAR](255) NULL,
[provstate] [NVARCHAR](255) NULL,
[provzip] [NVARCHAR](255) NULL,
[county] [NVARCHAR](255) NULL,
[performancelabel] [NVARCHAR](255) NULL,
[overallrating] [NVARCHAR](255) NULL,
[surveyrating] [NVARCHAR](255) NULL,
[qualityrating] [NVARCHAR](255) NULL,
[staffingrating] [NVARCHAR](255) NULL,
[avgoverallrating] [NVARCHAR](255) NULL,
[avgsurveyrating] [NVARCHAR](255) NULL,
[avgqualityrating] [NVARCHAR](255) NULL,
[avgstaffingrating] [NVARCHAR](255) NULL,
[rn_staff_hours] [NVARCHAR](255) NULL,
[pt_staff_hours] [NVARCHAR](255) NULL,
[ls_adk_need] [NVARCHAR](255) NULL,
[ls_pain] [NVARCHAR](255) NULL,
[ls_c_press_ulcers] [NVARCHAR](255) NULL,
[ls_weight_loss] [NVARCHAR](255) NULL,
[ls_b_or_b_control_loss] [NVARCHAR](255) NULL,
[ls_uti] [NVARCHAR](255) NULL,
[ls_depression_sympts] [NVARCHAR](255) NULL,
[ls_restraints] [NVARCHAR](255) NULL,
[ls_1_fall_c_major_injury] [NVARCHAR](255) NULL,
[ls_flu_vac] [NVARCHAR](255) NULL,
[ls_pneumo_vac] [NVARCHAR](255) NULL,
[ls_rec_antipsych] [NVARCHAR](255) NULL,
[ss_pain] [NVARCHAR](255) NULL,
[ss_new_or_worse_press_ulc] [NVARCHAR](255) NULL,
[ss_flu_vac] [NVARCHAR](255) NULL,
[ss_pneumo_vac] [NVARCHAR](255) NULL,
[ss_new_antipsych] [NVARCHAR](255) NULL,
[filedate] [NVARCHAR](255) NULL,
[ls_move_indepen_worse] NVARCHAR(8) NULL,
[ls_antianx_hypno_meds] NVARCHAR(8) NULL,
[ss_improv_funct] NVARCHAR(8) NULL,
[ss_succ_discharg] NVARCHAR(8) NULL,
[ss_rehodpitl_nh_admiss] NVARCHAR(8) NULL,
[ss_outpat_er_visit] NVARCHAR(8) NULL
)
INSERT INTO #tblcomparisonreport
(provnum,
comparisonlabel,
groupsequence,
grouplabel,
provname,
provaddr1,
provaddr2,
provaddr3,
provcity,
provstate,
provzip,
county,
overallrating,
surveyrating,
qualityrating,
staffingrating,
avgoverallrating,
avgsurveyrating,
avgqualityrating,
avgstaffingrating,
[rn_staff_hours],
[pt_staff_hours],
[ls_adk_need],
[ls_pain],
[ls_c_press_ulcers],
[ls_weight_loss],
[ls_b_or_b_control_loss],
[ls_uti],
[ls_depression_sympts],
[ls_restraints],
[ls_1_fall_c_major_injury],
[ls_flu_vac],
[ls_pneumo_vac],
[ls_rec_antipsych],
[ss_pain],
[ss_new_or_worse_press_ulc],
[ss_flu_vac],
[ss_pneumo_vac],
[ss_new_antipsych],
[filedate])
SELECT DISTINCT [kairosfacilities].medicareprovnumber,
@KairosComparisonLabel
AS
ComparisonLabel,
0
AS
GroupSequence,
@KairosGroupLabel
AS
GroupLabel,
[kairosfacilities].[name],
[kairosfacilities].[addr1],
[kairosfacilities].[addr2],
NULL
AS
ProvAddr3,
[kairosfacilities].city,
[kairosfacilities].state,
[kairosfacilities].zip,
'NA',/*[KairosFacilities].County,*/
Format(Round([nc_providerinfo].[overall_rating], 1), '0.0')
AS
OverallRating,
Format(Round([nc_providerinfo].[survey_rating], 1), '0.0')
AS
SurveyRating,
Format(Round([nc_providerinfo].[quality_rating], 1), '0.0')
AS
QualityRating,
Format(Round([nc_providerinfo].[staffing_rating], 1), '0.0')
AS
StaffingRating,
NULL
AS
AvgOverallRating,
NULL
AS
AvgSurveyRating,
NULL
AS
AvgQualityRating,
NULL
AS
AvgStaffingRating,
Format(Round([nc_providerinfo].[rnhrd], 1), '0.0')
AS
[RN_Staff_Hours],
Format(Round([nc_providerinfo].[pthrd], 1), '0.0')
AS
[PT_Staff_Hours],
NULL
AS
[LS_ADK_NEED],
NULL
AS
[LS_PAIN],
NULL
AS
[LS_C_PRESS_ULCERS],
NULL
AS
[LS_WEIGHT_LOSS],
NULL
AS
[LS_B_OR_B_CONTROL_LOSS],
NULL
AS
[LS_UTI],
NULL
AS
[LS_DEPRESSION_SYMPTS],
NULL
AS
[LS_RESTRAINTS],
NULL
AS
[LS_1_FALL_C_MAJOR_INJURY],
NULL
AS
[LS_FLU_VAC],
NULL
AS
[LS_PNEUMO_VAC],
NULL
AS
[LS_REC_ANTIPSYCH],
NULL
AS
[SS_PAIN],
NULL
AS
[SS_NEW_OR_WORSE_PRESS_ULC],
NULL
AS
[SS_FLU_VAC],
NULL
AS
[SS_PNEUMO_VAC],
NULL
AS
[SS_NEW_ANTIPSYCH],
NULL
AS
[FileDate]
FROM [kairosfacilities]
LEFT JOIN nc_providerinfo
ON [kairosfacilities].medicareprovnumber =
nc_providerinfo.provnum
--WHERE NC_ProviderInfo.provnum IN (SELECT @KairosFacilitiesProvnum);
WHERE [kairosfacilities].medicareprovnumber IN (SELECT provnum
FROM #templist);
--WHERE [KairosFacilities].MedicareProvNumber = @KairosFacilitiesProvnum;
-- SELECT '#tblComparisonReport 1', * FROM #tblComparisonReport;
CREATE TABLE #tblcomparisontext
(
[comparisonnumber] [NVARCHAR](255) NULL,
[comparisontext] [NVARCHAR](255) NULL
)
DELETE FROM #templist;
INSERT INTO #tblcomparisontext
(comparisonnumber,
comparisontext)
SELECT 1,
@KairosComparisonLabel
CREATE TABLE #tblcomparisonotherselected
(
[provnum] [NVARCHAR](255) NULL
);
SET @OtherSelectedProvnum = Ltrim(Rtrim(@OtherSelectedProvnum)) + ','
SET @Pos = Charindex(',', @OtherSelectedProvnum, 1)
IF Replace(@OtherSelectedProvnum, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @provnumID = Ltrim(Rtrim(LEFT(@OtherSelectedProvnum, @Pos
- 1)))
IF @provnumID <> ''
BEGIN
INSERT INTO #templist
(provnum)
VALUES (Cast(@provnumID AS INT))
--Use Appropriate conversion
END
SET @OtherSelectedProvnum = RIGHT(@OtherSelectedProvnum, Len(
@OtherSelectedProvnum) - @Pos)
SET @Pos = Charindex(',', @OtherSelectedProvnum, 1)
END
END
--SELECT '#TempList', * from #TempList;
INSERT INTO #tblcomparisonotherselected
(provnum)
SELECT @OtherSelectedProvnum;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Michael - Here is the problem. Now that there are multiple rows in the NC_providerInfo table we need to query out the
-- older records and keep only the newest one.
INSERT INTO #tblcomparisonreport
(provnum,
comparisonlabel,
groupsequence,
grouplabel,
provname,
provaddr1,
provaddr2,
provaddr3,
provcity,
provstate,
provzip,
county,
overallrating,
surveyrating,
qualityrating,
staffingrating,
avgoverallrating,
avgsurveyrating,
avgqualityrating,
avgstaffingrating,
[rn_staff_hours],
[pt_staff_hours],
[ls_adk_need],
[ls_pain],
[ls_c_press_ulcers],
[ls_weight_loss],
[ls_b_or_b_control_loss],
[ls_uti],
[ls_depression_sympts],
[ls_restraints],
[ls_1_fall_c_major_injury],
[ls_flu_vac],
[ls_pneumo_vac],
[ls_rec_antipsych],
[ss_pain],
[ss_new_or_worse_press_ulc],
[ss_flu_vac],
[ss_pneumo_vac],
[ss_new_antipsych])
SELECT [nc_providerinfo].provnum,
@kairosComparisonLabel AS
ComparisonLabel,
1 AS
GroupSequence,
@OtherGroupLabel AS
GroupLabel,
[nc_providerinfo].provname,
[nc_providerinfo].[address],
NULL AS
ProvAddr2
,
NULL AS
ProvAddr3,
[nc_providerinfo].city,
[nc_providerinfo].state,
[nc_providerinfo].zip,
[nc_providerinfo].[county_name],
Format(Round([nc_providerinfo].[overall_rating], 1), '0.0') AS
OverallRating,
Format(Round([nc_providerinfo].[survey_rating], 1), '0.0') AS
SurveyRating,
Format(Round([nc_providerinfo].[quality_rating], 1), '0.0') AS
QualityRating,
Format(Round([nc_providerinfo].[staffing_rating], 1), '0.0') AS
StaffingRating,
NULL AS
AvgOverallRating,
NULL AS
AvgSurveyRating,
NULL AS
AvgQualityRating,
NULL AS
AvgStaffingRating,
Format(Round([nc_providerinfo].[rnhrd], 1), '0.0') AS
[RN_Staff_Hours],
Format(Round([nc_providerinfo].[pthrd], 1), '0.0') AS
[PT_Staff_Hours],
NULL AS
[LS_ADK_NEED],
NULL AS
[LS_PAIN]
,
NULL AS
[LS_C_PRESS_ULCERS],
NULL AS
[LS_WEIGHT_LOSS],
NULL AS
[LS_B_OR_B_CONTROL_LOSS],
NULL AS
[LS_UTI],
NULL AS
[LS_DEPRESSION_SYMPTS],
NULL AS
[LS_RESTRAINTS],
NULL AS
[LS_1_FALL_C_MAJOR_INJURY],
NULL AS
[LS_FLU_VAC],
NULL AS
[LS_PNEUMO_VAC],
NULL AS
[LS_REC_ANTIPSYCH],
NULL AS
[SS_PAIN]
,
NULL AS
[SS_NEW_OR_WORSE_PRESS_ULC],
NULL AS
[SS_FLU_VAC],
NULL AS
[SS_PNEUMO_VAC],
NULL AS
[SS_NEW_ANTIPSYCH]
FROM [nc_providerinfo]
WHERE [nc_providerinfo].provnum IN (SELECT provnum
FROM #templist)
ORDER BY [nc_providerinfo].[filedate] DESC;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- SELECT '#tblComparisonReport 2', * FROM #tblComparisonReport;
----_ 401
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_adk_need] = Format(Round(
nc_qualitymsr_mds.measure_score_4qtr_avg, 1), '0.0')
FROM nc_qualitymsr_mds
INNER JOIN #tblcomparisonreport
ON nc_qualitymsr_mds.provnum = #tblcomparisonreport.provnum
WHERE ( nc_qualitymsr_mds.msr_cd = '401' );
--SELECT '#tblComparisonReport 2a', * FROM #tblComparisonReport;
----_ 402
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_pain] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '402' )
----_ 403
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_c_press_ulcers] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '403' )
----_ 404
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_weight_loss] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '404' )
----_ 406
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_b_or_b_control_loss] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '406' )
----_ 407
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_uti] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '407' )
----_ 408
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_depression_sympts] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '408' )
----_ 409
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_restraints] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '409' )
----_ 410
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_1_fall_c_major_injury] =
Format(Round([nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '410' )
----_ 411
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_flu_vac] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '411' )
----_ 415
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_pneumo_vac] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '415' )
----_ 419
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_rec_antipsych] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '419' )
----_ 424
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_pain] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '424' )
----_ 425
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_new_or_worse_press_ulc] =
Format(Round([nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '425' )
----_ 426
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_flu_vac] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '426' )
----_ 430
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_pneumo_vac] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '430' )
----_ 434
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_new_antipsych] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '434' )
----_ 451
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_move_indepen_worse] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '451' )
----_ 452
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_antianx_hypno_meds] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '452' )
----_ 471
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_improv_funct] = Format(Round(
[nc_qualitymsr_mds].[measure_score_4qtr_avg], 1), '0.0')
FROM [dbo].[nc_qualitymsr_mds]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_mds].provnum
WHERE ( [nc_qualitymsr_mds].[msr_cd] = '471' )
----_ 523 - Percentage of short-stay residents who were successfully discharged to the community
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_succ_discharg] = Format(Round(
[nc_qualitymsr_claims].[score_adjusted], 1), '0.0')
FROM [dbo].[nc_qualitymsr_claims]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum =
[nc_qualitymsr_claims].provnum
WHERE ( [nc_qualitymsr_claims].[msr_cd] = '523' )
----_ 521 - Percentage of short-stay residents who were rehospitalized after a nursing home admission
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_rehodpitl_nh_admiss] =
Format(Round([nc_qualitymsr_claims].[score_adjusted], 1), '0.0')
FROM [dbo].[nc_qualitymsr_claims]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum = [nc_qualitymsr_claims].provnum
WHERE ( [nc_qualitymsr_claims].[msr_cd] = '521' )
----_ 522 - Percentage of short-stay residents who had an outpatient emergency department visit
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_outpat_er_visit] = Format(Round(
[nc_qualitymsr_claims].[score_adjusted], 1), '0.0')
FROM [dbo].[nc_qualitymsr_claims]
INNER JOIN #tblcomparisonreport
ON #tblcomparisonreport.provnum =
[nc_qualitymsr_claims].provnum
WHERE ( [nc_qualitymsr_claims].[msr_cd] = '522' )
-- SELECT '#tblComparisonReport', * FROM #tblComparisonReport;
CREATE TABLE #tblstatesfoundinthiscorp
(
[provstate] [NVARCHAR](255) NULL,
[statename] [NVARCHAR](255) NULL
)
----_ qryAppendToStatesFoundInThisCorp
INSERT INTO #tblstatesfoundinthiscorp
(provstate,
statename)
SELECT DISTINCT #tblcomparisonreport.provstate,
tblstatenames.performancelabel AS StateName
FROM #tblcomparisonreport
INNER JOIN tblstatenames
ON #tblcomparisonreport.provstate =
tblstatenames.stateabbrev;
CREATE TABLE #tblstateaveragepermeasure
(
[provstate] NVARCHAR(255) NULL,
[performancescoreslabel] NVARCHAR(255) NULL,
[ls_adk_need] NVARCHAR(8) NULL,
[ls_pain] NVARCHAR(8) NULL,
[ls_c_press_ulcers] NVARCHAR(8) NULL,
[ls_weight_loss] NVARCHAR(8) NULL,
[ls_b_or_b_control_loss] NVARCHAR(8) NULL,
[ls_uti] NVARCHAR(8) NULL,
[ls_depression_sympts] NVARCHAR(8) NULL,
[ls_restraints] NVARCHAR(8) NULL,
[ls_1_fall_c_major_injury] NVARCHAR(8) NULL,
[ls_flu_vac] NVARCHAR(8) NULL,
[ls_pneumo_vac] NVARCHAR(8) NULL,
[ls_rec_antipsych] NVARCHAR(8) NULL,
[ss_pain] NVARCHAR(8) NULL,
[ss_new_or_worse_press_ulc] NVARCHAR(8) NULL,
[ss_flu_vac] NVARCHAR(8) NULL,
[ss_pneumo_vac] NVARCHAR(8) NULL,
[ss_new_antipsych] NVARCHAR(8) NULL,
[rn_staff_hours] NVARCHAR(8) NULL,
[pt_staff_hours] NVARCHAR(8) NULL,
[overallrating] NVARCHAR(8) NULL,
[surveyrating] NVARCHAR(8) NULL,
[qualityrating] NVARCHAR(8) NULL,
[staffingrating] NVARCHAR(8) NULL,
[ls_move_indepen_worse] NVARCHAR(8) NULL,
[ls_antianx_hypno_meds] NVARCHAR(8) NULL,
[ss_improv_funct] NVARCHAR(8) NULL,
[ss_succ_discharg] NVARCHAR(8) NULL,
[ss_rehodpitl_nh_admiss] NVARCHAR(8) NULL,
[ss_outpat_er_visit] NVARCHAR(8) NULL
)
----_ the 5 star ratings for STATE
INSERT INTO #tblstateaveragepermeasure
(provstate,
performancescoreslabel,
overallrating,
surveyrating,
qualityrating,
staffingrating,
rn_staff_hours,
pt_staff_hours)
SELECT nc_providerinfo.[state] AS ProvState,
#tblstatesfoundinthiscorp.[statename] AS
PerformanceScoresLabel,
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[overall_rating] AS
DECIMAL
(18, 4)
)), 1), '0.0') AS OverallRating,
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[survey_rating] AS
DECIMAL(
18, 4))
), 1), '0.0') AS SurveyRating,
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[quality_rating] AS
DECIMAL
(18, 4)
)), 1), '0.0') AS QualityRating,
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[staffing_rating] AS
DECIMAL(18, 4))), 1), '0.0') AS StaffingRating,
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[rnhrd] AS
DECIMAL(18, 4)))
, 1),
'0.0') AS
[RN_Staff_Hours],
Format(Round(Avg(Cast([dbo].[nc_providerinfo].[pthrd] AS
DECIMAL(18, 4)))
, 1),
'0.0') AS
[PT_Staff_Hours]
FROM [dbo].[nc_providerinfo]
INNER JOIN #tblstatesfoundinthiscorp
ON [dbo].[nc_providerinfo].[state] =
#tblstatesfoundinthiscorp.[provstate]
GROUP BY [dbo].[nc_providerinfo].[state],
#tblstatesfoundinthiscorp.[statename];
-- SELECT '#tblStateAveragePerMeasure 1', * FROM #tblStateAveragePerMeasure;
----_ insert National Average
INSERT INTO #tblcomparisonreport
(provnum,
provname,
comparisonlabel,
groupsequence,
grouplabel,
overallrating,
surveyrating,
qualityrating,
staffingrating,
[rn_staff_hours],
[pt_staff_hours])
SELECT '99'
AS
provnum,
'National Average'
AS
PROVNAME,
'Comparison to State and National Average'
AS
ComparisonLabel,
99
AS
GroupSequence,
'National Average'
AS
GroupLabel,
Avg(Cast([dbo].[nc_providerinfo].[overall_rating] AS DECIMAL(18, 4)))
AS
OverallRating,
Avg(Cast([dbo].[nc_providerinfo].[survey_rating] AS DECIMAL(18, 4)))
AS
SurveyRating,
Avg(Cast([dbo].[nc_providerinfo].[quality_rating] AS DECIMAL(18, 4)))
AS
QualityRating,
Avg(Cast([dbo].[nc_providerinfo].[staffing_rating] AS DECIMAL(18, 4))
) AS
StaffingRating,
Avg(Cast([dbo].[nc_providerinfo].[rnhrd] AS DECIMAL(18, 4)))
AS
[RN_Staff_Hours],
Avg(Cast([dbo].[nc_providerinfo].[pthrd] AS DECIMAL(18, 4)))
AS
[PT_Staff_Hours]
FROM [dbo].[nc_providerinfo];
-- SELECT '#tblComparisonReport 3', * FROM #tblComparisonReport;
CREATE TABLE #tblstateaverage
(
[groupsequence] [FLOAT] NULL,
[msr_cd] [NVARCHAR](255) NULL,
[state] [NVARCHAR](255) NULL,
[avgmsr] [FLOAT] NULL
)
----_ fill the StateAverages table with fresh data
INSERT INTO #tblstateaverage
SELECT 98 AS GroupSequence,
[dbo].[nc_qualitymsr_mds].[msr_cd],
[dbo].[nc_qualitymsr_mds].[state],
Avg(Cast([dbo].[nc_qualitymsr_mds].[measure_score_4qtr_avg] AS
DECIMAL(18, 4)))
AS AvgMsr
FROM [dbo].[nc_qualitymsr_mds]
GROUP BY [dbo].[nc_qualitymsr_mds].[msr_cd],
[dbo].[nc_qualitymsr_mds].[state],
[dbo].[nc_qualitymsr_mds].[msr_cd]
HAVING (( ( [dbo].[nc_qualitymsr_mds].[state] ) IN (
'PA', 'MD', 'OK', 'NJ', 'FL' ) ));
----_ fill the StateAverages table with fresh data
INSERT INTO #tblstateaverage
SELECT 97
AS
GroupSequence,
[dbo].[nc_qualitymsr_claims].[msr_cd],
[dbo].[nc_qualitymsr_claims].[state],
Avg(Cast([dbo].[nc_qualitymsr_claims].[score_adjusted] AS
DECIMAL(18, 4))
) AS
AvgMsr
FROM [dbo].[nc_qualitymsr_claims]
GROUP BY [dbo].[nc_qualitymsr_claims].[msr_cd],
[dbo].[nc_qualitymsr_claims].[state],
[dbo].[nc_qualitymsr_claims].[msr_cd]
HAVING (( ( [dbo].[nc_qualitymsr_claims].[state] ) IN (
'PA', 'MD', 'OK', 'NJ', 'FL' )
));
--SELECT '#tblStateAverage', * FROM #tblStateAverage;
----_ 401
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_adk_need] = Format(
Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '401';
--SELECT '#tblComparisonReport 2b', * FROM #tblComparisonReport;
----_ 402
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_pain] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '402';
----_ 403
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_c_press_ulcers] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '403';
----_ 404
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_weight_loss] =
Format(Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '404';
----_ 406
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_b_or_b_control_loss] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '406';
----_ 407
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_uti] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '407';
----_ 408
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_depression_sympts] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '408';
----_ 409
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_restraints] =
Format(Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '409';
----_ 410
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_1_fall_c_major_injury] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '410';
----_ 411
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_flu_vac] = Format(
Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '411';
----_ 415
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_pneumo_vac] =
Format(Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '415';
----_ 419
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_rec_antipsych] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '419';
----_ 424
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_pain] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '424';
----_ 425
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_new_or_worse_press_ulc] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '425';
----_ 426
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_flu_vac] = Format(
Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '426';
----_ 430
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_pneumo_vac] =
Format(Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '430';
----_ 434
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_new_antipsych] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '434';
----_ 451 - Percentage of long-stay residents whose ability to move independently worsened
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_move_indepen_worse] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '451';
----_ 452 - Percentage of long-stay residents who received an antianxiety or hypnotic medication
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ls_antianx_hypno_meds] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '452';
----_ 471 - Percentage of short-stay residents who made improvements in function
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_improv_funct] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '471';
----_ 523 - Percentage of short-stay residents who were successfully discharged to the community
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_succ_discharg] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '523';
----_ 521 - Percentage of short-stay residents who were rehospitalized after a nursing home admission
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_rehodpitl_nh_admiss] =
Format(Round(#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '521';
----_ 522 - Percentage of short-stay residents who had an outpatient emergency department visit
UPDATE #tblstateaveragepermeasure
SET #tblstateaveragepermeasure.[ss_outpat_er_visit] = Format(
Round(
#tblstateaverage.avgmsr, 1), '0.0')
FROM #tblstateaverage
INNER JOIN #tblstateaveragepermeasure
ON #tblstateaverage.state =
#tblstateaveragepermeasure.provstate
WHERE [#tblstateaverage].[msr_cd] = '522';
--SELECT '#tblStateAveragePerMeasure', * FROM #tblStateAveragePerMeasure;
----------_________________________________________________________________________________________________________________________________________________________________________________
--------_ NATIONAL AVERAGES FOR QUALITY MSR ___________________
----_ fill the StateAverages table with fresh data
CREATE TABLE #tblnationalaverage
(
[groupsequence] [FLOAT] NULL,
[msr_cd] [NVARCHAR](255) NULL,
[avgmsr] [FLOAT] NULL
)
INSERT INTO #tblnationalaverage
SELECT 99
AS
GroupSequence,
nc_qualitymsr_mds.msr_cd,
Avg(Cast(nc_qualitymsr_mds.measure_score_4qtr_avg AS DECIMAL(18, 4)))
AS
AvgMsr
FROM nc_qualitymsr_mds
GROUP BY nc_qualitymsr_mds.msr_cd;
INSERT INTO #tblnationalaverage
SELECT 99
AS
GroupSequence,
[nc_qualitymsr_claims].msr_cd,
Avg(Cast([nc_qualitymsr_claims].[score_adjusted] AS DECIMAL(18, 4)))
AS
AvgMsr
FROM [nc_qualitymsr_claims]
GROUP BY [nc_qualitymsr_claims].msr_cd;
--SELECT * FROM #tblNationalAverage;
----_ 401
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_adk_need] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '401';
--SELECT '#tblComparisonReport 2c', * FROM #tblComparisonReport;
----_ 402
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_pain] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '402';
----_ 403
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_c_press_ulcers] =
Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '403';
----_ 404
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_weight_loss] = Format(
Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '404';
----_ 406
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_b_or_b_control_loss] = Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '406';
----_ 407
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_uti] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '407';
----_ 408
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_depression_sympts] = Format(
Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '408';
----_ 409
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_restraints] = Format(
Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '409';
----_ 410
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_1_fall_c_major_injury] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '410';
----_ 411
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_flu_vac] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '411';
----_ 415
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_pneumo_vac] = Format(
Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '415';
----_ 419
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_rec_antipsych] =
Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '419';
----_ 424
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_pain] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '424';
----_ 425
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_new_or_worse_press_ulc] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '425';
----_ 426
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_flu_vac] =
Format(Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '426';
----_ 430
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_pneumo_vac] = Format(
Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '430';
----_ 434
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_new_antipsych] =
Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '434';
----_ 451
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_move_indepen_worse] = Format(
Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '451';
----_ 452
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ls_antianx_hypno_meds] = Format(
Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '452';
----_ 471
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_improv_funct] = Format(
Round(#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '471';
----_ 523 - Percentage of short-stay residents who were successfully discharged to the community
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_succ_discharg] =
Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '523';
----_ 521 - Percentage of short-stay residents who were rehospitalized after a nursing home admission
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_rehodpitl_nh_admiss] = Format(Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '521';
----_ 522 - Percentage of short-stay residents who had an outpatient emergency department visit
UPDATE #tblcomparisonreport
SET #tblcomparisonreport.[ss_outpat_er_visit] = Format(
Round(
#tblnationalaverage.avgmsr, 1), '0.0')
FROM #tblnationalaverage
INNER JOIN #tblcomparisonreport
ON #tblnationalaverage.groupsequence =
#tblcomparisonreport.groupsequence
WHERE [#tblnationalaverage].[msr_cd] = '522';
--SELECT * FROM #tblComparisonReport;
CREATE TABLE #tblnataveragepermeasure
(
[ls_adk_need] NVARCHAR(8) NULL,
[ls_pain] NVARCHAR(8) NULL,
[ls_c_press_ulcers] NVARCHAR(8) NULL,
[ls_weight_loss] NVARCHAR(8) NULL,
[ls_b_or_b_control_loss] NVARCHAR(8) NULL,
[ls_uti] NVARCHAR(8) NULL,
[ls_depression_sympts] NVARCHAR(8) NULL,
[ls_restraints] NVARCHAR(8) NULL,
[ls_1_fall_c_major_injury] NVARCHAR(8) NULL,
[ls_flu_vac] NVARCHAR(8) NULL,
[ls_pneumo_vac] NVARCHAR(8) NULL,
[ls_rec_antipsych] NVARCHAR(8) NULL,
[ss_pain] NVARCHAR(8) NULL,
[ss_new_or_worse_press_ulc] NVARCHAR(8) NULL,
[ss_flu_vac] NVARCHAR(8) NULL,
[ss_pneumo_vac] NVARCHAR(8) NULL,
[ss_new_antipsych] NVARCHAR(8) NULL,
[rn_staff_hours] NVARCHAR(8) NULL,
[pt_staff_hours] NVARCHAR(8) NULL,
[overallrating] NVARCHAR(8) NULL,
[surveyrating] NVARCHAR(8) NULL,
[qualityrating] NVARCHAR(8) NULL,
[staffingrating] NVARCHAR(8) NULL,
[ls_move_indepen_worse] NVARCHAR(8) NULL,
[ls_antianx_hypno_meds] NVARCHAR(8) NULL,
[ss_improv_funct] NVARCHAR(8) NULL,
[ss_succ_discharg] NVARCHAR(8) NULL,
[ss_rehodpitl_nh_admiss] NVARCHAR(8) NULL,
[ss_outpat_er_visit] NVARCHAR(8) NULL
);
----_ DoCmd.OpenQuery qryAppendNatAvgToComparisonTable
INSERT INTO #tblnataveragepermeasure
(overallrating,
surveyrating,
qualityrating,
staffingrating,
[rn_staff_hours],
[pt_staff_hours],
[ls_adk_need],
[ls_pain],
[ls_c_press_ulcers],
[ls_weight_loss],
[ls_b_or_b_control_loss],
[ls_uti],
[ls_depression_sympts],
[ls_restraints],
[ls_1_fall_c_major_injury],
[ls_flu_vac],
[ls_pneumo_vac],
[ls_rec_antipsych],
[ss_pain],
[ss_new_or_worse_press_ulc],
[ss_flu_vac],
[ss_pneumo_vac],
[ss_new_antipsych],
[ls_move_indepen_worse],
[ls_antianx_hypno_meds],
[ss_improv_funct],
[ss_succ_discharg],
[ss_rehodpitl_nh_admiss],
[ss_outpat_er_visit])
SELECT #tblcomparisonreport.overallrating,
#tblcomparisonreport.surveyrating,
#tblcomparisonreport.qualityrating,
#tblcomparisonreport.staffingrating,
#tblcomparisonreport.[rn_staff_hours],
#tblcomparisonreport.[pt_staff_hours],
#tblcomparisonreport.[ls_adk_need],
#tblcomparisonreport.[ls_pain],
#tblcomparisonreport.[ls_c_press_ulcers],
#tblcomparisonreport.[ls_weight_loss],
#tblcomparisonreport.[ls_b_or_b_control_loss],
#tblcomparisonreport.[ls_uti],
#tblcomparisonreport.[ls_depression_sympts],
#tblcomparisonreport.[ls_restraints],
#tblcomparisonreport.[ls_1_fall_c_major_injury],
#tblcomparisonreport.[ls_flu_vac],
#tblcomparisonreport.[ls_pneumo_vac],
#tblcomparisonreport.[ls_rec_antipsych],
#tblcomparisonreport.[ss_pain],
#tblcomparisonreport.[ss_new_or_worse_press_ulc],
#tblcomparisonreport.[ss_flu_vac],
#tblcomparisonreport.[ss_pneumo_vac],
#tblcomparisonreport.[ss_new_antipsych],
#tblcomparisonreport.[ls_move_indepen_worse],
#tblcomparisonreport.[ls_antianx_hypno_meds],
#tblcomparisonreport.[ss_improv_funct],
#tblcomparisonreport.[ss_succ_discharg],
#tblcomparisonreport.[ss_rehodpitl_nh_admiss],
#tblcomparisonreport.[ss_outpat_er_visit]
FROM #tblcomparisonreport
WHERE (( ( #tblcomparisonreport.provnum ) = '99' ));
--SELECT * FROM #tblNatAveragePerMeasure;
----_ DoCmd.OpenQuery qryAppendNatAvgToStatePerMsr
INSERT INTO #tblstateaveragepermeasure
(provstate,
performancescoreslabel,
[ls_adk_need],
[ls_pain],
[ls_c_press_ulcers],
[ls_weight_loss],
[ls_b_or_b_control_loss],
[ls_uti],
[ls_depression_sympts],
[ls_restraints],
[ls_1_fall_c_major_injury],
[ls_flu_vac],
[ls_pneumo_vac],
[ls_rec_antipsych],
[ss_pain],
[ss_new_or_worse_press_ulc],
[ss_flu_vac],
[ss_pneumo_vac],
[ss_new_antipsych],
[rn_staff_hours],
[pt_staff_hours],
overallrating,
surveyrating,
qualityrating,
staffingrating,
[ls_move_indepen_worse],
[ls_antianx_hypno_meds],
[ss_improv_funct],
[ss_succ_discharg],
[ss_rehodpitl_nh_admiss],
[ss_outpat_er_visit])
SELECT
'NAT'
AS
ProvState,
'National Average Performance Scores'
AS PerformanceScoresLabel,
Format(Round(#tblnataveragepermeasure.[ls_adk_need], 1), '0.0')
AS Expr1,
Format(Round(#tblnataveragepermeasure.[ls_pain], 1), '0.0')
AS Expr2,
Format(Round(#tblnataveragepermeasure.[ls_c_press_ulcers], 1), '0.0')
AS Expr3,
Format(Round(#tblnataveragepermeasure.[ls_weight_loss], 1), '0.0')
AS Expr4,
Format(Round(#tblnataveragepermeasure.[ls_b_or_b_control_loss], 1), '0.0')
AS
Expr5,
Format(Round(#tblnataveragepermeasure.[ls_uti], 1), '0.0')
AS
Expr6,
Format(Round(#tblnataveragepermeasure.[ls_depression_sympts], 1), '0.0')
AS
Expr7,
Format(Round(#tblnataveragepermeasure.[ls_restraints], 1), '0.0')
AS
Expr8,
Format(Round(#tblnataveragepermeasure.[ls_1_fall_c_major_injury], 1), '0.0')
AS
Expr9,
Format(Round(#tblnataveragepermeasure.[ls_flu_vac], 1), '0.0')
AS
Expr10,
Format(Round(#tblnataveragepermeasure.[ls_pneumo_vac], 1), '0.0')
AS
Expr11,
Format(Round(#tblnataveragepermeasure.[ls_rec_antipsych], 1), '0.0')
AS
Expr12,
Format(Round(#tblnataveragepermeasure.[ss_pain], 1), '0.0')
AS
Expr13,
Format(Round(#tblnataveragepermeasure.[ss_new_or_worse_press_ulc], 1), '0.0') AS
Expr14,
Format(Round(#tblnataveragepermeasure.[ss_flu_vac], 1), '0.0')
AS
Expr15,
Format(Round(#tblnataveragepermeasure.[ss_pneumo_vac], 1), '0.0')
AS
Expr16,
Format(Round(#tblnataveragepermeasure.[ss_new_antipsych], 1), '0.0')
AS
Expr17,
Format(Round(#tblnataveragepermeasure.[rn_staff_hours], 1), '0.0')
AS
Expr18,
Format(Round(#tblnataveragepermeasure.[pt_staff_hours], 1), '0.0')
AS
Expr19,
Format(Round(#tblnataveragepermeasure.[overallrating], 1), '0.0')
AS
Expr20,
Format(Round(#tblnataveragepermeasure.[surveyrating], 1), '0.0')
AS
Expr21,
Format(Round(#tblnataveragepermeasure.[qualityrating], 1), '0.0')
AS
Expr22,
Format(Round(#tblnataveragepermeasure.[staffingrating], 1), '0.0')
AS
Expr23,
Format(Round(#tblnataveragepermeasure.[ls_move_indepen_worse], 1), '0.0')
AS
Expr24,
Format(Round(#tblnataveragepermeasure.[ls_antianx_hypno_meds], 1), '0.0')
AS
Expr25,
Format(Round(#tblnataveragepermeasure.[ss_improv_funct], 1), '0.0')
AS
Expr26,
Format(Round(#tblnataveragepermeasure.[ss_succ_discharg], 1), '0.0')
AS
Expr27,
Format(Round(#tblnataveragepermeasure.[ss_rehodpitl_nh_admiss], 1), '0.0')
AS
Expr28,
Format(Round(#tblnataveragepermeasure.[ss_outpat_er_visit], 1), '0.0')
AS
Expr29
FROM #tblnataveragepermeasure;
DELETE FROM #tblcomparisonreport
WHERE groupsequence IN ( 98, 99 );
-- SELECT '#tblComparisonReport 4', * FROM #tblComparisonReport;
CREATE TABLE #tblconditionalformattingbystate
(
[provnum] [NVARCHAR](255) NULL,
[provname] [NVARCHAR](255) NULL,
[provstate] [NVARCHAR](255) NULL,
[overallrating] [NVARCHAR](255) NULL,
[surveyrating] [NVARCHAR](255) NULL,
[qualityrating] [NVARCHAR](255) NULL,
[staffingrating] [NVARCHAR](255) NULL,
[rn_staff_hours] [NVARCHAR](255) NULL,
[pt_staff_hours] [NVARCHAR](255) NULL,
[ls_adk_need] [NVARCHAR](255) NULL,
[ls_pain] [NVARCHAR](255) NULL,
[ls_c_press_ulcers] [NVARCHAR](255) NULL,
[ls_weight_loss] [NVARCHAR](255) NULL,
[ls_b_or_b_control_loss] [NVARCHAR](255) NULL,
[ls_uti] [NVARCHAR](255) NULL,
[ls_depression_sympts] [NVARCHAR](255) NULL,
[ls_restraints] [NVARCHAR](255) NULL,
[ls_1_fall_c_major_injury] [NVARCHAR](255) NULL,
[ls_flu_vac] [NVARCHAR](255) NULL,
[ls_pneumo_vac] [NVARCHAR](255) NULL,
[ls_rec_antipsych] [NVARCHAR](255) NULL,
[ss_pain] [NVARCHAR](255) NULL,
[ss_new_or_worse_press_ulc] [NVARCHAR](255) NULL,
[ss_flu_vac] [NVARCHAR](255) NULL,
[ss_pneumo_vac] [NVARCHAR](255) NULL,
[ss_new_antipsych] [NVARCHAR](255) NULL,
[ls_move_indepen_worse] [NVARCHAR](255) NULL,
[ls_antianx_hypno_meds] [NVARCHAR](255) NULL,
[ss_improv_funct] [NVARCHAR](255) NULL,
[ss_succ_discharg] [NVARCHAR](255) NULL,
[ss_rehodpitl_nh_admiss] [NVARCHAR](255) NULL,
[ss_outpat_er_visit] [NVARCHAR](255) NULL
);
-- SELECT '#tblComparisonReport 5', * FROM #tblComparisonReport;
INSERT INTO #tblconditionalformattingbystate
(provnum,
provname,
provstate,
overallrating,
surveyrating,
qualityrating,
staffingrating,
[ls_adk_need],
[ls_pain],
[ls_c_press_ulcers],
[ls_weight_loss],
[ls_b_or_b_control_loss],
[ls_uti],
[ls_depression_sympts],
[ls_restraints],
[ls_1_fall_c_major_injury],
[ls_flu_vac],
[ls_pneumo_vac],
[ls_rec_antipsych],
[ss_pain],
[ss_new_or_worse_press_ulc],
[ss_flu_vac],
[ss_pneumo_vac],
[ss_new_antipsych],
[ls_move_indepen_worse],
[ls_antianx_hypno_meds],
[ss_improv_funct],
[ss_succ_discharg],
[ss_rehodpitl_nh_admiss],
[ss_outpat_er_visit])
SELECT #tblcomparisonreport.provnum,
#tblcomparisonreport.provname,
'#ffffff',
Iif([#tblcomparisonreport].[overallrating] >=
[#tblstateaveragepermeasure].[overallrating], '00FF00', 'FCE6D4')
AS
OverallRating,
Iif([#tblcomparisonreport].[surveyrating] >=
[#tblstateaveragepermeasure].[surveyrating], '00FF00', 'FCE6D4')
AS
SurveyRating,
Iif([#tblcomparisonreport].[qualityrating] >=
[#tblstateaveragepermeasure].[qualityrating], '00FF00', 'FCE6D4')
AS
QualityRating,
Iif([#tblcomparisonreport].[staffingrating] >=
[#tblstateaveragepermeasure].[staffingrating], '00FF00', 'FCE6D4'
)
AS
StaffingRating,
Iif([#tblcomparisonreport].[ls_adk_need] <=
[#tblstateaveragepermeasure].[ls_adk_need], '00FF00', 'FCE6D4')
AS
[LS_ADK_NEED],
Iif([#tblcomparisonreport].[ls_pain] <=
[#tblstateaveragepermeasure].[ls_pain],
'00FF00', 'FCE6D4')
AS [LS_PAIN],
Iif([#tblcomparisonreport].[ls_c_press_ulcers] <=
[#tblstateaveragepermeasure].[ls_c_press_ulcers], '00FF00',
'FCE6D4')
AS
[LS_C_PRESS_ULCERS],
Iif([#tblcomparisonreport].[ls_weight_loss] <=
[#tblstateaveragepermeasure].[ls_weight_loss], '00FF00', 'FCE6D4'
)
AS
[LS_WEIGHT_LOSS],
Iif([#tblcomparisonreport].[ls_b_or_b_control_loss] <=
[#tblstateaveragepermeasure].[ls_b_or_b_control_loss], '00FF00',
'FCE6D4')
AS [LS_B_OR_B_CONTROL_LOSS],
Iif([#tblcomparisonreport].[ls_uti] <=
[#tblstateaveragepermeasure].[ls_uti],
'00FF00', 'FCE6D4')
AS [LS_UTI],
Iif([#tblcomparisonreport].[ls_depression_sympts] <=
[#tblstateaveragepermeasure].[ls_depression_sympts], '00FF00',
'FCE6D4') AS
[LS_DEPRESSION_SYMPTS],
Iif([#tblcomparisonreport].[ls_restraints] <=
[#tblstateaveragepermeasure].[ls_restraints], '00FF00', 'FCE6D4')
AS
[LS_RESTRAINTS],
Iif([#tblcomparisonreport].[ls_1_fall_c_major_injury] <=
[#tblstateaveragepermeasure].[ls_1_fall_c_major_injury], '00FF00'
,
'FCE6D4')
AS [LS_1_FALL_C_MAJOR_INJURY],
Iif([#tblcomparisonreport].[ls_flu_vac] >=#
tblstateaveragepermeasure.[LS_FLU_VAC], '00FF00', 'FCE6D4')
AS [LS_FLU_VAC],
Iif([#tblcomparisonreport].[ls_pneumo_vac] >=#
tblstateaveragepermeasure.[LS_PNEUMO_VAC], '00FF00', 'FCE6D4')
AS
[LS_PNEUMO_VAC],
Iif([#tblcomparisonreport].[ls_rec_antipsych] <=
[#tblstateaveragepermeasure].[ls_rec_antipsych], '00FF00',
'FCE6D4')
AS
[LS_REC_ANTIPSYCH],
Iif([#tblcomparisonreport].[ss_pain] <=
[#tblstateaveragepermeasure].[ss_pain],
'00FF00', 'FCE6D4')
AS [SS_PAIN],
Iif([#tblcomparisonreport].[ss_new_or_worse_press_ulc] <=
[#tblstateaveragepermeasure].[ss_new_or_worse_press_ulc],
'00FF00',
'FCE6D4'
)
AS [SS_NEW_OR_WORSE_PRESS_ULC],
Iif([#tblcomparisonreport].[ss_flu_vac] >=#
tblstateaveragepermeasure.[SS_FLU_VAC], '00FF00', 'FCE6D4')
AS [SS_FLU_VAC],
Iif([#tblcomparisonreport].[ss_pneumo_vac] >=#
tblstateaveragepermeasure.[SS_PNEUMO_VAC], '00FF00', 'FCE6D4')
AS
[SS_PNEUMO_VAC],
Iif([#tblcomparisonreport].[ss_new_antipsych] <=
[#tblstateaveragepermeasure].[ss_new_antipsych], '00FF00',
'FCE6D4')
AS
[SS_NEW_ANTIPSYCH],
Iif([#tblcomparisonreport].[ls_move_indepen_worse] <=
[#tblstateaveragepermeasure].[ls_move_indepen_worse], '00FF00',
'FCE6D4') AS
[LS_MOVE_INDEPEN_WORSE],
Iif([#tblcomparisonreport].[ls_antianx_hypno_meds] <=
[#tblstateaveragepermeasure].[ls_antianx_hypno_meds], '00FF00',
'FCE6D4') AS
[LS_ANTIANX_HYPNO_MEDS],
Iif([#tblcomparisonreport].[ss_improv_funct] <=
[#tblstateaveragepermeasure].[ss_improv_funct], '00FF00',
'FCE6D4')
AS
[SS_IMPROV_FUNCT],
Iif([#tblcomparisonreport].[ss_succ_discharg] <=
[#tblstateaveragepermeasure].[ss_succ_discharg], '00FF00',
'FCE6D4')
AS
[SS_SUCC_DISCHARG],
Iif([#tblcomparisonreport].[ss_rehodpitl_nh_admiss] <=
[#tblstateaveragepermeasure].[ss_rehodpitl_nh_admiss], '00FF00',
'FCE6D4')
AS [SS_REHODPITL_NH_ADMISS],
Iif([#tblcomparisonreport].[ss_outpat_er_visit] <=
[#tblstateaveragepermeasure].[ss_outpat_er_visit], '00FF00',
'FCE6D4'
) AS
[SS_OUTPAT_ER_VISIT]
FROM #tblcomparisonreport
INNER JOIN #tblstateaveragepermeasure
ON #tblcomparisonreport.provstate =
#tblstateaveragepermeasure.provstate;
-- SELECT * FROM #tblConditionalFormattingByState;
CREATE TABLE #tblconditionalformattingmarketavginterim
(
[provnum] NVARCHAR(255) NULL,
[provstate] NVARCHAR(255) NULL,
[avgoverallrating] NVARCHAR(8) NULL,
[avgsurveyrating] NVARCHAR(8) NULL,
[avgqualityrating] NVARCHAR(8) NULL,
[avgstaffingrating] NVARCHAR(8) NULL,
[avgrn_staff_hours] NVARCHAR(8) NULL,
[avgpt_staff_hours] NVARCHAR(8) NULL,
[avgls_adk_need] NVARCHAR(8) NULL,
[avgls_pain] NVARCHAR(8) NULL,
[avgls_c_press_ulcers] NVARCHAR(8) NULL,
[avgls_weight_loss] NVARCHAR(8) NULL,
[avgls_b_or_b_control_loss] NVARCHAR(8) NULL,
[avgls_uti] NVARCHAR(8) NULL,
[avgls_depression_sympts] NVARCHAR(8) NULL,
[avgls_restraints] NVARCHAR(8) NULL,
[avgls_1_fall_c_major_injury] NVARCHAR(8) NULL,
[avgls_flu_vac] NVARCHAR(8) NULL,
[avgls_pneumo_vac] NVARCHAR(8) NULL,
[avgls_rec_antipsych] NVARCHAR(8) NULL,
[avgss_pain] NVARCHAR(8) NULL,
[avgss_new_or_worse_press_ulc] NVARCHAR(8) NULL,
[avgss_flu_vac] NVARCHAR(8) NULL,
[avgss_pneumo_vac] NVARCHAR(8) NULL,
[avgss_new_antipsych] NVARCHAR(8) NULL,
[avgls_move_indepen_worse] NVARCHAR(8) NULL,
[avgls_antianx_hypno_meds] NVARCHAR(8) NULL,
[avgss_improv_funct] NVARCHAR(8) NULL,
[avgss_succ_discharg] NVARCHAR(8) NULL,
[avgss_rehodpitl_nh_admiss] NVARCHAR(8) NULL,
[avgss_outpat_er_visit] NVARCHAR(8) NULL
);
INSERT INTO #tblconditionalformattingmarketavginterim
(provnum,
provstate,
avgoverallrating,
avgsurveyrating,
avgqualityrating,
avgstaffingrating,
[avgls_adk_need],
[avgls_pain],
[avgls_c_press_ulcers],
[avgls_weight_loss],
[avgls_b_or_b_control_loss],
[avgls_uti],
[avgls_depression_sympts],
[avgls_restraints],
[avgls_1_fall_c_major_injury],
[avgls_flu_vac],
[avgls_pneumo_vac],
[avgls_rec_antipsych],
[avgss_pain],
[avgss_new_or_worse_press_ulc],
[avgss_flu_vac],
[avgss_pneumo_vac],
[avgss_new_antipsych],
[avgls_move_indepen_worse],
[avgls_antianx_hypno_meds],
[avgss_improv_funct],
[avgss_succ_discharg],
[avgss_rehodpitl_nh_admiss],
[avgss_outpat_er_visit])
SELECT '',
#tblcomparisonreport.provstate,
Format(Round(Avg(Cast(#tblcomparisonreport.[overallrating] AS DECIMAL
(18,
4))
+ 0.05), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[surveyrating] AS DECIMAL(
18,
4))
+ 0.05), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[qualityrating] AS DECIMAL
(18,
4))
+ 0.05), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[staffingrating] AS
DECIMAL(18
, 4))
+ 0.05), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_adk_need] AS
DECIMAL(18, 4))), 1)
, '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_pain] AS
DECIMAL(18, 4))),
1),
'0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_c_press_ulcers] AS
DECIMAL
(18, 4)
)), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_weight_loss] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_b_or_b_control_loss]
AS
DECIMAL(
18, 4)))
, 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_uti] AS DECIMAL(18, 4)
)),
1),
'0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_depression_sympts] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_restraints] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_1_fall_c_major_injury]
AS
DECIMAL
(18, 4)
)), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_flu_vac] AS
DECIMAL(18, 4)
)), 1),
'0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_pneumo_vac] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_rec_antipsych] AS
DECIMAL(
18, 4))
), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_pain] AS
DECIMAL(18, 4))),
1),
'0.0'),
Format(Round(Avg(Cast(
#tblcomparisonreport.[ss_new_or_worse_press_ulc] AS
DECIMAL(18, 4))), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_flu_vac] AS
DECIMAL(18, 4)
)), 1),
'0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_pneumo_vac] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_new_antipsych] AS
DECIMAL(
18, 4))
), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_move_indepen_worse] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ls_antianx_hypno_meds] AS
DECIMAL(18, 4))),
1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_improv_funct] AS
DECIMAL(
18, 4)))
, 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_succ_discharg] AS
DECIMAL(
18, 4))
), 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_rehodpitl_nh_admiss]
AS
DECIMAL(
18, 4)))
, 1), '0.0'),
Format(Round(Avg(Cast(#tblcomparisonreport.[ss_outpat_er_visit] AS
DECIMAL(18, 4))), 1), '0.0')
FROM #tblcomparisonreport
INNER JOIN #tblstateaveragepermeasure
ON #tblcomparisonreport.provstate =
#tblstateaveragepermeasure.provstate
GROUP BY #tblcomparisonreport.provstate;
--SELECT '#tblConditionalFormattingMarketAvgInterim', * FROM #tblConditionalFormattingMarketAvgInterim;
CREATE TABLE #tblconditionalformattingmarketavg
(
[provnum] [NVARCHAR](255) NULL,
[provname] [NVARCHAR](255) NULL,
[provstate] [NVARCHAR](255) NULL,
[avgoverallrating] [NVARCHAR](255) NULL,
[avgsurveyrating] [NVARCHAR](255) NULL,
[avgqualityrating] [NVARCHAR](255) NULL,
[avgstaffingrating] [NVARCHAR](255) NULL,
[avgrn_staff_hours] [NVARCHAR](255) NULL,
[avgpt_staff_hours] [NVARCHAR](255) NULL,
[avgls_adk_need] [NVARCHAR](255) NULL,
[avgls_pain] [NVARCHAR](255) NULL,
[avgls_c_press_ulcers] [NVARCHAR](255) NULL,
[avgls_weight_loss] [NVARCHAR](255) NULL,
[avgls_b_or_b_control_loss] [NVARCHAR](255) NULL,
[avgls_uti] [NVARCHAR](255) NULL,
[avgls_depression_sympts] [NVARCHAR](255) NULL,
[avgls_restraints] [NVARCHAR](255) NULL,
[avgls_1_fall_c_major_injury] [NVARCHAR](255) NULL,
[avgls_flu_vac] [NVARCHAR](255) NULL,
[avgls_pneumo_vac] [NVARCHAR](255) NULL,
[avgls_rec_antipsych] [NVARCHAR](255) NULL,
[avgss_pain] [NVARCHAR](255) NULL,
[avgss_new_or_worse_press_ulc] [NVARCHAR](255) NULL,
[avgss_flu_vac] [NVARCHAR](255) NULL,
[avgss_pneumo_vac] [NVARCHAR](255) NULL,
[avgss_new_antipsych] [NVARCHAR](255) NULL,
[avgls_move_indepen_worse] [NVARCHAR](255) NULL,
[avgls_antianx_hypno_meds] [NVARCHAR](255) NULL,
[avgss_improv_funct] [NVARCHAR](255) NULL,
[avgss_succ_discharg] [NVARCHAR](255) NULL,
[avgss_rehodpitl_nh_admiss] [NVARCHAR](255) NULL,
[avgss_outpat_er_visit] [NVARCHAR](255) NULL
)
INSERT INTO #tblconditionalformattingmarketavg
(provname,
provstate,
avgoverallrating,
avgsurveyrating,
avgqualityrating,
avgstaffingrating,
[avgls_adk_need],
[avgls_pain],
[avgls_c_press_ulcers],
[avgls_weight_loss],
[avgls_b_or_b_control_loss],
[avgls_uti],
[avgls_depression_sympts],
[avgls_restraints],
[avgls_1_fall_c_major_injury],
[avgls_flu_vac],
[avgls_pneumo_vac],
[avgls_rec_antipsych],
[avgss_pain],
[avgss_new_or_worse_press_ulc],
[avgss_flu_vac],
[avgss_pneumo_vac],
[avgss_new_antipsych],
[avgls_move_indepen_worse],
[avgls_antianx_hypno_meds],
[avgss_improv_funct],
[avgss_succ_discharg],
[avgss_rehodpitl_nh_admiss],
[avgss_outpat_er_visit])
SELECT '#ffffff',
#tblstateaveragepermeasure.provstate,
Iif([#tblconditionalformattingmarketavginterim].[avgoverallrating] >=
[#tblstateaveragepermeasure].[overallrating], '00FF00', 'FCE6D4')
AS
AvgOverallRating,
Iif([#tblconditionalformattingmarketavginterim].[avgsurveyrating] >=
[#tblstateaveragepermeasure].[surveyrating], '00FF00', 'FCE6D4')
AS
AvgSurveyRating,
Iif([#tblconditionalformattingmarketavginterim].[avgqualityrating] >=
[#tblstateaveragepermeasure].[qualityrating], '00FF00', 'FCE6D4')
AS
AvgQualityRating,
Iif([#tblconditionalformattingmarketavginterim].[avgstaffingrating]
>=
[#tblstateaveragepermeasure].[staffingrating], '00FF00', 'FCE6D4'
)
AS
AvgStaffingRating,
Iif(#tblconditionalformattingmarketavginterim.[avgls_adk_need] <=
#tblstateaveragepermeasure.[ls_adk_need], '00FF00', 'FCE6D4')
AS
[AvgLS_ADK_NEED],
Iif(#tblconditionalformattingmarketavginterim.[avgls_pain] <=
#tblstateaveragepermeasure.[ls_pain], '00FF00', 'FCE6D4')
AS [AvgLS_PAIN],
Iif(#tblconditionalformattingmarketavginterim.[avgls_c_press_ulcers]
<=
#tblstateaveragepermeasure.[ls_c_press_ulcers], '00FF00',
'FCE6D4')
AS
[AvgLS_C_PRESS_ULCERS],
Iif(#tblconditionalformattingmarketavginterim.[avgls_weight_loss] <=
#tblstateaveragepermeasure.[ls_weight_loss], '00FF00', 'FCE6D4')
AS
[AvgLS_WEIGHT_LOSS],
Iif(
#tblconditionalformattingmarketavginterim.[avgls_b_or_b_control_loss]
<=
#tblstateaveragepermeasure.[ls_b_or_b_control_loss], '00FF00',
'FCE6D4') AS
[AvgLS_B_OR_B_CONTROL_LOSS],
Iif(#tblconditionalformattingmarketavginterim.[avgls_uti] <=
#tblstateaveragepermeasure.[ls_uti], '00FF00', 'FCE6D4')
AS [AvgLS_UTI],
Iif(
#tblconditionalformattingmarketavginterim.[avgls_depression_sympts]
<=
#tblstateaveragepermeasure.[ls_depression_sympts], '00FF00', 'FCE6D4'
) AS
[AvgLS_DEPRESSION_SYMPTS],
Iif(#tblconditionalformattingmarketavginterim.[avgls_restraints] <=
#tblstateaveragepermeasure.[ls_restraints], '00FF00', 'FCE6D4')
AS
[AvgLS_RESTRAINTS],
Iif(
#tblconditionalformattingmarketavginterim.[avgls_1_fall_c_major_injury] <=
#tblstateaveragepermeasure.[ls_1_fall_c_major_injury], '00FF00', 'FCE6D4')
AS
[AvgLS_1_FALL_C_MAJOR_INJURY],
Iif(#tblconditionalformattingmarketavginterim.[avgls_flu_vac] >=
#tblstateaveragepermeasure.[ls_flu_vac], '00FF00', 'FCE6D4')
AS
[AvgLS_FLU_VAC],
Iif(#tblconditionalformattingmarketavginterim.[avgls_pneumo_vac] >=
#tblstateaveragepermeasure.[ls_pneumo_vac], '00FF00', 'FCE6D4')
AS
[AvgLS_PNEUMO_VAC],
Iif(#tblconditionalformattingmarketavginterim.[avgls_rec_antipsych]
<=
#tblstateaveragepermeasure.[ls_rec_antipsych], '00FF00', 'FCE6D4'
)
AS
[AvgLS_REC_ANTIPSYCH],
Iif(#tblconditionalformattingmarketavginterim.[avgss_pain] <=
#tblstateaveragepermeasure.[ss_pain], '00FF00', 'FCE6D4')
AS [AvgSS_PAIN],
Iif(
#tblconditionalformattingmarketavginterim.[avgss_new_or_worse_press_ulc] <=
#tblstateaveragepermeasure.[ss_new_or_worse_press_ulc], '00FF00', 'FCE6D4')
AS
[AvgSS_NEW_OR_WORSE_PRESS_ULC],
Iif(#tblconditionalformattingmarketavginterim.[avgss_flu_vac] >=
#tblstateaveragepermeasure.[ss_flu_vac], '00FF00', 'FCE6D4')
AS
[AvgSS_FLU_VAC],
Iif(#tblconditionalformattingmarketavginterim.[avgss_pneumo_vac] >=
#tblstateaveragepermeasure.[ss_pneumo_vac], '00FF00', 'FCE6D4')
AS
[AvgSS_PNEUMO_VAC],
Iif(#tblconditionalformattingmarketavginterim.[avgss_new_antipsych]
<=
#tblstateaveragepermeasure.[ss_new_antipsych], '00FF00', 'FCE6D4'
)
AS
[AvgSS_NEW_ANTIPSYCH],
Iif(
#tblconditionalformattingmarketavginterim.[avgls_move_indepen_worse]
<=
#tblstateaveragepermeasure.[ls_move_indepen_worse], '00FF00',
'FCE6D4') AS
[AvgLS_MOVE_INDEPEN_WORSE],
Iif(
#tblconditionalformattingmarketavginterim.[avgls_antianx_hypno_meds]
<=
#tblstateaveragepermeasure.[ls_antianx_hypno_meds], '00FF00',
'FCE6D4') AS
[AvgLS_ANTIANX_HYPNO_MEDS],
Iif(#tblconditionalformattingmarketavginterim.[avgss_improv_funct] <=
#tblstateaveragepermeasure.[ss_improv_funct], '00FF00', 'FCE6D4')
AS
[AvgSS_IMPROV_FUNCT],
Iif(#tblconditionalformattingmarketavginterim.[avgss_succ_discharg]
<=
#tblstateaveragepermeasure.[ss_succ_discharg], '00FF00', 'FCE6D4'
)
AS
[AvgSS_SUCC_DISCHARG],
Iif(
#tblconditionalformattingmarketavginterim.[avgss_rehodpitl_nh_admiss]
<=
#tblstateaveragepermeasure.[ss_rehodpitl_nh_admiss], '00FF00',
'FCE6D4') AS
[AvgSS_REHODPITL_NH_ADMISS],
Iif(#tblconditionalformattingmarketavginterim.[avgss_outpat_er_visit]
<=
#tblstateaveragepermeasure.[ss_outpat_er_visit], '00FF00',
'FCE6D4')
AS
[AvgSS_OUTPAT_ER_VISIT]
FROM #tblconditionalformattingmarketavginterim
INNER JOIN #tblstateaveragepermeasure
ON #tblconditionalformattingmarketavginterim.provstate =
#tblstateaveragepermeasure.provstate;
-- tblStateAveragePerMeasure subreport
SELECT '#tblStateAveragePerMeasure',
'' AS provnum,
#tblstateaveragepermeasure.provstate,
#tblstateaveragepermeasure.performancescoreslabel,
#tblstateaveragepermeasure.[ls_adk_need],
#tblstateaveragepermeasure.[ls_pain],
#tblstateaveragepermeasure.[ls_c_press_ulcers],
#tblstateaveragepermeasure.[ls_weight_loss],
#tblstateaveragepermeasure.[ls_b_or_b_control_loss],
#tblstateaveragepermeasure.[ls_uti],
#tblstateaveragepermeasure.[ls_depression_sympts],
#tblstateaveragepermeasure.[ls_restraints],
#tblstateaveragepermeasure.[ls_1_fall_c_major_injury],
#tblstateaveragepermeasure.[ls_flu_vac],
#tblstateaveragepermeasure.[ls_pneumo_vac],
#tblstateaveragepermeasure.[ls_rec_antipsych],
#tblstateaveragepermeasure.[ss_pain],
#tblstateaveragepermeasure.[ss_new_or_worse_press_ulc],
#tblstateaveragepermeasure.[ss_flu_vac],
#tblstateaveragepermeasure.[ss_pneumo_vac],
#tblstateaveragepermeasure.[ss_new_antipsych],
#tblstateaveragepermeasure.[rn_staff_hours],
#tblstateaveragepermeasure.[pt_staff_hours],
#tblstateaveragepermeasure.overallrating,
#tblstateaveragepermeasure.surveyrating,
#tblstateaveragepermeasure.qualityrating,
#tblstateaveragepermeasure.staffingrating,
#tblstateaveragepermeasure.[ls_move_indepen_worse],
#tblstateaveragepermeasure.[ls_antianx_hypno_meds],
#tblstateaveragepermeasure.[ss_improv_funct],
#tblstateaveragepermeasure.[ss_succ_discharg],
#tblstateaveragepermeasure.[ss_rehodpitl_nh_admiss],
#tblstateaveragepermeasure.[ss_outpat_er_visit]
FROM #tblstateaveragepermeasure;
SELECT '#tblConditionalFormattingMarketAvgInterim',
*
FROM #tblconditionalformattingmarketavginterim;
SELECT '#tblConditionalFormattingByState',
*
FROM #tblconditionalformattingbystate;
SELECT '#tblConditionalFormattingMarketAvg',
*
FROM #tblconditionalformattingmarketavg;
SELECT '#tblComparisonReport',
*
FROM #tblcomparisonreport;
END 
Vitor Montalvão
After running the SP?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.