Personal tools
You are here: Home / OSCAR Users / EMR and Case Management Resources / Query By Example - Database Searches / Billings over the last year, discounted 15% for rostered patients
 

Billings over the last year, discounted 15% for rostered patients

Created by Ian Pun - I made this SQL query to count the amount billed per patient over the last year and discounts it accordingly to 15% if patient is rostered. HOWEVER IT DOES NOT TAKE INTO ACCOUNT out of the basket billings!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Enjoy! Ian PUN MD

Plain Text icon Billlings over the last year- discounted for rostered patients.txt — Plain Text, 468 bytes

File contents

SELECT b.demographic_no ,  concat (dm.last_name,', ', dm.first_name) as
name, dm.roster_status, COUNT(1) AS count, format(sum(b.total),2) as
Payment, 
 format( if (dm.roster_status = "RO", sum(b.total) *.15 , sum(b.total) ) , 2
) as Roster_Payment
FROM  billing_on_cheader1 b  , demographic dm 
WHERE  b.demographic_no = dm.demographic_no and b.billing_date >=
DATE_SUB(NOW(),INTERVAL 1 YEAR) and b.status <> "D"
GROUP BY b.demographic_no
ORDER BY count desc;

Document Actions

 

Download button

DOWNLOAD OSCAR FOR TESTING

 

eForms button

DOWNLOAD SHARED E-FORMS


 Customize button

FIND PLUG-INS AND TWEAKS
FOR YOUR OSCAR EMR

 

Subscribe Button

SUBSCRIBE TO DISCUSSION LIST 
(SEE ALL LISTS)

  

Help button

ACCESS THE ONLINE MANUALS
(PAID SUPPORT)