[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] sample SQL commands (queries) for the Report generato

From: Karsten Hilbert
Subject: Re: [Gnumed-devel] sample SQL commands (queries) for the Report generator?
Date: Sun, 30 Dec 2007 16:06:21 +0100
User-agent: Mutt/1.5.17 (2007-11-01)

On Sat, Dec 29, 2007 at 09:07:43PM -0800, James Busser wrote:

> Anyone care to suggest any, including sample syntax with sample values?

There even is a button [Share] right in the report generator
which emails the query to this very list ;-)

The report generator comes with (a few) pre-fabricated
queries. Try hitting down-arrow in or enter "*" into the
empty "report" field which should - phrasewheely - drop down
a list.

> I can add them to the wiki page
Yes, please !

> - a query that would result the number of distinct patients in the database

        select count(*) from dem.identity

perhaps adding

        where deleted is FALSE / TRUE


        where deceased is NULL / NOT NULL

depending on what is wanted.

> - a query that would find patients based on a medication that is being used
We don't store medications yet ...

> - a query that can search for patients based on the diagnostic code

        select *
                        inner join
                        using (pk_identity)
                code = ...
                and coding_system = ...
                and soap_cat = ...

Note that clin.v_coded_item_narrative is new in gnumed_v9.
> (would this come from clin_diag or maybe Clin.Coded_Narrative
That depends on what one wants to see in the result of the
query. clin.coded_narrative simply lists codes for terms
under coding systems as known to the local GNUmed
installation. It doesn't bear relationship to what's in a
particular patient's narrative.

> - a query that would help by providing more fields (and sample values) that 
> could be altered and used to find a patient when the standard patient 
> search field did not permit a patient to be found, perhaps including the 
> communication channels (phone numbers)

Such queries could be

        select *
                        inner join
                dem.v_person_comms / dem.v_person_jobs / 
                        using (pk_identity)
                dem.v_person_comms.url = ... /
                dem.v_person_jobs.l10n_occupation = ... /
                dem.v_external_ids4identity.value = ...

GPG key ID E4071346 @
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

reply via email to

[Prev in Thread] Current Thread [Next in Thread]