Formatting issue -- uppercasing non-keywords

dbrinkerhoffdbrinkerhoff Posts: 7 Bronze 1
edited January 28, 2015 4:37AM in SQL Prompt
Let me start by saying I don't have a good example to reproduce. This issue is happening with long scripts that rely on in-house tables, etc. If it is needed I will take the time to find one.

What is happening is that, when using the same script and the same server and the same formatting style, different databases will behave differently when I use SQL Prompt to format. Something like:
SELECT
DEBUG.Name
FROM
(SELECT
'BLAH' AS Name
) AS DEBUG

(Again, this exact script doesn't cause the error, but it's similar in style to the ones that do.)

If I reformat the script in one database, nothing is changed. If I reformat in another database, SQL Prompt changes the SELECT statement to DEBUG.NAME, which is then an error. I have seen this for columns called Name, Active, and Section. Those don't appear to be reserved keywords by SQL Server (source: https://msdn.microsoft.com/en-us/library/ms189822.aspx ), and if they were, I would expect the behavior to be the same for all databases.

For our style, we use UPPERCASE for all of Reserved Keywords, Build-in functions, and Built-in data types. We are leaving "Use object definition case" unchecked. Any thoughts?
· Share on Twitter

Comments

  • Aaron LAaron L Posts: 596 New member
    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.
    · Share on Twitter
  • dbrinkerhoffdbrinkerhoff Posts: 7 Bronze 1
    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) $';
    · Share on Twitter
  • Aaron LAaron L Posts: 596 New member
    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.
    · Share on Twitter
  • dbrinkerhoffdbrinkerhoff Posts: 7 Bronze 1
    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 &lt;&gt; when used with null values
    SET QUOTED_IDENTIFIER ON    -- Ensures ISO-compliant behavior regarding quotation mark delimiting identifiers and literal strings
    
    IF @QRDA3 &lt;&gt; 'Y'
        AND @QRDA3 &lt;&gt; '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 &gt; 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 &lt;= @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.&#91;DateTime&#93;, 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
    · Share on Twitter
  • Aaron LAaron L Posts: 596 New member
    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.
    · Share on Twitter
  • Aaron LAaron L Posts: 596 New member
    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.
    · Share on Twitter
  • dbrinkerhoffdbrinkerhoff Posts: 7 Bronze 1
    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!
    · Share on Twitter
  • Aaron LAaron L Posts: 596 New member
    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.
    · Share on Twitter
Sign In or Register to comment.