We looking for a report that will list all sessions that have been billed for each insurance company during a month. For example, the report would list out many sessions have been billed for MVP insurance for the month of October. Please let me know if this is possible.
In the following query, the output is directed to an HTML file that can be viewed in a web browser, or loaded in Excel for additional manipulation.
SELECT DISTINCT pay.payorname, pt.lastname, pt.firstname, pt.id, jou.trandate, srv.srvcode, prv.provcode, jou.amount FROM sos.journal jou JOIN sos.jcharges chg ON jou.jnum = chg.jnum JOIN sos.jchgsplits chs ON chg.jnum = chs.jnum JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.payors pay ON ptp.payornum = pay.payornum JOIN sos.patients pt ON jou.ptnum = pt.ptnum JOIN sos.services srv ON chg.servicenum = srv.servicenum JOIN sos.providers prv ON chg.providernum = prv.providernum WHERE pay.payortype = 'I' AND trandate BETWEEN '2011-11-01' AND '2011-11-30' ORDER BY pay.payorname, pt.lastname, pt.firstname, pt.id, jou.trandate ; OUTPUT TO c:\sos\insforperiod.html FORMAT HTML