Aging Grouped By Insurance Carrier
Back to Query Directory
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.
This query uses additional queries to get subtotals and grand total, then uses the UNION operator to combine the result sets.
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 jchgsplits
WHERE ptpayornum = a.ptpayornum AND lastbilled IS NOT NULL),'NONE')
AS "Last Billed"
FROM
rv_ptpayors a JOIN 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
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
rv_ptpayors a
WHERE
a.payortype = 'I'
AND a.balance > 0
/*---- Sort combined result sets by first, then second column ------- */
ORDER BY
1,2
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 jchgsplits
WHERE ptpayornum = a.ptpayornum AND lastbilled IS NOT NULL),'NONE')
AS "Last Billed"
FROM
rv_ptpayors a JOIN 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
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
rv_ptpayors a
WHERE
a.payortype = 'I'
AND a.balance > 0
/*---- Sort combined result sets by first, then second column ------- */
ORDER BY
1,2
CategoryQueries
