Skip to content

Legacy SQL

Total MP Sales

1
SELECT sum(total), sum(refund_amount) FROM `pharmacy_invoices` WHERE paid_on >= '2020-02-01'AND paid_on < '2020-03-01' AND settings_type != 'ArtesanalPayment'

Total Weekly Dosing

1
SELECT sum(total) FROM `payment_invoices` WHERE type = 'Weekly' AND paid_on > '2020-02-19 07:18:49'

Total Monthly Subs

1
SELECT * FROM `payment_invoices` WHERE type = 'Monthly' AND paid_on > '2020-05-01 07:18:49'

Failed Payments

1
2
3
4
SELECT inv.number, inv.status, inv.type, inv.price, inv.due_on, pr.practice_name FROM `payment_invoices` as inv LEFT JOIN `payment_orders` as orders on inv.order_id = orders.id LEFT JOIN `practices` as pr on orders.practice_id = pr.practice_id
WHERE inv.status = 'failed'
AND inv.due_on > '2019-04-01'
ORDER BY inv.due_on DESC

Move patients from one doctor to another

1
UPDATE `patient_info` SET doctor_id = <NEW DOCTOR> WHERE doctor_id = <OLD DOCTOR>

Practices who have ordered from specific vendor

1
SELECT pr.practice_name, count(ord.id) FROM `practices` as pr LEFT JOIN `order` as ord on ord.buyer_id = pr.practice_id WHERE ord.seller_id = 'fffae2f6-c726-4c08-b68d-d3d7f1723369' GROUP BY ord.buyer_id

Spend By Practice

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
    pr.practice_name,
    sum(total),
    sum(refund_amount),
    MAX(inv.paid_on)
FROM `pharmacy_invoices` as inv
LEFT JOIN `order` as ord on (ord.id = inv.order_id)
LEFT JOIN `practices` as pr on (ord.buyer_id = pr.practice_id)
AND settings_type != 'ArtesanalPayment'
GROUP BY pr.practice_name

Current Legacy Subscription Customers

1
2
3
4
5
6
7
SELECT pr.practice_name, sum(inv.total) FROM `payment_invoices` as inv 
    LEFT JOIN `payment_orders` as ord on (ord.id = inv.order_id) 
    LEFT JOIN `practices` as pr on (pr.practice_id = ord.practice_id)
WHERE inv.type = 'Monthly'
    AND inv.total > 0
    AND inv.paid_on > '2020-05-01 07:18:49'
GROUP BY pr.practice_name