Category: Queries – Accounting

Credits by Sort Code

Posted on | by SOS Blog | Leave a Comment on Credits by Sort Code

In SOS SortCode values can be assigned to charge entries, credit entries, or both. As a result, there are a couple of ways to group and sort credits by SortCode. The following queries provide totals by giving priority to either the SortCode in the credit entry, or to the SortCode entered in the charge to which the credit split is applied. There is also the possiblity that a payment credit may have been entered, but not assigned as yet to a charge, as in the case of a pre-payment.

In the following query, the sortcode entered directly on the credit is represented by lu1.lucode. The SortCode on the charge to which it is applied (if it has been applied) is lu2.lucode. If neither value is available for a particular credit split, then it will be assigned the value ‘Unspecified’. The credit/charge priority is easily reversed by simply reversing the order of the lu1.sortcode and lu2.sortcode fields in the COALESCE function in line 2. Remember that the COALESCE function simply returns the value of the first parameter that is not NULL. In other words, if the first sortcode specified was not entered by the user, then it will check to see if other one was. If neither one is present, then it returns the third parameter, which in this case is the string “Unspecified”.

SELECT
COALESCE(lu1.lucode,lu2.lucode,'Unspecified') AS "SortCode",
SUM (c.crsplamt) AS "Total Credit"
FROM
sos.journal a
JOIN sos.jcredits b ON a.jnum = b.jnum
JOIN sos.jcrsplits c ON b.jnum = c.jnum
LEFT OUTER JOIN sos.lookups lu1 ON a.sortcode = lu1.lunum
LEFT OUTER JOIN sos.jchgsplits d ON c.chgsplnum = d.chgsplnum
LEFT OUTER JOIN sos.journal e ON d.jnum = e.jnum
LEFT OUTER JOIN sos.lookups lu2 ON e.sortcode = lu2.lunum
WHERE a.trandate BETWEEN '2000-01-01' AND '2011-12-31'
GROUP BY "SortCode"

 

 

Billed Charges by Payor

Posted on | by SOS Blog | Leave a Comment on Billed Charges by Payor

“Is there any report that will tell me how many claims were filed to the carriers?  I’m looking to identify which carrier we send the most claims to.”

There is no report, however, this query will tell you how many items were billed for each carrier. Please note that if you generate a claim, but then do not  print it out (or send it electronically), OM will still see it as having been billed.

SELECT
  pay.payorname AS "Carrier",
  car.companynum AS "NEIC",
  COUNT( DISTINCT j.JNUM) AS "Number of Services Billed"
FROM 
  sos.JOURNAL  j
  JOIN sos.JCHGSPLITS jc ON j.jnum=jc.jnum
  JOIN sos.PTPAYORS ptp ON jc.ptpayornum=ptp.ptpayornum
  JOIN sos.PAYORS pay ON ptp.payornum=pay.payornum
  JOIN sos.CARRIERS car ON pay.payornum=car.payornum
WHERE 
  pay.payortype='i' AND
  ( jc.firstbilled IS NOT NULL)
  AND j.trandate>'1980-01-01'
GROUP BY  
  "Carrier","NEIC"
ORDER BY  
  "Carrier","NEIC"

 

 

Aging Grouped By Insurance Carrier

Posted on | by SOS Blog | Leave a Comment on 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.

Adjustments by Type and by Client for a Period

Posted on | by SOS Blog | Leave a Comment on Adjustments by Type and by Client for a Period

I want a detailed or summary report of adjustments for a particular date range, grouped by type and then by client.

 

The detailed query would be:

SELECT
    adjcode, fullnameid, trandate, payorname, amount
FROM
    sos.rv_credits
WHERE
    credtype = 'Adjustment'
    AND trandate BETWEEN '2000-01-01' AND '2008-07-31'
ORDER BY
    adjcode, fullnameid, trandate

 

and the summary query would be:

SELECT
    adjcode, fullnameid, payorname, SUM(amount)
FROM
    sos.rv_credits
WHERE
    credtype = 'Adjustment'
    AND trandate BETWEEN '2000-01-01' AND '2008-07-31'
GROUP BY
    adjcode, fullnameid, payorname
ORDER BY
    adjcode, fullnameid, payorname