SQL Experts
- Thread starter Adower
- Start date
Far from an expert, as I do very basic stuff, but what are your questions? I could try bouncing them off a co-worker or two.
Weirdest place for a SQL question but yeah throw it down.
Will put the code in here momentarily.
Hah, I figured there might just be someone.
For sure, like others, I have a basic knowledge, just let us know.
Please see code below. Essentially I am trying to help my coworker to get the results to populate on the aggregate level. When I run the query the results are broken down per plan code. I think it has something to do with the group by statement at the bottom but I cannot figure it out. I'm a average SQL user at best so this is above my knowledge.
DECLARE
@vMeasurementQuarter AS VARCHAR(6)
SET
@vMeasurementQuarter = '2019Q4'
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* This measure is applied with a one quarter lag based on date of denial for the original submission. Therefore, */
/* the following parameters are permanently set to select data denied in the calendar quarter previous to the */
/* @MeasurementQuarter identified in the parameter set above. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
DECLARE
@vYear VARCHAR(4),
@vQuarter VARCHAR(1)
SET
@vYear = SUBSTRING(@vMeasurementQuarter, 1, 4)
SET
@vQuarter = SUBSTRING(@vMeasurementQuarter, 6, 1)
DECLARE
@vResponseQuarter AS VARCHAR(6)
IF (@vQuarter =1)
SET @vResponseQuarter = CONCAT((@vYear - 1), 'Q4')
ELSE
SET @vResponseQuarter = CONCAT(@vYear, 'Q',@vQuarter-1)
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
SELECT
Measure = 'DRMT.001',
Program = 'PHDP',
PlanCode = hp.HealthPlanCode,
--MeasurementQuarter = @vMeasurementQuarter,
ResponseQuarter = SubQ2.ResponseQuarter,
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The following case statement calculates the results for this measure. Possible results are Pass, Fail and */
/* Informational Only. */
/* Pass criteria are: */
/* 50% of denied records correctly resubmitted and accepted within 15 days; */
/* 80% of denied records correctly resubmitted and accepted within 30 days; */
/* 95% of denied records correctly resubmitted and accepted within 60 days; */
/* If greater than 5% of originally submitted encounters during the quarter were denied and the criteria above are */
/* not met, the result is Fail. */
/* If less than 5% of originally submitted encounters during the quarter were denied and the criteria above are */
/* not met, the result is Informational Only. */
/* If no data was submitted in the time frame, the measure automatically fails */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
Results = CASE WHEN SubQ2.TotalSubmitted IS NULL
THEN 'FAIL'
ELSE
CASE WHEN SubQ2.Perc15Days > .4999
AND SubQ2.Perc30Days > .7999
AND SubQ2.Perc60Days > .9499
THEN 'PASS'
ELSE
CASE WHEN SubQ2.PercDenied< .05
THEN 'N/A'
ELSE 'FAIL'
END
END
END,
MeasureCategory = CASE WHEN SubQ2.TotalSubmitted IS NULL
THEN 'Threshold Measure'
ELSE
CASE WHEN SubQ2.Perc15Days > .4999
AND SubQ2.Perc30Days > .7999
AND SubQ2.Perc60Days > .9499
THEN 'Threshold Measure'
ELSE
CASE WHEN SubQ2.PercDenied< .05
THEN'Information Only'
ELSE 'Threshold Measure'
END
END
END,
TotalSubmitted = ISNULL(SubQ2.TotalSubmitted, 0),
TotalDenied = ISNULL(SubQ2.TotalDenied, 0),
PercDenied = ISNULL(SubQ2.PercDenied, 0),
Perc15Days = ISNULL(SubQ2.Perc15Days, 0),
Perc30Days = ISNULL(SubQ2.Perc30Days, 0),
Perc60Days = ISNULL(SubQ2.Perc60Days, 0),
ReportDate = CONCAT(DATEPART(YYYY, GETDATE()), '-', DATEPART(mm, GETDATE()), '-',DATEPART(dd, GETDATE()))
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* Left join on udt_HealthPlan to force a row for HealthPlanCode regardless of whether data was received during */
/* the Measurement Quarter. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
FROM
udt_HealthPlan as hp
LEFT JOIN
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* SubQ2 aggregates the results of SubQ1 by PlanCode */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
(
SELECT
PlanCode = SubQ1.PlanCode,
ResponseQuarter = CONCAT(DATEPART(yyyy, SubQ1.ResponseDate), 'Q', DATEPART(qq,SubQ1.ResponseDate)),
TotalSubmitted = COUNT(DISTINCT SubQ1.EncID),
TotalDenied = COUNT(DISTINCT SubQ1.EncDenied),
PercDenied = CAST(CAST(COUNT(DISTINCT SubQ1.EncDenied) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncID), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc15Days = CAST(CAST(SUM(SubQ1.Days15orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc30Days = CAST(CAST(SUM(SubQ1.Days30orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc60Days = CAST(CAST(SUM(SubQ1.Days60orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4))
FROM
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* SubQ1 calculates the Lagtime and applicable categories for each encounter */
/* Note - to calculate lagtime, the original encounter must be denied, and the void/replacement encounter must be */
/* accepted. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
(
SELECT
PlanCode = rf1.HealthPlanCode,
ResponseDate = rf1.responsedate,
EncID = e1.EncounterId,
EncDenied = CASE WHEN e1.StatusTypeId = 3 THEN e1.EncounterId ELSE NULL END,
Days15orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 17 END) < 16
THEN 1
ELSE 0 END,
Days30orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 32 END) < 31
THEN 1
ELSE 0 END,
Days60orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 62 END) < 61
THEN 1
ELSE 0 END
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The first instance of udt_Encounter and udt_ReceivedFile selects all encounters submitted */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
FROM
(
udt_Encounter e1
LEFT JOIN
udt_ReceivedFile rf1
ON
e1.ReceivedFileId = rf1.ReceivedFileId
LEFT JOIN
udt_EncounterProvider as ep1
ON
e1.EncounterId = ep1.EncounterId
LEFT JOIN
udt_Provider as p1
ON ep1.ProviderId = p1.ProviderId
)
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The second instance of udt_Encounter and udt_ReceivedFile selects all encounters linked to the first set of */
/* encounters by ParentEncounterId - so these are the voids and replacements */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
LEFT JOIN
(
udt_Encounter e2
LEFT JOIN
udt_ReceivedFile rf2
ON
e2.ReceivedFileId = rf2.ReceivedFileId
)
ON
e1.EncounterId = e2.ParentEncounterId
WHERE
CONCAT(DATEPART(yyyy, rf1.ResponseDate), 'Q', DATEPART(qq, rf1.ResponseDate)) IN ('2018Q3','2018Q4', '2019Q1', '2019Q2', '2019Q3', '2019Q4', '2020Q1', '2020Q2')
AND ISNULL(e1.IsLegacyEncounter,0) = 0
AND ((ep1.ProviderTypeId = 1 AND p1.providerNPI IN ('1538157508',
'1124018031',
'1487897047',
'1407069537',
'1043316292',
'1093830994'
) OR ep1.ProviderTypeId = 1 AND e1.ClaimType <> 1 AND p1.providerNPI IN ( '1841626587',
'1558737486',
'1972593788',
'1962492876',
'1598941825',
'1093980823',
'1871839027',
'1720286750',
'1174503114',
'1518018191'
)))
) AS SubQ1
GROUP BY
SubQ1.PlanCode,
CONCAT(DATEPART(yyyy, SubQ1.ResponseDate), 'Q', DATEPART(qq, SubQ1.ResponseDate))
) AS SubQ2
ON hp.HealthPlanCode = SubQ2.PlanCode
--WHERE HP.HCPTypeId in (1,2)
ORDER BY
hp.HealthPlanCode
DECLARE
@vMeasurementQuarter AS VARCHAR(6)
SET
@vMeasurementQuarter = '2019Q4'
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* This measure is applied with a one quarter lag based on date of denial for the original submission. Therefore, */
/* the following parameters are permanently set to select data denied in the calendar quarter previous to the */
/* @MeasurementQuarter identified in the parameter set above. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
DECLARE
@vYear VARCHAR(4),
@vQuarter VARCHAR(1)
SET
@vYear = SUBSTRING(@vMeasurementQuarter, 1, 4)
SET
@vQuarter = SUBSTRING(@vMeasurementQuarter, 6, 1)
DECLARE
@vResponseQuarter AS VARCHAR(6)
IF (@vQuarter =1)
SET @vResponseQuarter = CONCAT((@vYear - 1), 'Q4')
ELSE
SET @vResponseQuarter = CONCAT(@vYear, 'Q',@vQuarter-1)
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
SELECT
Measure = 'DRMT.001',
Program = 'PHDP',
PlanCode = hp.HealthPlanCode,
--MeasurementQuarter = @vMeasurementQuarter,
ResponseQuarter = SubQ2.ResponseQuarter,
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The following case statement calculates the results for this measure. Possible results are Pass, Fail and */
/* Informational Only. */
/* Pass criteria are: */
/* 50% of denied records correctly resubmitted and accepted within 15 days; */
/* 80% of denied records correctly resubmitted and accepted within 30 days; */
/* 95% of denied records correctly resubmitted and accepted within 60 days; */
/* If greater than 5% of originally submitted encounters during the quarter were denied and the criteria above are */
/* not met, the result is Fail. */
/* If less than 5% of originally submitted encounters during the quarter were denied and the criteria above are */
/* not met, the result is Informational Only. */
/* If no data was submitted in the time frame, the measure automatically fails */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
Results = CASE WHEN SubQ2.TotalSubmitted IS NULL
THEN 'FAIL'
ELSE
CASE WHEN SubQ2.Perc15Days > .4999
AND SubQ2.Perc30Days > .7999
AND SubQ2.Perc60Days > .9499
THEN 'PASS'
ELSE
CASE WHEN SubQ2.PercDenied< .05
THEN 'N/A'
ELSE 'FAIL'
END
END
END,
MeasureCategory = CASE WHEN SubQ2.TotalSubmitted IS NULL
THEN 'Threshold Measure'
ELSE
CASE WHEN SubQ2.Perc15Days > .4999
AND SubQ2.Perc30Days > .7999
AND SubQ2.Perc60Days > .9499
THEN 'Threshold Measure'
ELSE
CASE WHEN SubQ2.PercDenied< .05
THEN'Information Only'
ELSE 'Threshold Measure'
END
END
END,
TotalSubmitted = ISNULL(SubQ2.TotalSubmitted, 0),
TotalDenied = ISNULL(SubQ2.TotalDenied, 0),
PercDenied = ISNULL(SubQ2.PercDenied, 0),
Perc15Days = ISNULL(SubQ2.Perc15Days, 0),
Perc30Days = ISNULL(SubQ2.Perc30Days, 0),
Perc60Days = ISNULL(SubQ2.Perc60Days, 0),
ReportDate = CONCAT(DATEPART(YYYY, GETDATE()), '-', DATEPART(mm, GETDATE()), '-',DATEPART(dd, GETDATE()))
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* Left join on udt_HealthPlan to force a row for HealthPlanCode regardless of whether data was received during */
/* the Measurement Quarter. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
FROM
udt_HealthPlan as hp
LEFT JOIN
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* SubQ2 aggregates the results of SubQ1 by PlanCode */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
(
SELECT
PlanCode = SubQ1.PlanCode,
ResponseQuarter = CONCAT(DATEPART(yyyy, SubQ1.ResponseDate), 'Q', DATEPART(qq,SubQ1.ResponseDate)),
TotalSubmitted = COUNT(DISTINCT SubQ1.EncID),
TotalDenied = COUNT(DISTINCT SubQ1.EncDenied),
PercDenied = CAST(CAST(COUNT(DISTINCT SubQ1.EncDenied) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncID), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc15Days = CAST(CAST(SUM(SubQ1.Days15orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc30Days = CAST(CAST(SUM(SubQ1.Days30orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4)),
Perc60Days = CAST(CAST(SUM(SubQ1.Days60orLess) AS NUMERIC)
/ CAST(NULLIF(COUNT(DISTINCT SubQ1.EncDenied), 0) ASNUMERIC) AS NUMERIC(5,4))
FROM
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* SubQ1 calculates the Lagtime and applicable categories for each encounter */
/* Note - to calculate lagtime, the original encounter must be denied, and the void/replacement encounter must be */
/* accepted. */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
(
SELECT
PlanCode = rf1.HealthPlanCode,
ResponseDate = rf1.responsedate,
EncID = e1.EncounterId,
EncDenied = CASE WHEN e1.StatusTypeId = 3 THEN e1.EncounterId ELSE NULL END,
Days15orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 17 END) < 16
THEN 1
ELSE 0 END,
Days30orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 32 END) < 31
THEN 1
ELSE 0 END,
Days60orLess = CASE WHEN
(CASE WHEN e1.StatusTypeId = 3
AND e2.StatusTypeId = 1
THEN DATEDIFF(dd,rf1.ResponseDate, rf2.SubmissionDate)
ELSE 62 END) < 61
THEN 1
ELSE 0 END
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The first instance of udt_Encounter and udt_ReceivedFile selects all encounters submitted */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
FROM
(
udt_Encounter e1
LEFT JOIN
udt_ReceivedFile rf1
ON
e1.ReceivedFileId = rf1.ReceivedFileId
LEFT JOIN
udt_EncounterProvider as ep1
ON
e1.EncounterId = ep1.EncounterId
LEFT JOIN
udt_Provider as p1
ON ep1.ProviderId = p1.ProviderId
)
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
/* The second instance of udt_Encounter and udt_ReceivedFile selects all encounters linked to the first set of */
/* encounters by ParentEncounterId - so these are the voids and replacements */
/*``````````````````````````````````````````````````````````````````````````````````````````````````````````````````*/
LEFT JOIN
(
udt_Encounter e2
LEFT JOIN
udt_ReceivedFile rf2
ON
e2.ReceivedFileId = rf2.ReceivedFileId
)
ON
e1.EncounterId = e2.ParentEncounterId
WHERE
CONCAT(DATEPART(yyyy, rf1.ResponseDate), 'Q', DATEPART(qq, rf1.ResponseDate)) IN ('2018Q3','2018Q4', '2019Q1', '2019Q2', '2019Q3', '2019Q4', '2020Q1', '2020Q2')
AND ISNULL(e1.IsLegacyEncounter,0) = 0
AND ((ep1.ProviderTypeId = 1 AND p1.providerNPI IN ('1538157508',
'1124018031',
'1487897047',
'1407069537',
'1043316292',
'1093830994'
) OR ep1.ProviderTypeId = 1 AND e1.ClaimType <> 1 AND p1.providerNPI IN ( '1841626587',
'1558737486',
'1972593788',
'1962492876',
'1598941825',
'1093980823',
'1871839027',
'1720286750',
'1174503114',
'1518018191'
)))
) AS SubQ1
GROUP BY
SubQ1.PlanCode,
CONCAT(DATEPART(yyyy, SubQ1.ResponseDate), 'Q', DATEPART(qq, SubQ1.ResponseDate))
) AS SubQ2
ON hp.HealthPlanCode = SubQ2.PlanCode
--WHERE HP.HCPTypeId in (1,2)
ORDER BY
hp.HealthPlanCode
Last edited:
GROUP BY implicitly does a DISTINCT over the values of the column you're grouping by. DISTINCT just removes duplicates.
GROUP BY lets you use aggregate functions, like AVG, MAX, MIN, SUM, and COUNT.
GROUP BY lets you use aggregate functions, like AVG, MAX, MIN, SUM, and COUNT.
Users who are viewing this thread
