SQL Experts

Adower

Well-Known Member
Established Member
Joined
Sep 3, 2005
Messages
2,309
Location
SAC
Does anyone here work in SQL? If so, can I bounce a few questions off of you?
 

black92

Hot rod Lincoln
Established Member
Joined
Dec 22, 2005
Messages
6,505
Location
Olathe, KS
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.
 

Adower

Well-Known Member
Established Member
Joined
Sep 3, 2005
Messages
2,309
Location
SAC
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
 
Last edited:

cobracide

Well-Known Member
Established Member
Joined
Aug 23, 2002
Messages
8,229
Location
Cacky Lacky, USA
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.
 

Users who are viewing this thread



Top