Re: need ``row number``

> You are going to need a set returning function. It will have
> to look up the expected boosters, the expected time elapsed,
> and return them with their sequence numbers if they exists.
> There is no easy way to do it in a view that I can think of.
I am not convinced I'll need a SRF. I am not trying to
calculate something that isn't there yet. I am just trying to
join two views appropriately. I might have to employ some
variant of Celko's integer helper table but I'm not sure how
to proceed.

Karsten

> <<< Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> 9/23 1:56p >>>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346