How can we help you today? How can we help you today?
dbrinkerhoff
Activity overview Badges (0) Posts (3) Comments (4)

Comments

Sort by recent activity
It looks like this was fixed with the current version of SQL Prompt (10.11.10.27545).  I can't recreate the problem. / comments
  1. Community
  2. SQL Prompt
  3. SQL Prompt formatting -- incorrectly qualifying constraint names
It looks like this was fixed with the current version of SQL Prompt (10.11.10.27545).  I can't recreate the problem.
0 votes
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! / comments
  1. Community
  2. SQL Prompt
  3. Formatting issue -- uppercasing non-keywords
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!
0 votes
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 / comments
  1. Community
  2. SQL Prompt
  3. Formatting issue -- uppercasing non-keywords
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....
0 votes
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 &lt;&gt; 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.&#91;DateTime&#93; 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.&#91;DateTime&#93; 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.&#91;DateTime&#93; 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.&#91;DateTime&#93; 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.&#91;DateTime&#93;), 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.&#91;DateTime&#93;), 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 &gt;= @FromDateNoTime AND BHOE.EncDateTime &lt;= 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.&#91;DateTime&#93; &gt;= BHOE.EncDateTime AND BHEAP.&#91;DateTime&#93; &lt; 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 &gt;= Dx.LowTime AND ( OutEnc.EncDateTime &lt;= 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.&#91;DateTime&#93; = OutEnc.EncDateTime UNION ALL SELECT EncsAndProcs.MriPatientID ,EncsAndProcs.&#91;DateTime&#93; AS 'DateTime' FROM dbo.#BH_EncsAndProcs AS EncsAndProcs ) AS BH_Items ON BH_Items.MriPatientID = BHOE.MriPatientID AND BH_Items.&#91;DateTime&#93; &lt; BHOE.EncDateTime AND DATEADD(DAY, 180, BH_Items.&#91;DateTime&#93;) &gt; 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.&#91;DateTime&#93; &gt;= DENOM.EncDateTime INNER JOIN ( SELECT D.MriPatientID ,MIN(BHEAP.&#91;DateTime&#93;) AS 'FirstDateTime' FROM dbo.#Denominator AS D INNER JOIN dbo.#BH_EncsAndProcs AS BHEAP ON BHEAP.MriPatientID = D.MriPatientID AND BHEAP.&#91;DateTime&#93; &gt;= D.EncDateTime AND BHEAP.&#91;DateTime&#93; &lt; DATEADD(DAY, 43, D.EncDateTime) GROUP BY D.MriPatientID ) AS FirstBHItem ON FirstBHItem.MriPatientID = BHAAD.MriPatientID AND FirstBHItem.FirstDateTime &gt;= BHAAD.&#91;DateTime&#93;; 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 &lt;&gt; 'Y' AND @QRDA3 &lt;&gt; '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) $'; / comments
  1. Community
  2. SQL Prompt
  3. Formatting issue -- uppercasing non-keywords
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 ...
0 votes
Total activity
9
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2