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  

Open in new window

mburk1968Asked:
Who is Participating?
 
Arifhusen AnsariConnect With a Mentor Business Intelligence Developer and AnalystCommented:
Hi,

I have gone through the Stored Procedure code that you have sent.

you have mentioned that you have some issue in the query where you are fetching the data from
NC_providerInfo table and you want the latest data for the provnum.

Below is the query in which you are observing the issue.

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 

Open in new window



What I understood from your explanation is you want the latest data for provnum from the table nc_providerinfo.

may be you are considering the data as latest based on filedate column

Try the below code. It might resolve your issue.

; WITH CTE_Data_With_LatestSeq

AS

(
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]
, ROW_NUMBER() OVER (PARTITION BY [nc_providerinfo].provnum ORDER BY [nc_providerinfo].[filedate] DESC) AS LatestSeq
FROM   [nc_providerinfo]
WHERE  [nc_providerinfo].provnum IN (SELECT provnum
                     FROM   #templist)

) 




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 

SELECT 
[nc_providerinfo].provnum,
ComparisonLabel,

GroupSequence,

GroupLabel,
[nc_providerinfo].provname,
[nc_providerinfo].[address],
ProvAddr2
,
ProvAddr3,
[nc_providerinfo].city,
[nc_providerinfo].state,
[nc_providerinfo].zip,
[nc_providerinfo].[county_name],
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] 

FROM 

CTE_Data_With_LatestSeq

WHERE LatestSeq =1 --- This Condition will give you the latest data for the  provnum

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I want to grab the most current records based on the FileDate.
After running the SP?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.