Health Datasets/Australian Medical Benefits Scheme (MBS) and Pharmaceutical Benefits Scheme (PBS)/Useful SQL Queries
Jump to navigation
Jump to search
The number of prescriptions and total quantity/30 in a following 3 month window
[edit | edit source]SELECT t0.pk, t0.spply_dt, t0.pin
, sum(t1.pbs_rgltn24_adjst_qty)/30 AS PE, count(*) AS trans_count
FROM pbs_rr t0
JOIN pbs_rr t1 USING (pin)
WHERE t1.spply_dt >= t0.spply_dt
AND t1.spply_dt < t0.spply_dt + 90
-- AND t0.pin = '00342416727' -- for a single pin
GROUP BY t0.pk
ORDER BY t0.spply_dt, t0.pk;
The number of pills in a 3,6,and 9 month window before and after a surgery event
[edit | edit source]with pillsAroundSurgery as (
select item, pin, m.date_of_service, sum(pbs_rgltn24_adjst_qty) as no_pills, floor( ( m.date_of_service - p.spply_dt )/182) as period
FROM mbs_surg1 m
JOIN pbs_surg1 p USING (pin)
where p.spply_dt >= m.date_of_service - 182*3
and p.spply_dt <= m.date_of_service + 182*3
group by pin, m.date_of_service, period, item
having count( distinct pin ) = 1
)
select item, period, round(avg(no_pills)) as avg_pills
from pillsAroundSurgery
group by item, period
order by item, period;