SELECT
r.provcode,
r.provlname,
SUM(j.amount)
FROM
sos.journal j
JOIN sos.patients p on j.ptnum=p.ptnum
JOIN sos.providers r on p.providernum=r.providernum
WHERE
j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
j.sortcode IS NULL
GROUP BY
r.provcode, r.provlnamee
Category: Queries – Accounting
Total Credits Without Sort Codes By Primary Provider
SELECT
r.provcode,
r.provlname,
SUM(j.amount)
FROM
sos.journal j
JOIN sos.patients p on j.ptnum=p.ptnum
JOIN sos.providers r on p.providernum=r.providernum
WHERE
j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
j.sortcode IS NULL
GROUP BY
r.provcode, r.provlname
Total Amount In Collections
Once we change a Patient’s Billing Options to “date turned over to collections”, this patient now appears as the color red in the Active Patients List.
Is there a way to determine the amount of balance that all those patients in red have–in total?
SELECT
SUM(postedbal - ageunapplied) AS "AmtInCollections"
FROM
sos.ptpayors a
JOIN sos.payors b ON a.payornum = b.payornum
WHERE
b.collectdate IS NOT NULL
Service Count By Provider, Patient Category and Service Code For Period
I need total # of services rendered broken down by provider, patient category, and service code, for a specific time frame.
SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
c.categcode AS "Category",
a.srvcode AS "Service",
COUNT(DISTINCT jnum) AS "Srv Count"
FROM
sos.rv_charges a
JOIN sos.patients b ON a.ptnum = b.ptnum
LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
a.trandate BETWEEN '1980-01-01' AND '2008-12-31'
GROUP BY
"Provider","Category","Service"
Payments by Service and Sort Code for Period
I need a break down of payments totalled by Service Code and Sort Code. We use the SortCode field on the Charge entry to indicate the facility where the services were rendered.
SELECT
srvcode, COALESCE(lucode,'None') AS "Facility", sum(crsplamt) AS Payments
FROM
sos.rv_creditsplits a LEFT OUTER JOIN sos.lookups b ON a.srv_sortcode = b.lunum
WHERE
CredType <> 'Adjustment'
AND
Srv_Date BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
srvcode, "Facility"
ORDER BY
srvcode, "Facility"
