SQL: Posted Credits

There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator.

About

This report shows posted credits (where the money came into the office) of method CC (credit card), CA (cash), or CK (check) on a date you specify, along with the staff member who posted the credit in OP.

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

  • This pulls daysheeted and nondaysheeted credits! For example, if you run your daysheet at 4:15 pm, but then Ms. Test comes in at 4:55 to pay on her bill, this report will show Ms. Test, but it WON'T BE ON YOUR DAYSHEET YET.
  • It only shows actual money received.  Transfers between sibs, etc. are NOT shown.
  • It doesn't show refunds and voids.  This is very important!   Let's say Rita Receptionist collects a $10 copay and posts it.   But then five-minutes later Receptionist Rita realizes that it is a checkup and no copay was due and she hands the $10 bill back to the mom and voids the credit in OP.  This report will show you that the $10 was collected, but it does not show you that the $10 was returned.

SQL Code: Firebird

To highlight and copy the code below to your clipboard, simply click the Copy button.

select pca_id as transaction_number, patno as patient_chart, date1, pat_amount,deposit_method, deposit_source, staffname   from patient_credit_acct
left outer join staff1 on staff1.staffid = patient_credit_acct.staffid where date1 = :date_to_check
and  pat_amount <> 0 and deposit_method in ('CC','CA','CK')


SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

select pca_id as transaction_number, patno as patient_chart, cast(date1 as date) as date_of_deposit, pat_amount,deposit_method, deposit_source, staffname   from patient_credit_acct
left outer join staff1 on staff1.staffid = patient_credit_acct.staffid where date1 = :date_to_check
and  pat_amount <> 0 and deposit_method in ('CC','CA','CK')