You asked for a report that includes last payment information. This information is readily available in the database, but we have never included it in any of the standard reports.
SELECT
(a.Lastname + ', ' + a.Firstname) AS "PtName",
a.id,
TRIM(c.firstname + ' '+c.payorname) AS "Payor",
b.lastpaydate,
b.lastpayamt,
b.postedbal
FROM
sos.patients a
JOIN sos.ptpayors b ON a.ptnum = b.ptnum
JOIN sos.payors c ON b.payornum = c.payornum
WHERE
postedbal > 0
Breaks down credits applied to services in a date range groups by insurance payor.
Select
payorname,
payornum,
credtype,
count(distinct cre_jnum) as "Number",
sum(crsplamt) as "Paid"
From
sos.rv_creditsplits
Where
srv_date between '2001-01-01' and '2001-01-31'
and payortype = 'I'
and systranflag not in ('TT','TF')
group by
credtype,
payornum,
payorname
Breaks down credits applied to services in a date range by type and groups by payor type.
Select
payortype,
Credtype,
count(distinct cre_jnum) as "Number",
sum(crsplamt) as "Paid"
From
sos.rv_creditsplits
Where
srv_date between '2001-01-01' and '2001-01-31'
and systranflag not in ('TT','TF')
group by
credtype,
payortype
SELECT
sum(chgsplamt) as "totalcharge",
defcode as "POS",
year(trandate) as "year"
FROM
sos.rv_charges a join sos.poscodes b on a.poscodenum=b.poscodenum
GROUP BY POS,year
SELECT
year(c.trandate) as YEAR,
defcode as POS,
sum(crsplamt) as "TOTAL PAID"
FROM
sos.jcrsplits a
join sos.jcredits b
join sos.journal c
join sos.rv_charges d on a.chgsplnum=d.chgsplnum
join sos.poscodes e on d.poscodenum=e.poscodenum
WHERE
b.credtype <> 'Adjustment'
GROUP BY year,pos
ORDER BY year,pos
Simple Software Designs, LLC, dba Synergistic Office Solutions