Re: need ``row number`` - Mailing list pgsql-general

> 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.Hilbert@gmx.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