Category: Queries – Accounting

Payments By Provider For Two Periods

Posted on | by SOS Blog | Leave a Comment on Payments By Provider For Two Periods

This query is interesting in that here we use correlated subqueries in the SELECT list to produce the subtotals for columns 2 and 3. 

SELECT
  provcode,
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2000-01-01' AND '2000-12-31') AS "Per 1 Payments",
  (SELECT COALESCE(sum(crsplamt),0) FROM sos.rv_creditsplits
        WHERE providernum = a.providernum
        AND credtype IN ('cash','check','other')
        AND dateapplied BETWEEN '2001-01-01' AND '2001-12-31') AS "Per 2 Payments"
FROM
  sos.providers a
WHERE
  a.providernum > 100
  AND a.hiderow <> 1
ORDER BY
  provcode

Patients with Unapplied Payments and Outstanding Balances

Posted on | by SOS Blog | Leave a Comment on Patients with Unapplied Payments and Outstanding Balances

I need a report that shows me all of the patients who have an unapplied credit AND a patient balance. At the end of the month I need to run a this report and go back and apply credits to balances that somehow I have missed throughout the month. I have tried to do this by running the unapplied credit report but that report includes patients without a balance and is very time consuming to go into each of those accounts. For example, a patient received a statement for $75.00 yet she had $70 in unapplied credits.

While all the information you need is in the standard Aging by Patient report, this query filters that information so the results contain only those accounts and payors of interest.

SELECT
   (PtPayors.Age0to30 + PtPayors.Age31to60 + PtPayors.Age61to90 + PtPayors.Age91to120 + PtPayors.AgeOvr120) AS "BALANCE",
   PtPayors.Ageunapplied AS "UNAPPLIED",
   Patients.ID,
   Patients.LastName + ', ' + Patients.FirstName AS "Patient Name",
   Payors.PayorName + ' ' + Payors.FirstName AS "Payor Name",
   PtPayors.PayorNum
 FROM  
   SOS.Patients Patients
   LEFT OUTER JOIN SOS.PtPayors PtPayors ON Patients.PtNum= PtPayors.PtNum
   LEFT OUTER JOIN SOS.Payors Payors ON PtPayors.PayorNum=Payors.PayorNum
 WHERE
   "UNAPPLIED" > 0
   AND "BALANCE" > 0
 ORDER BY
  "Patient Name", Patients.ID, PtPayors.PayorNum

Outstanding Account Cleanup

Posted on | by SOS Blog | Leave a Comment on Outstanding Account Cleanup

Would it be possible to write a query to do the following:

List the patient name, account number, outstanding balance and provider
For any account that has not had a date of service in 2009
And has had no payments within the past 30 days.

We want to use this to clear out all such outstanding accounts..

The views used in the query below are not super-efficient, so on a large database it will take a good while to run, but it will deliver the results you want.

SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT sos.LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT sos.LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM sos.patients a
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
JOIN sos.patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.html FORMAT HTML
;

Non-Insurance Balance By Place of Service, Date Range, and Provider

Posted on | by SOS Blog | Leave a Comment on Non-Insurance Balance By Place of Service, Date Range, and Provider

I am looking for a query that prints out total balance remaining on patients only (not insurance) by LOC code (e.g.,11 or 61) by date range by provider.

The following query gives the balance itemized by patient. To get just summary totals, remove “a.lastname,a.firstname,a.id” from the SELECT and GROUP BY clauses.

SELECT
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
sos.rv_charges a
JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN sos.payors c ON b.payornum = c.payornum
JOIN sos.poscodes d ON a.poscodenum = d.poscodenum
JOIN sos.patients e ON a.ptnum = e.ptnum
WHERE
a.licnum = 101 AND   /*look only at main data set*/
e.flag = 0 AND          /* just active list patients*/
c.payortype <> 'I'     /* ignore insurance splits*/
AND d.defcode IN ('11','61')    /* place of service code is 11 or 61*/
AND a.trandate BETWEEN '2009-01-01' AND '2009-03-31'    /* date range*/
GROUP BY
a.provcode,a.lastname,a.firstname,a.id

Future Dated Transactions And Splits

Posted on | by SOS Blog | Leave a Comment on Future Dated Transactions And Splits

Having trouble reconciling your aging reports? The likely reason is that you have charge splits or credit splits with dates in the future. Aging is based on SPLIT dates, not service dates or credit dates. On the charge side, SOS’s aging reports use the charge split date; on the credit side they use the Date Applied in the credit split.

Use the following queries to find splits and transactions with future dates:

/* --------- charges --------------*/

SELECT
  licnum AS "Dataset", (lastname + ', '+firstname+' / '+ id) AS "Patient",
  trandate AS "Service Date", provcode AS "Provider", srvcode AS "Service",
  amount AS "Fee", chgsplamt AS "Split Amt", chgspldate AS "Split Date",
  jnum AS "Journal#"
FROM
  sos.rv_charges
WHERE
  chgspldate > today() OR trandate > today()


/*--------- credits ------------*/

SELECT
  licnum AS "Dataset", fullnameid AS "Patient",
  cre_date AS "Credit Date", credtype AS "Credit Type",
  payorname AS "Payor", cre_amount AS "Total Credit",
  crsplamt AS "Split Amt", dateapplied AS "Date Applied",
  cre_jnum AS "Journal#"
FROM
  sos.rv_creditsplits
WHERE
  dateapplied > today() OR cre_date > today()