Formatting issue -- uppercasing non-keywords

• 
  Hi dbrinkerhoff,
  
  Firstly, could I check which version of Prompt you're using?
  
  Name is considered a "semi-reserved" keyword as it can be a keyword depending on the context (such as in ALTER DATABASE MODIFY NAME or inside a backup statement). Would it be possible to provide us with the script where the error occurred? I'm wondering if Prompt has mis-parsed the script and thought that it was meant to be a keyword in that specific context?
  
  Thanks,
  Aaron.

  Aaron L January 26, 2015 04:49

  - 0 +
• 
  The version is 6.4.0.675, although I am going to try updating and see if that makes a difference. I don't expect it to, because someone else here at my workplace is using a later version, and the bug still happens for him.
  
  There are several different scripts that are producing similar errors. They depend on tables created in other scripts, and they are not short. I will give you this one, which is 613 lines. Reformatting only changes the case of the second instance of IPP.Name to IPP.NAME, and only when formatted in "master" as opposed to any other database. I will try to find a better example.
  

  /********************************************************************************************/
  /*  CMS 169 (NQF 0110)			                                                            */
  /*  Bipolar Disorder and Major Depression: Appraisal for alcohol or chemical substance use  */
  /*	$Revision: 8785 $																		*/
  /*	$Date: 2015-01-23 13:10:14 -0600 (Fri, 23 Jan 2015) $									*/
  /********************************************************************************************/
  
  IF OBJECT_ID(N'dbo.mpmzcus_2014_CMS_169', N'P') IS NOT NULL
      DROP PROCEDURE dbo.mpmzcus_2014_CMS_169;
  GO
  
  CREATE PROCEDURE dbo.mpmzcus_2014_CMS_169
      @ReportRunTime DATETIME
     ,@FromDate DATETIME
     ,@ThruDate DATETIME
     ,@Submission_ProviderID VARCHAR(MAX)
     ,@Detail CHAR(1)
     ,@QRDA1 CHAR(1)
     ,@QRDA3 CHAR(1)
  AS
      SET NOCOUNT ON              -- Stops the message that shows the count of rows for faster processing
      SET ANSI_NULLS ON           -- Ensures ISO-compliant behavior of = and <> when used with null values
      SET QUOTED_IDENTIFIER ON    -- Ensures ISO-compliant behavior regarding quotation mark delimiting identifiers and literal strings
      
      DECLARE
          @LocalReportRunTime AS DATETIME
         ,@CMSMeasureID AS VARCHAR(3)
         ,@LocalFromDate AS DATETIME
         ,@LocalThruDate AS DATETIME
         ,@ProviderID AS VARCHAR(MAX)
         ,@FromDateNoTime AS DATETIME
         ,@ThruDateNoTime AS DATETIME;
  
      SET @LocalReportRunTime = @ReportRunTime;
      SET @CMSMeasureID = '169';
      SET @LocalFromDate = @FromDate;
      SET @LocalThruDate = @ThruDate;
      SET @ProviderID = @Submission_ProviderID;
      SET @FromDateNoTime = DATEADD(dd, DATEDIFF(dd, 0, @LocalFromDate), 0);
      SET @ThruDateNoTime = DATEADD(dd, DATEDIFF(dd, 0, @LocalThruDate), 0);
         
      CREATE TABLE dbo.#ProviderIDs ( ProviderID VARCHAR(256) );
  	
      INSERT  INTO dbo.#ProviderIDs
              (
               ProviderID
  			)
              SELECT
  				DISTINCT
                  CAST(Items AS VARCHAR(256))
              FROM
                  dbo.mpm_fn_CommaDelimiter(@ProviderID);
  
  /**************************************/
  /*  BUILD INITIAL PATIENT POPULATION  */
  /**************************************/
  
      INSERT  INTO dbo.mpmzcus_2014_Population
              SELECT
                  @LocalReportRunTime
                 ,@CMSMeasureID
                 ,PP.MriPatientID
              FROM
                  dbo.mpmzcus_2014_PbrPatientsOverEqual18 AS PP;
  
      SELECT
          PP.MriPatientID
         ,PP.Name
         ,PP.Sex
      INTO
          dbo.#Patients
      FROM
          dbo.mpmzcus_2014_PbrPatientsOverEqual18 AS PP;
  
      SELECT
          P.MriPatientID
         ,P.Name
         ,P.Sex
         ,MIS.ProviderID
      INTO
          dbo.#Population
      FROM
          dbo.#Patients AS P
          CROSS JOIN dbo.mpmzcus_2014_MisProviders AS MIS
      WHERE
          @ProviderID = 'ALL'
          OR MIS.ProviderID IN ( SELECT
                                  ProviderID
                                 FROM
                                  dbo.#ProviderIDs );
  
      DROP TABLE dbo.#Patients;
  
  /********************************/
  /*  SUPPLEMENTAL DATA ELEMENTS  */
  /********************************/
  
      SELECT
          Ethnicity.MriPatientID
         ,Ethnicity.EthnicityID
         ,Ethnicity.Code
         ,Ethnicity.CodeSystemName
      INTO
          dbo.#Ethnicity
      FROM
          dbo.mpm_fn_2014_Ethnicity(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.114222.4.11.837') AS Ethnicity;
    
      SELECT
          Payer.MriPatientID
         ,Payer.PayerID
         ,Payer.Code
         ,Payer.CodeSystemName
      INTO
          dbo.#Payer
      FROM
          dbo.mpm_fn_2014_Payer(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.114222.4.11.3591') AS Payer;
                        
      SELECT
          Race.MriPatientID
         ,Race.RaceID
         ,Race.Code
         ,Race.CodeSystemName
      INTO
          dbo.#Race
      FROM
          dbo.mpm_fn_2014_Race(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.114222.4.11.836') AS Race;
  
  /******************/
  /*  BUILD TABLES  */
  /******************/
  
      SELECT
          EncounterPerformed.MriPatientID
         ,EncounterPerformed.Name
         ,EncounterPerformed.Sex
         ,EncounterPerformed.ProviderID
         ,EncounterPerformed.[DateTime] AS 'EncDateTime'
      INTO
          dbo.#BH_OutpatientEncounter
      FROM
          dbo.mpm_fn_2014_PBRTransactions(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.464.1.49') AS EncounterPerformed
      WHERE
          (
            @ProviderID = 'ALL'
            OR EncounterPerformed.ProviderID IN ( SELECT
                                                      ProviderID
                                                  FROM
                                                      dbo.#ProviderIDs )
          );
  
      SELECT
          RXMOrders.MriPatientID
         ,RXMOrders.LowTime AS 'DateTime'
      INTO
          dbo.#BH_EncsAndProcs
      FROM
          dbo.mpm_fn_2014_RXMOrders(@LocalReportRunTime, @CMSMeasureID,
                                    '2.16.840.1.113883.3.1257.1.973, 2.16.840.1.113883.3.1257.1.1616, 2.16.840.1.113883.3.1257.1.1533')
          AS RXMOrders
      WHERE
          RXMOrders.OrderCategory = 'Referral'
      UNION ALL
      SELECT
          PBRT.MriPatientID
         ,PBRT.[DateTime] AS 'DateTime'
      FROM
          dbo.mpm_fn_2014_PBRTransactions(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.1257.1.973') AS PBRT
      UNION ALL
      SELECT
          PBRProcs.MriPatientID
         ,PBRProcs.[DateTime] AS 'DateTime'
      FROM
          dbo.mpm_fn_2014_PBRProcedures(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.1257.1.1533') AS PBRProcs
      UNION ALL
      SELECT
          MedOrder.MriPatientID
         ,MedOrder.LowTime AS 'DateTime'
      FROM
          dbo.mpm_fn_2014_MedicationOrder(@LocalReportRunTime, @CMSMeasureID,
                                          '2.16.840.1.113883.3.1257.1.972, 2.16.840.1.113883.3.1257.1.950') AS MedOrder;
  
      SELECT
          MedActive.MriPatientID
         ,MedActive.LowTime AS 'DateTime'
      INTO
          dbo.#BH_ActiveMed
      FROM
          dbo.mpm_fn_2014_MedicationActive(@LocalReportRunTime, @CMSMeasureID,
                                           '2.16.840.1.113883.3.1257.1.972, 2.16.840.1.113883.3.1257.1.950') AS MedActive;
  
      SELECT
          ActiveDx.MriPatientID
         ,DATEADD(DAY, DATEDIFF(DAY, 0, ActiveDx.LowTime), 0) AS 'LowTime'
         ,DATEADD(DAY, DATEDIFF(DAY, 0, ActiveDx.HighTime), 0) AS 'HighTime'
      INTO
          dbo.#BH_Diagnosis
      FROM
          dbo.mpm_fn_2014_ActiveDiagnoses(@LocalReportRunTime, @CMSMeasureID,
                                          '2.16.840.1.113883.3.1257.1.1504, 2.16.840.1.113883.3.1257.1.1505') ActiveDx
      UNION ALL
      SELECT
          VisitDx.MriPatientID
         ,DATEADD(DAY, DATEDIFF(DAY, 0, VisitDx.LowTime), 0) AS 'LowTime'
         ,DATEADD(DAY, DATEDIFF(DAY, 0, VisitDx.HighTime), 0) AS 'HighTime'
      FROM
          dbo.mpm_fn_2014_VisitDiagnoses(@LocalReportRunTime, @CMSMeasureID,
                                         '2.16.840.1.113883.3.1257.1.1504, 2.16.840.1.113883.3.1257.1.1505') VisitDx;
          
      SELECT
          PBRProcs.MriPatientID
         ,PBRProcs.[DateTime] AS 'DateTime'
      INTO
          dbo.#BH_AssessmentAlcoholDrugs
      FROM
          dbo.mpm_fn_2014_PBRProcedures(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.1257.1.1604') AS PBRProcs
      UNION ALL
      SELECT
          Queries.MriPatientID
         ,DATEADD(DAY, DATEDIFF(DAY, 0, Queries.[DateTime]), 0) AS 'DateTime'
      FROM
          dbo.mpm_fn_2014_Queries(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.1257.1.1604') AS Queries
      UNION ALL
      SELECT
          PASTMEDHIST.MriPatientID
         ,DATEADD(DAY, DATEDIFF(DAY, 0, PASTMEDHIST.[DateTime]), 0) AS 'DateTime'
      FROM
          dbo.mpm_fn_2014_PastMedicalHistory(@LocalReportRunTime, @CMSMeasureID, '2.16.840.1.113883.3.1257.1.1604') AS PASTMEDHIST;
  
  /***********************/                                                                 
  /*  BUILD DENOMINATOR  */
  /***********************/
  
      SELECT
          IPP.MriPatientID
         ,IPP.ProviderID
         ,BHOE.EncDateTime
      INTO
          dbo.#Denominator
      FROM
          dbo.#Population AS IPP
          INNER JOIN dbo.#BH_OutpatientEncounter AS BHOE
              ON BHOE.MriPatientID = IPP.MriPatientID
                 AND BHOE.ProviderID = IPP.ProviderID
                 AND BHOE.EncDateTime >= @FromDateNoTime
                 AND BHOE.EncDateTime <= DATEADD(DAY, -42, @ThruDateNoTime)
          INNER JOIN dbo.#BH_Diagnosis AS BHDx
              ON BHDx.MriPatientID = BHOE.MriPatientID
                 AND BHDx.LowTime = BHOE.EncDateTime
          INNER JOIN dbo.#BH_EncsAndProcs AS BHEAP
              ON BHEAP.MriPatientID = BHOE.MriPatientID
                 AND BHEAP.[DateTime] >= BHOE.EncDateTime
                 AND BHEAP.[DateTime] < DATEADD(DAY, 43, BHOE.EncDateTime)
          LEFT OUTER JOIN ( SELECT
                              OutEnc.MriPatientID
                             ,OutEnc.EncDateTime AS 'DateTime'
                            FROM
                              dbo.#BH_OutpatientEncounter AS OutEnc
                              INNER JOIN dbo.#BH_Diagnosis AS Dx
                                  ON Dx.MriPatientID = OutEnc.MriPatientID
                            WHERE
                              OutEnc.EncDateTime >= Dx.LowTime
                              AND (
                                    OutEnc.EncDateTime <= Dx.HighTime
                                    OR Dx.HighTime IS NULL
                                  )
                            UNION ALL
                            SELECT
                              OutEnc.MriPatientID
                             ,OutEnc.EncDateTime AS 'DateTime'
                            FROM
                              dbo.#BH_OutpatientEncounter AS OutEnc
                              INNER JOIN dbo.#BH_ActiveMed AS ActiveMed
                                  ON ActiveMed.MriPatientID = OutEnc.MriPatientID
                                     AND ActiveMed.[DateTime] = OutEnc.EncDateTime
                            UNION ALL
                            SELECT
                              EncsAndProcs.MriPatientID
                             ,EncsAndProcs.[DateTime] AS 'DateTime'
                            FROM
                              dbo.#BH_EncsAndProcs AS EncsAndProcs
                          ) AS BH_Items
              ON BH_Items.MriPatientID = BHOE.MriPatientID
                 AND BH_Items.[DateTime] < BHOE.EncDateTime
                 AND DATEADD(DAY, 180, BH_Items.[DateTime]) > BHOE.EncDateTime
      WHERE
          BH_Items.MriPatientID IS NULL;
  
      DROP TABLE dbo.#BH_ActiveMed, dbo.#BH_Diagnosis, dbo.#BH_OutpatientEncounter;
  
  /*********************/
  /*  BUILD NUMERATOR  */
  /*********************/
  
      SELECT
          DENOM.MriPatientID
         ,DENOM.ProviderID
      INTO
          dbo.#Numerator
      FROM
          dbo.#Denominator AS DENOM
          INNER JOIN dbo.#BH_AssessmentAlcoholDrugs AS BHAAD
              ON BHAAD.MriPatientID = DENOM.MriPatientID
                 AND BHAAD.[DateTime] >= DENOM.EncDateTime
          INNER JOIN ( SELECT
                          D.MriPatientID
                         ,MIN(BHEAP.[DateTime]) AS 'FirstDateTime'
                       FROM
                          dbo.#Denominator AS D
                          INNER JOIN dbo.#BH_EncsAndProcs AS BHEAP
                              ON BHEAP.MriPatientID = D.MriPatientID
                                 AND BHEAP.[DateTime] >= D.EncDateTime
                                 AND BHEAP.[DateTime] < DATEADD(DAY, 43, D.EncDateTime)
                       GROUP BY
                          D.MriPatientID
                     ) AS FirstBHItem
              ON FirstBHItem.MriPatientID = BHAAD.MriPatientID
                 AND FirstBHItem.FirstDateTime >= BHAAD.[DateTime];
  
      DROP TABLE dbo.#BH_AssessmentAlcoholDrugs, dbo.#BH_EncsAndProcs;
  
  /*********************************/
  /*  DISPLAY QUALIFYING PATIENTS  */
  /*********************************/
  
      SELECT DISTINCT
          @CMSMeasureID AS 'CMSID'
         ,IPP.ProviderID AS 'ProviderID'
         ,IPP.MriPatientID AS 'PatientID'
         ,IPP.Name AS 'PatientName'
         ,CASE WHEN IPP.MriPatientID IS NOT NULL THEN 'Y'
               ELSE ' '
          END AS 'IPP'
         ,CASE WHEN DENOM.MriPatientID IS NOT NULL THEN 'Y'
               ELSE ' '
          END AS 'DENOM'
         ,CASE WHEN NUMER.MriPatientID IS NOT NULL THEN 'Y'
               ELSE ' '
          END AS 'NUMER'
         ,IPP.Sex AS 'Gender'
         ,COALESCE(RACE.Code, 'UNK') AS 'Race'
         ,COALESCE(ETHNICITY.Code, 'UNK') AS 'Ethnicity'
         ,COALESCE(PAYER.Code, 'UNK') AS 'Payer'
      INTO
          dbo.#OutputForAttestation
      FROM
          dbo.#Population AS IPP
          LEFT OUTER JOIN dbo.#Denominator AS DENOM
              ON IPP.MriPatientID = DENOM.MriPatientID
                 AND IPP.ProviderID = DENOM.ProviderID
          LEFT OUTER JOIN dbo.#Numerator AS NUMER
              ON IPP.MriPatientID = NUMER.MriPatientID
                 AND IPP.ProviderID = NUMER.ProviderID
          LEFT OUTER JOIN dbo.#Ethnicity AS ETHNICITY
              ON ETHNICITY.MriPatientID = IPP.MriPatientID
          LEFT OUTER JOIN dbo.#Payer AS PAYER
              ON PAYER.MriPatientID = IPP.MriPatientID
          LEFT OUTER JOIN dbo.#Race AS RACE
              ON RACE.MriPatientID = IPP.MriPatientID;
  
  /****************************/
  /*  OUTPUT FOR ATTESTATION  */
  /****************************/
  
      SELECT
          OFA.ProviderID
         ,COUNT(CASE WHEN OFA.IPP = 'Y' THEN 1
                     ELSE NULL
                END) AS 'IPP'
         ,COUNT(CASE WHEN OFA.DENOM = 'Y' THEN 1
                     ELSE NULL
                END) AS 'DENOM'
         ,COUNT(CASE WHEN OFA.NUMER = 'Y' THEN 1
                     ELSE NULL
                END) AS 'NUMER'
      INTO
          dbo.#ProviderOutputForAttestation
      FROM
          dbo.#OutputForAttestation AS OFA
      GROUP BY
          OFA.ProviderID;
  
      IF @ProviderID = 'ALL'
          BEGIN
              DELETE FROM
                  dbo.#ProviderIDs;
              
              INSERT  INTO dbo.#ProviderIDs
                      (
                       ProviderID
  			        )
                      SELECT DISTINCT
                          ProviderID
                      FROM
                          dbo.#ProviderOutputForAttestation;
          END
  
      IF @QRDA3 <> 'Y'
          AND @QRDA3 <> 'y'
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,PIDS.ProviderID AS 'ProviderID'
             ,COALESCE(POFA.IPP, 0) AS 'Initial Patient Population'
             ,COALESCE(POFA.DENOM, 0) AS 'Denominator'
             ,COALESCE(POFA.NUMER, 0) AS 'Numerator'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2014_PerformanceRate(POFA.DENOM, POFA.NUMER, NULL, NULL)), 'N/A') AS 'Performance Rate'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2014_ReportingRate(POFA.DENOM, POFA.NUMER, NULL, NULL)), 'N/A') AS 'Reporting Rate'
          FROM
              dbo.#ProviderIDs AS PIDS
              LEFT OUTER JOIN dbo.#ProviderOutputForAttestation AS POFA
                  ON POFA.ProviderID = PIDS.ProviderID;
  	
  /************/
  /*  QRDA 1  */
  /************/ 
              
      IF @QRDA1 = 'Y'
          OR @QRDA1 = 'y'
          INSERT  INTO dbo.mpmzcus_2014_QRDA1Patients
                  (
                   ReportRunTime
                  ,CMSMeasureID
                  ,ProviderID
                  ,MriPatientID
                  )
                  SELECT DISTINCT
                      @LocalReportRunTime
                     ,@CMSMeasureID
                     ,ProviderID
                     ,PatientID
                  FROM
                      dbo.#OutputForAttestation;	
  
      IF @Detail = 'Y'
          OR @Detail = 'y'
          BEGIN
              SELECT
                  *
              FROM
                  dbo.#OutputForAttestation
              WHERE
                  DENOM = 'Y'
              ORDER BY
                  ProviderID;
  
              SELECT DISTINCT
                  @CMSMeasureID AS 'CMSID'
                 ,CASE WHEN @ProviderID = 'ALL' THEN 'All Providers'
                       ELSE @ProviderID
                  END AS 'Provider(s)'
                 ,IPP.MriPatientID AS 'PatientID'
                 ,IPP.Name AS 'PatientName'
                 ,'Y' AS 'IPP'
                 ,IPP.Sex AS 'Gender'
                 ,COALESCE(RACE.Code, 'UNK') AS 'Race'
                 ,COALESCE(ETHNICITY.Code, 'UNK') AS 'Ethnicity'
                 ,COALESCE(PAYER.Code, 'UNK') AS 'Payer'
              FROM
                  dbo.#Population AS IPP
                  LEFT OUTER JOIN dbo.#Ethnicity AS ETHNICITY
                      ON ETHNICITY.MriPatientID = IPP.MriPatientID
                  LEFT OUTER JOIN dbo.#Payer AS PAYER
                      ON PAYER.MriPatientID = IPP.MriPatientID
                  LEFT OUTER JOIN dbo.#Race AS RACE
                      ON RACE.MriPatientID = IPP.MriPatientID;
          END
          
      
  /************/
  /*  QRDA 3  */
  /************/
  
      IF @QRDA3 = 'Y'
          OR @QRDA3 = 'y'
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,'PAYER' AS 'FIELD'
             ,SUPP.Payer AS 'VALUE'
             ,SUPP.ProviderID
             ,SUPP.IPP
             ,SUPP.DENOM
             ,SUPP.NUMER
          FROM
              ( SELECT
                  OFA.ProviderID
                 ,SUM(CASE WHEN OFA.IPP = 'Y' THEN 1
                           ELSE 0
                      END) AS IPP
                 ,SUM(CASE WHEN OFA.DENOM = 'Y' THEN 1
                           ELSE 0
                      END) AS DENOM
                 ,SUM(CASE WHEN OFA.NUMER = 'Y' THEN 1
                           ELSE 0
                      END) AS NUMER
                 ,OFA.Payer
                FROM
                  dbo.#OutputForAttestation AS OFA
                GROUP BY
                  OFA.ProviderID
                 ,OFA.Payer
              ) AS SUPP
          UNION
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,'GENDER' AS 'FIELD'
             ,SUPP.Gender AS 'VALUE'
             ,SUPP.ProviderID
             ,SUPP.IPP
             ,SUPP.DENOM
             ,SUPP.NUMER
          FROM
              ( SELECT
                  OFA.ProviderID
                 ,SUM(CASE WHEN OFA.IPP = 'Y' THEN 1
                           ELSE 0
                      END) AS IPP
                 ,SUM(CASE WHEN OFA.DENOM = 'Y' THEN 1
                           ELSE 0
                      END) AS DENOM
                 ,SUM(CASE WHEN OFA.NUMER = 'Y' THEN 1
                           ELSE 0
                      END) AS NUMER
                 ,OFA.Gender
                FROM
                  dbo.#OutputForAttestation AS OFA
                GROUP BY
                  OFA.ProviderID
                 ,OFA.Gender
              ) AS SUPP
          UNION
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,'RACE' AS 'FIELD'
             ,SUPP.Race AS 'VALUE'
             ,SUPP.ProviderID
             ,SUPP.IPP
             ,SUPP.DENOM
             ,SUPP.NUMER
          FROM
              ( SELECT
                  OFA.ProviderID
                 ,SUM(CASE WHEN OFA.IPP = 'Y' THEN 1
                           ELSE 0
                      END) AS IPP
                 ,SUM(CASE WHEN OFA.DENOM = 'Y' THEN 1
                           ELSE 0
                      END) AS DENOM
                 ,SUM(CASE WHEN OFA.NUMER = 'Y' THEN 1
                           ELSE 0
                      END) AS NUMER
                 ,OFA.Race
                FROM
                  dbo.#OutputForAttestation AS OFA
                GROUP BY
                  OFA.ProviderID
                 ,OFA.Race
              ) AS SUPP
          UNION
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,'ETHNICITY' AS 'FIELD'
             ,SUPP.Ethnicity AS 'VALUE'
             ,SUPP.ProviderID
             ,SUPP.IPP
             ,SUPP.DENOM
             ,SUPP.NUMER
          FROM
              ( SELECT
                  OFA.ProviderID
                 ,SUM(CASE WHEN OFA.IPP = 'Y' THEN 1
                           ELSE 0
                      END) AS IPP
                 ,SUM(CASE WHEN OFA.DENOM = 'Y' THEN 1
                           ELSE 0
                      END) AS DENOM
                 ,SUM(CASE WHEN OFA.NUMER = 'Y' THEN 1
                           ELSE 0
                      END) AS NUMER
                 ,OFA.Ethnicity
                FROM
                  dbo.#OutputForAttestation AS OFA
                GROUP BY
                  OFA.ProviderID
                 ,OFA.Ethnicity
              ) AS SUPP
          UNION
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,'TOTAL' AS 'FIELD'
             ,NULL AS 'VALUE'
             ,PIDS.ProviderID
             ,COALESCE(POFA.IPP, 0)
             ,COALESCE(POFA.DENOM, 0)
             ,COALESCE(POFA.NUMER, 0)
          FROM
              dbo.#ProviderIDs AS PIDS
              LEFT OUTER JOIN dbo.#ProviderOutputForAttestation AS POFA
                  ON POFA.ProviderID = PIDS.ProviderID;
  
      DELETE FROM
          dbo.mpmzcus_2014_Population
      WHERE
          ReportRunTime = @LocalReportRunTime
          AND CMSMeasureID = @CMSMeasureID;
  
  GO
  
  /* Build extended property with revision information */
  
  EXEC dbo.mpmzcus_2014_ExtendedProperties
  	 @Name = 'mpmzcus_2014_CMS_169'
  	,@Type = 'PROCEDURE'
  	,@Revision = '$Revision: 8785 $'
  	,@Date = '$Date: 2015-01-23 13:10:14 -0600 (Fri, 23 Jan 2015) $';
  

  dbrinkerhoff January 26, 2015 09:40

  - 0 +
• 
  Thank you for the script!
  
  I can reproduce the issue here on the latest version of Prompt and I'll have a look into a fix for you now.
  
  Thanks,
  Aaron.

  Aaron L January 26, 2015 09:45

  - 0 +
• 
  This example is better in two ways. It is shorter, and, if part of it is commented out, the bug does not occur.
  
  I have cut out a lot of the script, so what is left will not actually run correctly. But it does format, and both instances of DEBUG.Section become DEBUG.SECTION. However, if the statement that begins "IF @QRDA3 <> 'Y' " is commented out, the formatting does not change the case. And, as before, this only happens in certain databases. In this case it is not only "master" that causes the issue.
  

  /**********************************************************************************************************/
  /*  CMS 064 (NQF XXXX)															                          */
  /*  Preventive Care and Screening: Risk-Stratified Cholesterol – Fasting Low Density Lipoprotein (LDL-C)  */
  /*  Vendor Measure Version: 1												                              */
  /**********************************************************************************************************/
  
  IF OBJECT_ID(N'dbo.mpmzcus_2015_CMS_064', N'P') IS NOT NULL
      DROP PROCEDURE dbo.mpmzcus_2015_CMS_064;
  GO
  
  CREATE PROCEDURE dbo.mpmzcus_2015_CMS_064
      @ReportRunTime DATETIME
     ,@FromDate DATETIME
     ,@ThruDate DATETIME
     ,@Submission_ProviderID VARCHAR(MAX)
     ,@Detail CHAR(1)
     ,@QRDA1 CHAR(1)
     ,@QRDA3 CHAR(1)
     ,@Submission_PatientID VARCHAR(MAX)
     ,@Debug CHAR(1)
     ,@Audit CHAR(1)
  AS
      SET NOCOUNT ON              -- Stops the message that shows the count of rows for faster processing
      SET ANSI_NULLS ON           -- Ensures ISO-compliant behavior of = and <> when used with null values
      SET QUOTED_IDENTIFIER ON    -- Ensures ISO-compliant behavior regarding quotation mark delimiting identifiers and literal strings
      
      IF @QRDA3 <> 'Y'
          AND @QRDA3 <> 'y'
          SELECT
              @CMSMeasureID AS 'CMSMeasureID'
             ,PIDS.ProviderID AS 'ProviderID'
             ,COALESCE(POFA.IPP1, 0) AS 'Initial Patient Population 1'
             ,COALESCE(POFA.IPP2, 0) AS 'Initial Patient Population 2'
             ,COALESCE(POFA.IPP3, 0) AS 'Initial Patient Population 3'
             ,COALESCE(POFA.DENOM1, 0) AS 'Denominator 1'
             ,COALESCE(POFA.DENOM2, 0) AS 'Denominator 2'
             ,COALESCE(POFA.DENOM3, 0) AS 'Denominator 3'
             ,COALESCE(POFA.DENEX1, 0) AS 'Denominator Exclusions 1'
             ,COALESCE(POFA.DENEX2, 0) AS 'Denominator Exclusions 2'
             ,COALESCE(POFA.DENEX3, 0) AS 'Denominator Exclusions 3'
             ,COALESCE(POFA.NUMER1, 0) AS 'Numerator 1'
             ,COALESCE(POFA.NUMER2, 0) AS 'Numerator 2'
             ,COALESCE(POFA.NUMER3, 0) AS 'Numerator 3'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_PerformanceRate(POFA.DENOM1, POFA.NUMER1, POFA.DENEX1, NULL)),
                       'N/A') AS 'Performance Rate 1'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_ReportingRate(POFA.DENOM1, POFA.NUMER1, POFA.DENEX1, NULL)),
                       'N/A') AS 'Reporting Rate 1'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_PerformanceRate(POFA.DENOM2, POFA.NUMER2, POFA.DENEX2, NULL)),
                       'N/A') AS 'Performance Rate 2'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_ReportingRate(POFA.DENOM2, POFA.NUMER2, POFA.DENEX2, NULL)),
                       'N/A') AS 'Reporting Rate 2'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_PerformanceRate(POFA.DENOM3, POFA.NUMER3, POFA.DENEX3, NULL)),
                       'N/A') AS 'Performance Rate 3'
             ,COALESCE(CONVERT(VARCHAR(4), dbo.mpm_fn_2015_ReportingRate(POFA.DENOM3, POFA.NUMER3, POFA.DENEX3, NULL)),
                       'N/A') AS 'Reporting Rate 3'
          FROM
              dbo.#ProviderIDs AS PIDS
              LEFT OUTER JOIN dbo.#ProviderOutputForAttestation AS POFA
                  ON POFA.ProviderID = PIDS.ProviderID;
  
  /***********/
  /*  DEBUG  */
  /***********/
  
      IF (
           @Debug = 'Y'
           AND @PatientID IS NULL
         )
          SELECT
              DEBUG.PatientID
             ,DEBUG.Section
             ,DEBUG.SectionText
             ,DEBUG.MoreInfo
          FROM
              ( SELECT
                  PID.PatientID
                 ,NULL AS 'Section'
                 ,0 AS 'SectionOrder'
                 ,'Not in any IPP; no qualifying encounter during measurement period' AS 'SectionText'
                 ,NULL AS 'MoreInfo'
                FROM
                  dbo.#OriginalPopulation AS PP
                  INNER JOIN dbo.#PatientIDs AS PID
                      ON PP.MriPatientID = PID.PatientID
                         AND PP.ReportRunTime = @LocalReportRunTime
                         AND PP.CMSMeasureID = @CMSMeasureID
                  LEFT OUTER JOIN dbo.#Population AS POP
                      ON POP.MriPatientID = PID.PatientID
                WHERE
                  POP.MriPatientID IS NULL
                UNION
                SELECT
                  PID.PatientID
                 ,'IPP1' AS 'Section'
                 ,1.1 AS 'SectionOrder'
                 ,CASE WHEN PLTDM.MriPatientID IS NULL
                       THEN 'Not in DENOM1; missing qualifying Lab Test(s) during measurement period'
                       WHEN CHDorFram.MriPatientID IS NULL
                       THEN 'Not in DENOM1; no CHD Diagnosis before or during measurement period or no Framingham risk with > 20% result during measurement period'
                       ELSE NULL
                  END AS 'SectionText'
                 ,NULL AS 'MoreInfo'
                FROM
                  dbo.#PatientIDs AS PID
                  INNER JOIN dbo.#Population AS POP
                      ON POP.MriPatientID = PID.PatientID
                  LEFT OUTER JOIN dbo.#PerfLabTestsDuringMP AS PLTDM
                      ON PLTDM.MriPatientID = POP.MriPatientID
                  LEFT OUTER JOIN ( SELECT
                                      CHD.MriPatientID
                                    FROM
                                      dbo.#Diagnoses AS CHD
                                    WHERE
                                      CHD.ValueSetOID = '2.16.840.1.113883.3.600.863'
                                      AND CHD.LowTime <= @LocalThruDate
                                    UNION ALL
                                    SELECT
                                      FGT20.MriPatientID
                                    FROM
                                      dbo.#FraminghamGreaterThan20 AS FGT20
                                  ) AS CHDorFram
                      ON CHDorFram.MriPatientID = POP.MriPatientID
                WHERE
                  PLTDM.MriPatientID IS NULL
                  OR CHDorFram.MriPatientID IS NULL
              ) AS DEBUG
          ORDER BY
              DEBUG.PatientID
             ,DEBUG.SectionOrder;
  
      IF (
           @Debug = 'Y'
           AND @PatientID IS NOT NULL
         )
          SELECT
              DEBUG.PatientID
             ,DEBUG.Section
             ,DEBUG.SectionText
             ,DEBUG.MoreInfo
          FROM
              ( SELECT
                  PID.PatientID
                 ,'IPP1/2/3' AS 'Section'
                 ,0.1 AS 'SectionOrder'
                 ,CASE WHEN POP.MriPatientID IS NOT NULL THEN 'Between ages 20 and 79'
                       ELSE 'Not between ages 20 and 79'
                  END AS 'SectionText'
                 ,NULL AS 'MoreInfo'
                FROM
                  dbo.#PatientIDs AS PID
                  LEFT OUTER JOIN dbo.#OriginalPopulation AS POP
                      ON POP.MriPatientID = PID.PatientID
                UNION
                SELECT
                  PID.PatientID
                 ,'IPP1/2/3' AS 'Section'
                 ,0.2 AS 'SectionOrder'
                 ,CASE WHEN POP.MriPatientID IS NOT NULL THEN 'Has qualifying encounter during measurement period'
                       ELSE 'No qualifying encounter during measurement period'
                  END AS 'SectionText'
                 ,CASE WHEN POP.MriPatientID IS NOT NULL
                       THEN 'Encounter date: ' + CONVERT(VARCHAR(20), POP.[DateTime], 101) + ' with provider: '
                            + POP.ProviderID
                       ELSE NULL
                  END AS 'MoreInfo'
                FROM
                  dbo.#OriginalPopulation AS PP
                  INNER JOIN dbo.#PatientIDs AS PID
                      ON PID.PatientID = PP.MriPatientID
                         AND PP.ReportRunTime = @LocalReportRunTime
                         AND PP.CMSMeasureID = @CMSMeasureID
                  LEFT OUTER JOIN dbo.#Population AS POP
                      ON POP.MriPatientID = PID.PatientID
              ) AS DEBUG
          ORDER BY
              DEBUG.PatientID
             ,DEBUG.SectionOrder;
  
  
  
  GO
  

  dbrinkerhoff January 26, 2015 10:14

  - 0 +
• 
  Thanks again for the examples - they're really useful!
  
  I think what's happening is on some databases it may not be able resolve some of the identifiers to the database objects and so it's interpreting the script slightly differently depending on if it has those objects there or not.
  
  I've made a first attempt at a fix for you in this private build, could you give it a go and let me know if it helps with the cases you've got?
  
  I'll continue to investigate to see if this might be affecting anything else.
  
  Thanks,
  Aaron.
  
  EDIT: Apologies, the fix may have introduced a knock on, i'll look into another possible fix.

  Aaron L January 26, 2015 11:29

  - 0 +
• 
  We've done some further testing and think we have this fixed for you in this private build. Please give it a try and let us know how you get on.
  
  Thanks!
  Aaron.

  Aaron L January 27, 2015 05:06

  - 0 +
• 
  That looks like it's working. If I find any files where it fails, I'll post again. Thanks so much for the quick response!

  dbrinkerhoff January 27, 2015 08:49

  - 0 +
• 
  Thanks for letting us know - We'll continue to test it here and hopefully we'll be able to roll the fix into a stable release next week.
  
  Thanks again for your help!
  Aaron.

  Aaron L January 28, 2015 04:37

  - 0 +

Add comment

Please sign in to leave a comment.