Re: row numbering

> I don't know that much about medicine, so this might be a funny
> question, but do you really need to know that "shots 4 and 5 are
> missing",
I want to be able to display "shot 4: ..." and "shot 5: ..." in
the application but pull the data from the database, not
calculate it in the application. Reason being that there
are/can be several different clients that should all be getting
this right. Our current view does just that but only with shots
already given.

> or just that the patient needs to be shot two more times,
No, that would simply be

select max(shots in schedule) - count(shots given) where patient = <someone>

> or do you really want the *application dates*?
No. Those are under more conditions than the database should
have to handle. IOW I would not suggest putting *that* much
business logic into the database. There would be some value in
getting the *by-schedule* next application date but that's of
secondary importance to me.

> This is a description of steps you decided would get you to your
> goal. Instead of describing the steps, what's the goal?
Sure, fine.

I want a view that roughly looks like this:

pk_patient schedule vacc_no given
1 Tet 1 1.1.1980
1 Tet 2 1.1.1985
1 Hep 1 1.1.1980
1 Hep 2 NULL -> IOW missing
2 ... ...
...

Why is there no missing Tet shot ? Because the schedule does
not define more than 2 shots. Medically, this is a bad example
because Tet actually requires more and also requires a booster
every 10 years but that does not make a difference to the
problem at hand.

The less technical goal is: I want the database to be able to
provide a vaccination *status* view to me.

Also notice that we do have views that display the missing
shots per schedule per patient. I just have not found a way to
join the two views (that is, given and missing) because that
would AFAICT require the output "row numbering".

You can look up our complete schema in our Wiki:

http://salaam.homeunix.com/twiki/bin/view/Gnumed/WebHome

Go to Deverloper Guide -> Database Structure.

Karsten Hilbert, MD
GnuMed i18n coordinator
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346