vendredi 16 juin 2017

How to find 50 random rows for multiple Metrics?

We have scores for providers based on whether or not their patients are or are not in the numerator. In order to complete validation on if the patient is set (compliant or not compliant), I have been tasked with creating a report that randomly selects 50 patients from each metric. Now, I can run my query 12 times to gather 50 patients for each metric, but that is very time consuming. I thought about changing the code from Top 50 to Top 600, but returns 600 random rows, but not all the metrics are represented.

The code I am including is how it is written for just one metric at a time.

DECLARE
 @MetricID  INT
,@AsOfDate  DATE

SET @MetricID = 52
SET @AsOfDate = '04/30/2017'


SELECT TOP 50
     Pat.PatientID
    ,Metrics.MetricID   
    ,Metrics.MetricName
    ,PCPS.NumCompliant
    ,Denominator = PCPS.PQRIPopulation
    ,AsOfDate = PCPS.EndDate
FROM
    dbo.PhyComPQRSStaging PCPS TABLESAMPLE(.2 PERCENT)
    INNER JOIN dbo.PCQMPatient Pat
        ON PCPS.PATIENTID= Pat.PATIENTID
    INNER JOIN dbo.PCQMMetrics Metrics
        ON PCPS.MetricID = Metrics.MetricID
WHERE
    Metrics.MetricID = @MetricID
GROUP BY
     Pat.PAT_ID
    ,Pat.FH_MRN
    ,PAMN.AlternateMeasureNumber
    ,Metrics.MetricID   
    ,Metrics.MetricName
    ,PCPS.NumCompliant
    ,PCPS.PQRIPopulation
    ,PCPS.EndDate

I am sure there are probably ways to do this outside of SQL, but I am limited to only using SQL at this point in time. Any help is much appreciated.




Aucun commentaire:

Enregistrer un commentaire