Health Datasets/Australian Medical Benefits Scheme (MBS) and Pharmaceutical Benefits Scheme (PBS)/Useful SQL Queries

The number of prescriptions and total quantity/30 in a following 3 month window

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

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;