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

[Karsten Hilbert]

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
>       http://wiki.gnumed.de/bin/view/Gnumed/GnumedReportGenerator
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

and/or

        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 *
        from
                dem.v_basic_person
                        inner join
                clin.v_coded_item_narrative
                        using (pk_identity)
        where
                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 *
        from
                dem.v_basic_person
                        inner join
                dem.v_person_comms / dem.v_person_jobs / 
dem.v_external_ids4identity
                        using (pk_identity)
        where
                dem.v_person_comms.url = ... /
                dem.v_person_jobs.l10n_occupation = ... /
                dem.v_external_ids4identity.value = ...
        ;


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