List Medicare Patients Showing 90 Day Countdown for PQRS Reporting

Medicare PQRS reporting requires that we submit documentation every 90 days after initial intake. How would we generate a list of patients (name, ID, intake date, provider, number of days left in 90 day interval, next appointment, and last appointment), sorted by number of days left until the next 90 day deadline.

The query below includes two subqueries. One retrieves the next appointment and the other retrieves the patients last (most recent) appointment. They are almost identical, with the exception of a slight change in the WHERE, and the ORDER BY. They use the “SELECT TOP 1”  syntax to return just the first row of the result set generated by the subquery.

The other interesting feature is the use of the MOD operator in line 7 to return the remainder of a division operation (number of days since intake divided by 90).

The WHERE clause in the main query, starting at line 22, restricts the results to active Medicare patients.

SELECT DISTINCT
  a.lastname AS LastName, 
  a.firstname AS FirstName, 
  a.id AS ID, 
  a.intakedate AS IntakeDate, 
  f.provcode AS Provider,
  90 - MOD(DATEDIFF(DAY,a.intakedate,today()),90) AS "Days until next 90 marker",
  (  SELECT TOP 1 b.apptdate FROM sos.rv_appts b 
     WHERE b.ptnum = a.ptnum AND b.apptdate > TODAY() 
     ORDER BY b.apptdate ASC ) 
  AS "NextAppt", 
  (  SELECT TOP 1 b.apptdate FROM sos.rv_appts b 
     WHERE b.ptnum = a.ptnum AND b.apptdate < TODAY() 
     ORDER BY b.apptdate DESC ) 
  AS "LastAppt" 
FROM
  sos.patients a
  JOIN sos.ptpayors c ON a.ptnum = c.ptnum
  JOIN sos.payors d ON c.payornum = d.payornum
  JOIN sos.carriers e ON d.payornum = e.payornum
  JOIN sos.providers f ON a.providernum = f.providernum
WHERE
  a.flag = 0                            -- in active patient list
  AND a.dischargedate IS NULL           -- no discharge date
  AND e.coverage = 'C'                  -- has at least one medicare policy
ORDER BY 
  "Days until next 90 marker",a.lastname, a.firstname, a.id