-
AuthorPosts
-
June 11, 2013 at 12:02 pm #31393
How do we add patient category {private or insurance} in to a financial report?
June 11, 2013 at 3:28 pm #33175Support
KeymasterHi Dee – thanks for your post. The answer is it depends on which financial report you have in mind – please let us know and we’ll provide instructions.
June 20, 2013 at 3:12 pm #33176Hi
I am trying to run a report detailing private vs insurance patients on the invoice list report template unless there is an easier way of obtaining the information.
Thanks for your help
DeeJune 21, 2013 at 9:20 am #33177William
KeymasterHi Dee
If it is just a list of patients that have the patient category of Private or Insurance you could always use the main Patient grid in ClinicOffice or the Patient List and then search on Patient Category.
With this you can search on both categories and under Grid Options select the “By Category” layout. This will group the grid by Private and Insurance and will give you a sub total of the number of records under each group. Alternatively you could just search on each category separately and view the total count of records at the bottom of the grid.
Would this be sufficient or would you prefer to be able to see the amount of money each group have been invoiced/paid for?
June 21, 2013 at 3:06 pm #33178Thanks for your answer we do need to know the financial side and need to put in a time span, we just want to add the insurnace/private criteria into the search items on the invoices list!
Thanks DeeJune 24, 2013 at 9:39 am #33179William
KeymasterThat will be no problem Dee. To add the Patient Category to the Invoice List please follow the steps below.
[1] Right click on the Invoice List report and select Copy
[2] Right click on this copied report and select Rename
[3] Enter a new name like “Invoice List with Patient Categories”
[4] Click OK
[5] Right click on the report and select Report Source
[6] Click on the SQL Source tab
[7] Delete any text present in this window
[8] Copy all of the the SQL belowselect inv.id, invoicenum, datetime, duedate, inv.discpercent, discamount, nettotal, taxtotal, total, printed,
inv.per_id as recipient_per_id, trim(recipper.lastname || ', ' || coalesce(recipper.firstname,'') || ' ' || coalesce(recipper.title,'')) as _recipient,
inv.clinic_id, clinic.name as _clinicname,
inv.staff_id,
(case
when inv.staff_id is null then ''
else staff.knownas
end) as _staffmember,
inv.room_id, room.name as _roomname,
inv.apptype_id, apptype.name as _apptype,
inv.patient_per_id, trim(patper.lastname || ', ' || coalesce(patper.firstname,'') || ' ' || coalesce(patper.title,'')) as _patient,
patper.code, patper.insuranceref,
(coalesce(clinic.prefix, '') || cast(inv.invoicenum as varchar(20))) as _invoiceref,
coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,
(inv.total-coalesce(view_invamtalloc.amtalloc, 0)) as outstanding,
cast(
case
when (inv.total = 0) or (view_invamtalloc.amtalloc >= inv.total) then
'Paid In Full'
when (view_invamtalloc.amtalloc > 0) and (view_invamtalloc.amtalloc < inv.total) then
'Part Paid'
else
'Unpaid'
end as varchar(20)) as _status,
(case when (inv.total > 0) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)
then (cast((current_date - cast(inv.datetime as date)) as integer))
else cast(null as integer) end) as _ageing,
(case when (inv.total > 0) and (current_date > duedate) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)
then true
else false end) as _overdue,
inv.createdon, inv.createdby_staff_id, cr_staff.knownas as _createdby,
inv.updatedon, inv.updatedby_staff_id, up_staff.knownas as _lastupdatedby,
patper.patcat_id, patcat.name as _patient_category
from inv
inner join per as recipper on (inv.per_id = recipper.id)
inner join clinic on (inv.clinic_id = clinic.id)
left join staff on (inv.staff_id = staff.id)
left join room on (inv.room_id = room.id)
left join apptype on (inv.apptype_id = apptype.id)
left join per as patper on (inv.patient_per_id = patper.id)
left join staff as cr_staff on (inv.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (inv.updatedby_staff_id = up_staff.id)
left join view_invamtalloc on (inv.id = view_invamtalloc.inv_id)
left join patcat on (patper.patcat_id=patcat.id)[9] Paste this into the SQL Source window
[10] Click OKWhen you run this report you will see whether a patient is of the private or insurance categories. Please let us know if you have any problems with the above instructions.
June 25, 2013 at 5:37 pm #33180Thats excellent – Many thanks for your help =D>
Dee -
AuthorPosts
- You must be logged in to reply to this topic.