Skip site navigation (1) Skip section navigation (2)

Re: need ``row number``

From: "Ian Harding" <iharding(at)tpchd(dot)org>
To: <Karsten(dot)Hilbert(at)gmx(dot)net>, <pgsql-general(at)postgresql(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: need ``row number``
Date: 2004-09-24 00:43:10
Message-ID: s1530b77.094@mail.tpchd.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
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.

<<< 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

                        

Responses

pgsql-sql by date

Next:From: Karsten HilbertDate: 2004-09-24 07:21:17
Subject: Re: need ``row number``
Previous:From: Karsten HilbertDate: 2004-09-23 20:24:17
Subject: need "row number"

pgsql-general by date

Next:From: Robert FitzpatrickDate: 2004-09-24 00:58:38
Subject: Re: Returning recordsets with functions
Previous:From: Alvaro HerreraDate: 2004-09-24 00:34:55
Subject: Re: Restore a especific function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group