Solved

How to select/populate table with most current records

Posted on 2016-10-06
2
56 Views
Last Modified: 2016-10-11
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

0
Comment
Question by:mburk1968
2 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I want to grab the most current records based on the FileDate.
After running the SP?
0
 
LVL 12

Accepted Solution

by:
Arifhusen Ansari earned 500 total points
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now