> 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 <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
