Aging Grouped By Insurance Carrier

I am looking for an aging report that displays just the insurance payor aging, grouped by carrier. That is, a report that has Insurance Carrier as a group heading with the relevant patient aging within the group.

If you want to export to Excel, you must use DBISQLG. The older DBISQLC version, on the other hand, has a handy TEXT output format that is lacking in the G version. These are Sybase tools and I am at a loss as to why they omitted certain features in the new version. They are written in different programming languages (C vs. Java), so perhaps that explains it.

You could also use FlySpeed SQL Query, which has a feature to export nicely formatted results to PDF or Excel.

SELECT DISTINCT
  a.payorname AS "INS PAYOR",
  (b.lastname +', '+ b.firstname +' / '+ b.ID) AS "Patient",
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  COALESCE((SELECT STRING(MAX(lastbilled))
    FROM sos.jchgsplits
    WHERE ptpayornum = a.ptpayornum AND lastbilled IS NOT NULL),'NONE')
    AS "Last Billed"
FROM
  sos.rv_ptpayors a JOIN sos.patients b
WHERE
  a.payortype = 'I'
  AND a.balance > 0
GROUP BY
  "INS PAYOR","Patient",a.ptpayornum

UNION   /* --- COMBINE RESULT SETS OF QUERY ABOVE WITH QUERY BELOW */

SELECT
  a.payorname AS "INS PAYOR",
  'SUBTOTAL',
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  ' ' AS "Last Billed"
FROM
  sos.rv_ptpayors a
WHERE
  a.payortype = 'I'
  AND a.balance > 0
GROUP BY
  "INS PAYOR"

UNION  /* --- COMBINE RESULT SETS OF QUERY ABOVE WITH QUERY BELOW - */

SELECT
  'Z-Z-Z--- GRAND TOTALS ---Z-Z-Z',
  ' ',
  SUM(a.balance) AS "BALANCE",
  SUM(a.age0to30) AS "Current",
  SUM(a.age61to90) AS "31 - 60",
  SUM(a.age91to120) AS "91-120",
  SUM(a.ageovr120) AS "OVER 120",
  SUM(a.ageunapplied ) AS "Unapplied",
  ' ' AS "Last Billed"
FROM
  sos.rv_ptpayors a
WHERE
  a.payortype = 'I'
  AND a.balance > 0

/*---- Sort combined result sets by first, then second column ------- */
ORDER BY
  1,2

This query uses additional queries to get subtotals and grand total, then uses the UNION operator to combine the result sets.