Re: row numbering - Mailing list pgsql-general

>     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