Hello all,
yes, I know, "row number" isn't a concept that fits into the
relational model and I will only be asking for something
similar.
explanation (actual views below)
--------------------------------
I have a view that holds the vaccinations scheduled for a
patient (v_vaccs_scheduled4pat) depending on what vaccination
regimes that patient is on. There are typically between 1 to 5
vaccinations per disease (indication/regime) which is expressed
in the vaccination sequence number. Some regimes also have
booster shots scheduled. Those boosters are to be given
regularly after a set interval. Those have the sequence number
field set to NULL.
There is a second view that lists all the vaccinations
actually given to a patient per regime (v_pat_vacc4ind).
This view has dates when the shot was given but no sequence
number.
I now want to create a view which correlates the two showing
me which actual vaccination corresponds to which scheduled
vaccination. This is what I cannot get my head wrapped around
although it is probably fairly straightforward.
The conceptual solution would be to order actual vaccinations
by date per regime and number them (remember the "row number"
in the subject line ?). One would then join on that with the
sequence numbers from the scheduled vaccinations view and treat
any actual vaccinations where "row number" > max(sequence
number) as being boosters (medically this is correct, btw).
Yes, there can and will be several boosters for some regimes.
raw data
--------
full schema here:
http://hherb.com/gnumed/schema/
full schema defs in CVS here:
http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/gmClinicalViews.sql
relevant excerpt:
--- ==========================================================
--- vaccination stuff
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_vacc_regimes;
\set ON_ERROR_STOP 1
create view v_vacc_regimes as
select
vreg.id as pk_regime,
vind.description as indication,
_(vind.description) as l10n_indication,
vreg.name as regime,
coalesce(vreg.comment, '') as comment,
vreg.fk_indication as pk_indication,
vreg.fk_recommended_by as pk_recommended_by
from
vacc_regime vreg,
vacc_indication vind
where
vreg.fk_indication = vind.id
;
comment on view v_vacc_regimes is
'all vaccination schedules known to the system';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_vacc_defs4reg;
\set ON_ERROR_STOP 1
create view v_vacc_defs4reg as
select
vreg.id as pk_regime,
vind.description as indication,
_(vind.description) as l10n_indication,
vreg.name as regime,
coalesce(vreg.comment, '') as reg_comment,
vdef.is_booster as is_booster,
vdef.seq_no as vacc_seq_no,
vdef.min_age_due as age_due_min,
vdef.max_age_due as age_due_max,
vdef.min_interval as min_interval,
coalesce(vdef.comment, '') as vacc_comment,
vind.id as pk_indication,
vreg.fk_recommended_by as pk_recommended_by
from
vacc_regime vreg,
vacc_indication vind,
vacc_def vdef
where
vreg.id = vdef.fk_regime
and
vreg.fk_indication = vind.id
order by
indication,
vacc_seq_no
;
comment on view v_vacc_defs4reg is
'vaccination event definitions for all schedules known to the system';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_vacc_regs4pat;
\set ON_ERROR_STOP 1
create view v_vacc_regs4pat as
select
lp2vr.fk_patient as pk_patient,
vvr.indication as indication,
vvr.l10n_indication as l10n_indication,
vvr.regime as regime,
vvr.comment as comment,
vvr.pk_regime as pk_regime,
vvr.pk_indication as pk_indication,
vvr.pk_recommended_by as pk_recommended_by
from
lnk_pat2vacc_reg lp2vr,
v_vacc_regimes vvr
where
vvr.pk_regime = lp2vr.fk_regime
;
comment on view v_vacc_regs4pat is
'selection of configured vaccination schedules a patient is actually on';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_vaccs_scheduled4pat;
\set ON_ERROR_STOP 1
create view v_vaccs_scheduled4pat as
select
vvr4p.pk_patient as pk_patient,
vvr4p.indication as indication,
vvr4p.l10n_indication as l10n_indication,
vvr4p.regime as regime,
vvr4p.comment as reg_comment,
vvd4r.is_booster,
vvd4r.vacc_seq_no,
vvd4r.age_due_min,
vvd4r.age_due_max,
vvd4r.min_interval,
vvd4r.vacc_comment as vacc_comment,
vvr4p.pk_regime as pk_regime,
vvr4p.pk_indication as pk_indication,
vvr4p.pk_recommended_by as pk_recommended_by
from
v_vacc_regs4pat vvr4p,
v_vacc_defs4reg vvd4r
where
vvd4r.pk_regime = vvr4p.pk_regime
;
comment on view v_vaccs_scheduled4pat is
'vaccinations scheduled for a patient according
to the vaccination schedules he/she is on';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_pat_vacc4ind;
\set ON_ERROR_STOP 1
create view v_pat_vacc4ind as
select
v.fk_patient as pk_patient,
v.id as pk_vaccination,
v.clin_when as date,
vind.description as indication,
_(vind.description) as l10n_indication,
vcine.trade_name as vaccine,
vcine.short_name as vaccine_short,
v.batch_no as batch_no,
v.site as site,
coalesce(v.narrative, '') as narrative,
vind.id as pk_indication,
v.fk_provider as pk_provider,
vcine.id as pk_vaccine,
vpep.pk_health_issue as pk_health_issue,
v.fk_episode as pk_episode,
v.fk_encounter as pk_encounter
from
vaccination v,
vaccine vcine,
lnk_vaccine2inds lv2i,
vacc_indication vind,
v_pat_episodes vpep
where
vpep.pk_episode=v.fk_episode
and
v.fk_vaccine = vcine.id
and
lv2i.fk_vaccine = vcine.id
and
lv2i.fk_indication = vind.id
;
comment on view v_pat_vacc4ind is
'vaccinations a patient has actually received for the various indications';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_pat_missing_vaccs;
\set ON_ERROR_STOP 1
create view v_pat_missing_vaccs as
select
vvs4p.pk_patient,
vvs4p.indication,
vvs4p.l10n_indication,
vvs4p.regime,
vvs4p.reg_comment,
vvs4p.vacc_seq_no as seq_no,
case when vvs4p.age_due_max is null
then (now() + coalesce(vvs4p.min_interval, vvs4p.age_due_min))
else ((select identity.dob from identity where
identity.id=vvs4p.pk_patient) + vvs4p.age_due_max)
end as latest_due,
--- note that ...
--- ... 1) time_left ...
case when vvs4p.age_due_max is null
then coalesce(vvs4p.min_interval, vvs4p.age_due_min)
else (((select identity.dob from identity where
identity.id=vvs4p.pk_patient) + vvs4p.age_due_max) - now())
end as time_left,
--- ... and 2) amount_overdue ...
case when vvs4p.age_due_max is null
then coalesce(vvs4p.min_interval, vvs4p.age_due_min)
else (now() - ((select identity.dob from identity where
identity.id=vvs4p.pk_patient) + vvs4p.age_due_max))
end as amount_overdue,
--- ... are just the inverse of each other
vvs4p.age_due_min,
vvs4p.age_due_max,
vvs4p.min_interval,
vvs4p.vacc_comment,
vvs4p.pk_regime,
vvs4p.pk_indication,
vvs4p.pk_recommended_by
from
v_vaccs_scheduled4pat vvs4p
where
vvs4p.is_booster is false
and
vvs4p.vacc_seq_no > (
select count(*)
from v_pat_vacc4ind vpv4i
where
vpv4i.pk_patient = vvs4p.pk_patient
and
vpv4i.indication = vvs4p.indication
)
;
comment on view v_pat_missing_vaccs is
'vaccinations a patient has not been given yet according
to the schedules a patient is on and the previously
received vaccinations';
--- -----------------------------------------------------
\unset ON_ERROR_STOP
drop view v_pat_missing_boosters;
\set ON_ERROR_STOP 1
--- FIXME: only list those that DO HAVE a previous vacc (max(date) is not null)
create view v_pat_missing_boosters as
select
vvs4p.pk_patient,
vvs4p.indication,
vvs4p.l10n_indication,
vvs4p.regime,
vvs4p.reg_comment,
vvs4p.vacc_seq_no as seq_no,
coalesce(
((select max(vpv4i11.date)
from v_pat_vacc4ind vpv4i11
where
vpv4i11.pk_patient = vvs4p.pk_patient
and
vpv4i11.indication = vvs4p.indication
) + vvs4p.min_interval),
(now() - '1 day'::interval)
) as latest_due,
coalesce(
(now() - (
(select max(vpv4i12.date)
from v_pat_vacc4ind vpv4i12
where
vpv4i12.pk_patient = vvs4p.pk_patient
and
vpv4i12.indication = vvs4p.indication) +
vvs4p.min_interval)
),
'1 day'::interval
) as amount_overdue,
vvs4p.age_due_min,
vvs4p.age_due_max,
vvs4p.min_interval,
vvs4p.vacc_comment,
vvs4p.pk_regime,
vvs4p.pk_indication,
vvs4p.pk_recommended_by
from
v_vaccs_scheduled4pat vvs4p
where
vvs4p.is_booster is true
and
vvs4p.min_interval < age (
(select max(vpv4i13.date)
from v_pat_vacc4ind vpv4i13
where
vpv4i13.pk_patient = vvs4p.pk_patient
and
vpv4i13.indication = vvs4p.indication
))
;
comment on view v_pat_missing_boosters is
'boosters a patient has not been given yet according
to the schedules a patient is on and the previously
received vaccinations';
Thanks,
Karsten Hilbert, MD, PhD
GnuMed i18n coordinator
http://www.gnumed.org
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
