- From: Kashyap, Vipul B8MM <Vipul.Kashyap@CIGNA.COM>
- Date: Mon, 13 Oct 2008 13:08:28 -0400
- To: "Eric Prud'hommeaux" <eric@w3.org>
- cc: public-hcls-coi@w3.org
- Message-ID: <CC50B57203ABFB479BBC7A718DFFB97A05D590DB@wldemb002.internal.cigna.com>
Eric,
Thanks for sending these across. Let's try to match these queries to the
demo scenarios attached with this e-mail in tomorrow's conference call.
Cheers,
---Vipul
Vipul Kashyap, PhD | Director, Clinical Programs | CIGNA Healthcare |
Office: 860.226.8048 | Mobile: 860.334.9004 | vipul.kashyap@cigna.com
-----Original Message-----
From: public-hcls-coi-request@w3.org
[mailto:public-hcls-coi-request@w3.org] On Behalf Of Eric Prud'hommeaux
Sent: Saturday, October 11, 2008 12:12 PM
To: public-hcls-coi@w3.org
Subject: Re: Re: COI protocol #8 demo progress
I've created an sdtm query - sdtm.rq
to query data mapped from hl7 - hl7-sdtm.rq
which is mapped from the db - db-hl7.rq
I poked around to look for things like the relationship between a
patient and a medication, but am not super confident that I've aligned
with existing structures (say, in XML).
You can try this at home by downloading an executable for your
operating system from http://www.w3.org/2008/04/SPARQLfed/ and running
cat sdtm.rq | \
SWtransformer -q - hl7-sdtm.rq | \
SWtransformer -q - db-hl7.rq -s http://hospital.example/DB/ | \
mysql -u root DiabeticPatientsDataSet --table
where the db comes from
http://www.w3.org/2008/04/DiabeticPatientsDataSet/
* Eric Prud'hommeaux <eric@w3.org> [2008-10-01 05:45-0400]
> I've extended these queries to include (contra)indications by
ingredient:
> [[
> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
> PREFIX Person: <http://hospital.example/DB/Person#>
> PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
> PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
> PREFIX Medication: <http://hospital.example/DB/Medication#>
> PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
> PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
>
> SELECT ?person ?dob ?sex ?takes ?contraItem WHERE {
> ?person Person:MiddleName ?middleName ;
> Person:DateOfBirth ?dob ;
> Person:SexDE ?sexEntry .
> ?sexEntry Sex_DE:EntryName ?sex .
>
> ?indicItem Item_Medication:PatientID ?person ;
> Item_Medication:EntryName ?takes .
> ?indicMed Medication:ItemID ?indicItem ;
> Medication:MedDictDE ?indicDE .
> ?indicDE Medication_DE:NDC ?indicNDC .
> ?indicCode NDCcodes:NDC ?indicNDC ;
> NDCcodes:ingredient 6809 .
>
> OPTIONAL {
> ?contraItem Item_Medication:PatientID ?person .
> ?contraMed Medication:ItemID ?contraItem ;
> Medication:MedDictDE ?contraDE .
> ?contraDE Medication_DE:NDC ?contraNDC .
> ?contraCode NDCcodes:NDC ?contraNDC ;
> NDCcodes:ingredient 11289
> }
> FILTER (?dob < "1984-01-01T00:00:00"^^xsd:dateTime &&
!BOUND(?contraItem))
> } LIMIT 30
> ]]
>
> yielding:
> [[
>
+---------+---------------------+--------+------------------------------
-------------+
> | person | dob | sex | takes
|
>
+---------+---------------------+--------+------------------------------
-------------+
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS
|
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS
|
> | 1517000 | 1958-01-01 00:00:00 | Male | Metformin HCl 500 MG Tablet
|
> | 1517000 | 1958-01-01 00:00:00 | Male | Metformin HCl 500 MG Tablet
|
> | 1517403 | 1981-05-11 00:00:00 | Male | Metformin HCl 500 MG Tablet
|
> | 1505583 | 1950-02-25 00:00:00 | Female | Metformin HCl 500 MG Tablet
|
> | 1234561 | 1983-01-02 00:00:00 | Male | GlipiZIDE-Metformin HCl
2.5-250 MG Tablet |
> | 1234561 | 1983-01-02 00:00:00 | Male | GlipiZIDE-Metformin HCl
2.5-250 MG Tablet |
> | 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl
2.5-250 MG Tablet |
> | 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl
2.5-250 MG Tablet |
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS
|
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS
|
>
+---------+---------------------+--------+------------------------------
-------------+
> ]]
>
>
> The birthdate limitation is set for < 1984-01-01 in order to eliminate
>
+---------+---------------------+------+--------------------------------
-----------+
> | person | dob | sex | takes
|
>
+---------+---------------------+------+--------------------------------
-----------+
> | 1234563 | 1983-02-25 00:00:00 | Male | GlipiZIDE-Metformin HCl
2.5-250 MG Tablet |
>
+---------+---------------------+------+--------------------------------
-----------+
>
> The dupes appear to come from identical Medication.ItemID for probably
> different dates:
> [[
> mysql> select * from Medication where ItemID=10276300039;
>
+-------------+-------------+--------------------+-------------+------+-
-------+------------------------+--------------------+------------+-----
-----------+-----------+
> | ID | ItemID | FormOfMedicationDE | FreqUnitsDE | Dose
| Refill | RoutOfAdministrationDE | QuantityToDispense | DaysToTake |
PrescribedByID | MedDictDE |
>
+-------------+-------------+--------------------+-------------+------+-
-------+------------------------+--------------------+------------+-----
-----------+-----------+
> | 10276300041 | 10276300039 | 0 | 2 | 1
| 0 | 15 | 0 | 0 |
1524566 | 90346 |
> | 10307800003 | 10276300039 | 0 | 2 | 1
| 0 | 15 | 0 | 0 |
1524566 | 90346 |
>
+-------------+-------------+--------------------+-------------+------+-
-------+------------------------+--------------------+------------+-----
-----------+-----------+
> ]]
> Fixing date ranges in the query will help this, I expect.
>
>
> * Eric Prud'hommeaux <eric@w3.org> [2008-10-01 01:32-0400]
> > Given some data that Helen added to db:
> > <http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockPerson.csv>
> >
<http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockMedication.csv>
> >
<http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockItem_Medication.c
sv>
> > and the latest version of SWObjects:
> > <http://www.w3.org/2008/04/SPARQLfed/>
> > I tested the SPARQL-SQL mapping on a sample query
> > tests/execute_HealthCare1 tests/query_stem:Person.rq -s
http://hospital.example/DB/
> > <http://www.w3.org/2008/04/SPARQLfed/tests/query_stem:Person.rq>
> >
> > got an SQL query:
> > [[
> > SELECT person.id AS person, person.DateOfBirth AS dob,
sexEntry.EntryName AS sex, medication.id AS medication, opt1.contraIndic
AS contraIndic
> > FROM Person AS person
> > INNER JOIN Sex_DE AS sexEntry ON
sexEntry.id=person.SexDE
> > INNER JOIN Item_Medication AS medication ON
medication.PatientID=person.id AND
medication.EntryName="GlipiZIDE-Metformin HCl 2.5-250 MG Tablet"
> > LEFT OUTER JOIN (
> > SELECT contraIndic.PatientID AS person, contraIndic.id AS
contraIndic
> > FROM Item_Medication AS contraIndic
> > WHERE contraIndic.EntryName="Warfarin Sodium 5 MG Tablet"
> > ) AS opt1 ON opt1.person=person.id
> > WHERE (person.DateOfBirth < "1980-01-01 00:00:00" AND
!(opt1.contraIndic IS NOT NULL))
> > LIMIT 10
> > ]]
> >
> > executed it, and got:
> >
+---------+---------------------+--------+-------------+-------------+
> > | person | dob | sex | medication | contraIndic
|
> >
+---------+---------------------+--------+-------------+-------------+
> > | 1234562 | 1963-12-27 00:00:00 | Female | 99999999003 | NULL
|
> > | 1234562 | 1963-12-27 00:00:00 | Female | 99999999004 | NULL
|
> >
+---------+---------------------+--------+-------------+-------------+
> >
> > I encourage folks to play more. Also, probably time to start working
on
> > the construct rules that allow the SPARQL query to work over HL7
data.
> >
> > Holger, could you build a DOS/WIN version of the code and distribute
it
> > or make it available to folks who want to help?
> > --
> > -eric
> >
> > office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
> > mobile: +1.617.599.3509
> >
> > (eric@w3.org)
> > Feel free to forward this message to any list for any purpose other
than
> > email address distribution.
>
>
>
> --
> -eric
>
> office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
> mobile: +1.617.599.3509
>
> (eric@w3.org)
> Feel free to forward this message to any list for any purpose other
than
> email address distribution.
--
-eric
office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
mobile: +1.617.599.3509
(eric@w3.org)
Feel free to forward this message to any list for any purpose other than
email address distribution.
------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright 2008 CIGNA
==============================================================================
Attachments
- text/plain attachment: Demo Scenarios.txt
Received on Monday, 13 October 2008 17:09:09 UTC