Hi Kingsley, Thought maybe you'd find the attached queries of interest - it'd be great to see some cool PivotViewer stuff using our http://health.data.gov/sparql Virtuoso endpoint and CMS Hospital Compare data if/when you get the chance - or otherwise to learn from you how/why the endpoint might be more useful. George ---------- Forwarded message ---------- From: Doug Whitehead Date: Fri, Sep 16, 2011 at 5:04 PM Subject: Updated queries of interest To: George Thomas George, I've attached the updated queries of interest. The following things were changed: 1) Documented the example CMM queries through out 2) Instantiated query template 4 into examples 4.1 through 4.6 3) Instantiated query template 5 into examples 5.1 through 5.3 4) utilized un-named variables where I could (for a theoretical improvement in speed) 5) Fixed one error found (some sort of cut/paste error, I forget where...) 6) Added comments to some subgraphs Query 22 is the only query that times out. It is documented in the header of the query, but one needs to execute queries #18 and #7 as a workaround. (something about joining a query for a hospital with a VCard for its location makes Virtuoso go apesh*t) You be the judge, but I think this is ready for Kingsley. Doug CqldQueriesOfInterest.txt ####################################### ####################################### # # CQLD - Clinical Quality Linked Data # # Original Author: Doug Whitehead doug.whitehead@gmail.com # Date: September 16, 2011 # # # Values of "goodness" are relevant for a 3 dimensional filter. # Condition - describes the condition of the patient (e.g. where heart failure, asthma, etc) # Measure - describes how the hospital responded (e.g. where antibiotic given, vaccinnation, etc) # Metric - are qualitative criterion (e.g. where Cleanliness of Hospital is.., etc) # # The 3 dimensional filter is sparse. In other words, Not all Measures and Metrics # are relevant for a given Condition. And not all Conditions are relevant for a given # Measure, etc. # # Queries 1, 2, and 3 retrieve all instances of Condition, Measure, and Metric. # Queries 4 and 5 retrieve all instances given one or both of the other dimensions have FIXED values. # For example, if one were to select a Condition of interest, it would be FIXED. # Query 6 retrive all instances of Hospital. # # Results from Queries 1,2,3, and 6 should probably be stored in a Hashmap. As the # dynamic retrieval of an rdfs:label is particularly slow in Virtuoso. As such, it # radically slows down other queries. Queries 8 and above only fetch Ids for speed. # Often there will be a slow version (e.g. Query 8 and Query 8slow) provided for convenience. # # Values of "goodness" are datum retrieved. Examples of values of "goodness" are: # percentage - e.g. the percentage of patience readmitted within 30 days of surgury # footnote - a comment, e.g. the sample size was insufficient to be reliable # # Queries that return Values of "goodness" are constructed to return all kinds of # values of "goodness" (they are OPTIONAL). Different 3D filters will expect different # kinds of values of "goodness" (not every kind of value of "goodness" is relevant). # # For queries 7 and above, anytime you see an literal/instance, it is meant as a suggestion. # In other words, the proper literal/instance should be filled into the query at that point. # ####################################### # # Suggestion for understanding the CQLD domain: execute Query "8slow" below # # Notice how rows that have Conditions and Measures often don't have Metrics # # Notice how rows that have Measures and Metrics often don't have Conditions # as it may be part of a survey about the Hospital # ####################################### ####################################### ####################################### # # 1) List all ConditionIds and Conditions # # Every ConditionId has exactly one Condition (description). # Use this query to populate a Hashmap, so you don't have to use # Virtuoso to get the Condition from a ConditionId ever again # (as it slows down dynamic retrieval in other queries). # ####################################### # PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?conditionId ?condition WHERE { ?conditionId a comp:Condition. OPTIONAL { ?conditionId rdfs:comment ?condition. } } ORDER BY ?conditionId ####################################### # # 2) List all MeasureIds and Measures # # Every MeasureId has exactly one Measure (description). # Use this query to populate a Hashmap, so you don't have to use # Virtuoso to get the Measure from a MeasureId ever again # (as it slows down dynamic retrieval in other queries). # ####################################### # PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?measureId ?measure where { { _:subClass1 rdfs:subClassOf comp:Measure. _:subClass rdfs:subClassOf _:subClass1. ?measureId a _:subClass. } UNION { _:subClass rdfs:subClassOf comp:Measure. ?measureId a _:subClass. } UNION { ?measureId a comp:Measure. } ?measureId rdfs:comment ?measure. } ORDER BY ?measureId ####################################### # # 3) List all MetricIds and Metrics # # Every MetricId has exactly one Metric (description). # Use this query to populate a Hashmap, so you don't have to use # Virtuoso to get the Metric from a MetricId ever again # (as it slows down dynamic retrieval in other queries). # ####################################### # PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?metricId ?metric where { { _:subClass1 rdfs:subClassOf comp:Metric. _:subClass rdfs:subClassOf _:subClass1. ?metricId a _:subClass. } UNION { _:subClass rdfs:subClassOf comp:Metric. ?metricId a _:subClass. } UNION { ?metricId a comp:Metric. } ?metricId rdfs:comment ?metric. } ORDER BY ?metricId ####################################### # # 4) Find an Id of one type (ConditionId, MeasureId, MetricId) # that are relevant if ONE of the other two types are FIXED. # # ?desiredId the info desired # # ?desiredIdPredicate is one of: comp:condition, comp:measure, comp:metric # ?givenIdPredicate is one of: comp:condition, comp:measure, comp:metric # ?givenId is an object of ?givenIdPredicate # ####################################### # # DO NOT RUN THIS QUERY WITHOUT FILLING IN VALUES FOR # ?givenId ?givenIdPedicate, and ?desiredIdPredicate # ####################################### # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record ?givenIdPredicate ?givenId. _:record ?desiredIdPredicate ?desiredId. } ORDER BY ?desiredId ##################### # # 4.1 through 4.6 below are sample queries that can be run without modification # ##################### # # 4.1) What are the Measures that can be used in conjunction with Conditon 9? # (when the Outpatient has AMI/Chest Pain) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:condition . _:record comp:measure ?desiredId. } ORDER BY ?desiredId # # 4.2) What are the Metrics that can be used in conjunction with Conditon 9? # (when the Outpatient has AMI/Chest Pain) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:condition . _:record comp:metric ?desiredId. } ORDER BY ?desiredId # # 4.3) What are the Conditions that can be used in conjunction with Measure 39? # (where the patient was given an Aspirin at Arrival) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:measure . _:record comp:condition ?desiredId. } ORDER BY ?desiredId # # 4.4) What are the Metrics that can be used in conjunction with Measure 39? # (where the patient was given an Aspirin at Arrival) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:measure . _:record comp:metric ?desiredId. } ORDER BY ?desiredId # # 4.5) What are the Conditions that can be used in conjunction with Metric 1? # (where the performance was "90th Percentile") # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:metric . _:record comp:condition ?desiredId. } ORDER BY ?desiredId # # 4.6) What are the Measures that can be used in conjunction with Metric 1? # (where the performance was "90th Percentile") # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:metric . _:record comp:measure ?desiredId. } ORDER BY ?desiredId ####################################### # # 5) Find an Id of one type (ConditionId, MeasureId, MetricId) # that are relevant if BOTH of the other two types are FIXED. # # ?desiredId the info desired # # ?desiredIdPredicate is one of: comp:condition, comp:measure, comp:metric # ?givenIdPredicate1 is one of: comp:condition, comp:measure, comp:metric # ?givenId1 is an object of ?givenIdPredicate1 # ?givenIdPredicate2 is one of: comp:condition, comp:measure, comp:metric # ?givenId2 is an object of ?givenIdPredicate2 # ####################################### # # DO NOT RUN THIS QUERY WITHOUT FILLING IN VALUES FOR: # ?givenId1 ?givenIdPedicate1, ?givenId2, ?givenIdPredicate2 and ?desiredIdPredicate # ####################################### # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record ?givenIdPredicate1 ?givenId1. _:record ?givenIdPredicate2 ?givenId2. _:record ?desiredIdPredicate ?desiredId. } ORDER BY ?desiredId ##################### # # 5.1 through 5.3 below are sample queries that can be run without modification # ##################### # # 5.1) What are the Metrics that can be used # in conjunction with Condition 2 and Measure 27? # (where the patient had a heart failure condition, and died within 30 days) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:condition . _:record comp:measure . _:record comp:metric ?desiredId. } ORDER BY ?desiredId # # 5.2) What are the Measures that can be used # in conjunction with Condition 2 and Metric 13? # (where the patient had a heart failure condition, # and the hospital mortality rate is the same as the national average) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:condition . _:record comp:metric . _:record comp:measure ?desiredId. } ORDER BY ?desiredId # # 5.3) What are the Conditions that can be used # in conjunction with Measure 27 and Metric 13? # (where the patient died within 30 days, # and the hospital mortality rate is the same as the national average) # PREFIX comp: SELECT DISTINCT ?desiredId WHERE { _:record comp:measure . _:record comp:metric . _:record comp:condition ?desiredId. } ORDER BY ?desiredId ####################################### # # 6) List all HospitalIds and Hospitals. # # Every HospitalId has exactly one Hospital (description). # Use this query to populate a Hashmap, so you don't have to use # Virtuoso to get the Hospital from a HospitalId ever again # (as it slows down dynamic retrieval in other queries). # ####################################### # PREFIX hosp: PREFIX rdfs: SELECT DISTINCT ?hospitalId ?hospital WHERE { ?hospitalId a hosp:Hospital. ?hospitalId rdfs:label ?hospital. } # # 6.1) Count the number of Hospitals # PREFIX hosp: SELECT COUNT( ?hospitalId ) as ?numberOfHospitals WHERE { { SELECT DISTINCT ?hospitalId WHERE { ?hospitalId a hosp:Hospital. } } } ####################################### # # 7) Retrieve metadata/contact information about a Hospital # (Fair Oaks Hospital, in this example) # # In this and in all following queries, instances/literals found in the query # such as are merely examples. # You should replace it with the hospitalId of the hospital for which you want information. # ####################################### # PREFIX rdfs: PREFIX gd: PREFIX hosp: PREFIX ns: PREFIX vcard: SELECT ?lat ?lon ?hospital ?hospitalType ?hasER ?phone ?streetAddr ?city ?stateCode ?countyCode ?zip ?ownershipType WHERE { { SELECT ?lat ?lon ?phone ?city ?zip ?streetAddr WHERE { hosp:site ?site. ?site ns:siteAddress ?vcard. OPTIONAL { ?vcard vcard:latitude ?lat. } OPTIONAL { ?vcard vcard:longitude ?lon. } OPTIONAL { ?vcard vcard:tel ?phone. } OPTIONAL { ?vcard vcard:locality ?city. } OPTIONAL { ?vcard vcard:postal-code ?zip. } OPTIONAL { ?vcard vcard:street-address ?streetAddr. } } LIMIT 1 } rdfs:label ?hospital. gd:stateCode ?stateId. ?stateId rdfs:label ?stateCode. gd:countyCode ?countyCode. hosp:ownership ?ownershipType. hosp:type ?hospitalType. hosp:emergencyServices ?hasER. } LIMIT 1 ####################################### # # 8) Retrieve all clinical data for a given hospital # (Fair Oaks Hospital, in this example) # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?conditionId ?measureId ?metricId ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { hoco:recordset _:rs. { GRAPH ?g { _:rs comp:metric ?metricId. } ?g dcterms:issued ?date. OPTIONAL { _:rs gd:percentage ?percentage. } OPTIONAL { _:rs comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } UNION { GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. } OPTIONAL { _:record comp:measure ?measureId. } OPTIONAL { _:record comp:metric ?metricId. } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { _:record gd:denominator ?denominator. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:admissions ?admissions. } OPTIONAL { _:record hoco:rsmrLow ?rsmrLow. } OPTIONAL { _:record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { _:record hoco:rsrrLow ?rsrrLow. } OPTIONAL { _:record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { _:record hoco:stateCount ?stateCount. } OPTIONAL { _:record hoco:medianPayment ?medianPayment. } OPTIONAL { _:record hoco:msdrg ?msdrg. } } } ####################################### # # 8slow) Retrieve all clinical data for a given hospital # (Fair Oaks Hospital, in this example) # # Slow version that fetches descriptions for hospitalId, conditionId, measureId, metricId # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?condition ?measure ?metric ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?conditionId ?measureId ?metricId ?footnoteId WHERE { hoco:recordset _:rs. { GRAPH ?g { _:rs comp:metric ?metricId. ?metricId rdfs:comment ?metric. } ?g dcterms:issued ?date. OPTIONAL { _:rs gd:percentage ?percentage. } OPTIONAL { _:rs comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } UNION { GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. ?conditionId rdfs:comment ?condition. } OPTIONAL { _:record comp:measure ?measureId. ?measureId rdfs:comment ?measure. } OPTIONAL { _:record comp:metric ?metricId. ?metricId rdfs:comment ?metric. } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { _:record gd:denominator ?denominator. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:admissions ?admissions. } OPTIONAL { _:record hoco:rsmrLow ?rsmrLow. } OPTIONAL { _:record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { _:record hoco:rsrrLow ?rsrrLow. } OPTIONAL { _:record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { _:record hoco:stateCount ?stateCount. } OPTIONAL { _:record hoco:medianPayment ?medianPayment. } OPTIONAL { _:record hoco:msdrg ?msdrg. } } } ####################################### # # 9) Retrieve all clinical quality averages for a state # (The state of Virginia, in this example) # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?conditionId ?measureId ?metricId ?percentage ?ratio ?medianTime ?stateCount ?nationalCount ?msdrg ?footnote ?footnoteId WHERE { rdfs:label ?stateCode. hoco:recordset _:rs. GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. } OPTIONAL { _:record comp:measure ?measureId. } OPTIONAL { _:record comp:metric ?metricId. } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record hoco:stateCount ?stateCount. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:nationalCount ?nationalCount. } OPTIONAL { _:record hoco:msdrg ?msdrg. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 9slow) Retrieve all clinical quality averages for a state # (The state of Virginia, in this example) # # Slow version that fetches descriptions for conditionId, measureId, metricId # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?condition ?measure ?metric ?percentage ?ratio ?medianTime ?stateCount ?nationalCount ?msdrg ?footnote ?conditionId ?measureId ?metricId ?footnoteId WHERE { rdfs:label ?stateCode. hoco:recordset _:rs. GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. ?conditionId rdfs:comment ?condition } OPTIONAL { _:record comp:measure ?measureId. ?measureId rdfs:comment ?measure } OPTIONAL { _:record comp:metric ?metricId. ?metricId rdfs:comment ?metric } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record hoco:stateCount ?stateCount. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:nationalCount ?nationalCount. } OPTIONAL { _:record hoco:msdrg ?msdrg. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 10) Retrieve all clinical quality averages for the US # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?conditionId ?measureId ?metricId ?percentage ?ratio ?medianTime ?nationalCount ?msdrg ?footnote ?footnoteId WHERE { hoco:recordset _:rs. GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. } OPTIONAL { _:record comp:measure ?measureId. } OPTIONAL { _:record comp:metric ?metricId. } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:nationalCount ?nationalCount. } OPTIONAL { _:record hoco:msdrg ?msdrg. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 10slow) Retrieve all clinical quality averages for the US # # Slow version that fetches descriptions for conditionId, measureId, metricId # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?condition ?measure ?metric ?percentage ?ratio ?medianTime ?nationalCount ?msdrg ?footnote ?conditionId ?measureId ?metricId ?footnoteId WHERE { hoco:recordset _:rs. GRAPH ?g { _:rs hoco:record _:record. } ?g dcterms:issued ?date. OPTIONAL { _:record comp:condition ?conditionId. ?conditionId rdfs:comment ?condition. } OPTIONAL { _:record comp:measure ?measureId. ?measureId rdfs:comment ?measure. } OPTIONAL { _:record comp:metric ?metricId. ?metricId rdfs:comment ?metric. } OPTIONAL { _:record gd:percentage ?percentage. } OPTIONAL { _:record hoco:ratio ?ratio. } OPTIONAL { _:record hoco:medianTime ?medianTime. } OPTIONAL { _:record hoco:nationalCount ?nationalCount. } OPTIONAL { _:record hoco:msdrg ?msdrg. } OPTIONAL { _:record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 11) Retrieve the values for a Condition, Measure, and Metric at a given hospital # # In this example: Where the patient was admitted with outpatient AMI/Chest Pain, # what is the median transfer time to another facility for Acute Coronary Intervention? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { { GRAPH ?g { hoco:recordset ?rs. } ?rs hoco:record ?record. } UNION { GRAPH ?g { hoco:recordset ?record. } } ?g dcterms:issued ?date. ?record comp:condition . ?record comp:measure . # ?record comp:metric <>. # No metric filter in this example OPTIONAL { ?record gd:percentage ?percentage.} OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } ####################################### # # 12) Retrieve the values for a condition/measure/metric for a state # # In this example: According to survey, what percentage of patients # felt they always had communication with doctors in the state of Virginia? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?stateCode # ?condition # No condition filter in this example ?measure ?metric ?percentage ?ratio ?medianTime ?stateCount ?nationalCount ?msdrg ?footnote ?footnoteId WHERE { rdfs:label ?stateCode. GRAPH ?g { hoco:recordset ?rs. } ?g dcterms:issued ?date. ?rs hoco:record ?record. # ?record comp:condition <>. # No condition filter in this example # <> rdfs:comment ?condition. # No condition filter in this example ?record comp:measure . rdfs:comment ?measure. ?record comp:metric . rdfs:comment ?metric. OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 13) Retrieve the values for a condition/measure/metric for the US # # In this example: According to survey, what is the US average percentage of patients # who checked "Usually" when asked about the responsiveness of the hospital staff. # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date # ?condition # No condition filter in this example ?measure ?metric ?percentage ?ratio ?medianTime ?nationalCount ?msdrg WHERE { hoco:recordset ?rs. GRAPH ?g { ?rs hoco:record ?record. } ?g dcterms:issued ?date. # ?record comp:condition <>. # No condition filter in this example # <> rdfs:comment ?condition. # No condition filter in this example ?record comp:measure . rdfs:comment ?measure. ?record comp:metric . rdfs:comment ?metric. OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } ####################################### # # 14) Retrieve all data publication dates # # All queries greater than 14 require a publication date. # Presumably, a developer might use this query to drive a script in a loop # ####################################### # PREFIX dcterms: PREFIX void: SELECT DISTINCT ?date WHERE { ?s void:dataset ?ds . ?ds dcterms:issued ?date. } ####################################### # # 15) Retrieve the hospital with the Highest value # for some condition/measure/metric for some date published. # # In this example: As reported for June 7, 2011, what hospital performed the greatest # percentage of MRIs performed for outpatients suffering from lower back pain? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: PREFIX void: SELECT DISTINCT ?hospitalId ?hospital ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital with the highest value # { SELECT DISTINCT ?bag ?hospitalId ?percentage WHERE { ?bag comp:condition . ?bag comp:measure . # ?bag comp:metric . # This query doesn't use a metric GRAPH ?g { ?bag gd:percentage ?percentage. } ?g dcterms:issued "2011-06-07"^^xsd:date. { ?rs hoco:record ?bag. ?hospitalId hoco:recordset ?rs. } UNION { ?hospitalId hoco:recordset ?bag. } } ORDER BY DESC( ?percentage ) LIMIT 1 } # # Now retrieve all the information about that performance at that hospital # ?hospitalId rdfs:label ?hospital. OPTIONAL { ?bag comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?bag gd:denominator ?denominator. } OPTIONAL { ?bag hoco:ratio ?ratio. } OPTIONAL { ?bag hoco:medianTime ?medianTime. } OPTIONAL { ?bag hoco:admissions ?admissions. } OPTIONAL { ?bag hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?bag hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?bag hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?bag hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?bag hoco:stateCount ?stateCount. } OPTIONAL { ?bag hoco:medianPayment ?medianPayment. } OPTIONAL { ?bag hoco:msdrg ?msdrg. } } ####################################### # # 16) Retrieve the hospital with the lowest value # for some condition/measure/metric of ANY date published. # # In this example: What hospital had the overall least # percentage of MRIs performed for outpatients suffering from lower back pain? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: PREFIX void: SELECT DISTINCT ?date ?hospitalId ?hospital ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital with the lowest value for some CMM for any date # { SELECT DISTINCT ?date ?bag ?hospitalId ?percentage WHERE { ?bag comp:condition . ?bag comp:measure . # ?bag comp:metric . GRAPH ?g { ?bag gd:percentage ?percentage. } ?g dcterms:issued ?date. { ?rs hoco:record ?bag. ?hospitalId hoco:recordset ?rs. ?hospitalId a hosp:Hospital. } UNION { ?hospitalId hoco:recordset ?bag. ?hospitalId a hosp:Hospital. } } ORDER BY ASC( ?percentage ) LIMIT 1 } # # Now retrieve all related information about that hospital record # ?hospitalId rdfs:label ?hospital. OPTIONAL { ?bag comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?bag gd:denominator ?denominator. } OPTIONAL { ?bag hoco:ratio ?ratio. } OPTIONAL { ?bag hoco:medianTime ?medianTime. } OPTIONAL { ?bag hoco:admissions ?admissions. } OPTIONAL { ?bag hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?bag hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?bag hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?bag hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?bag hoco:stateCount ?stateCount. } OPTIONAL { ?bag hoco:medianPayment ?medianPayment. } OPTIONAL { ?bag hoco:msdrg ?msdrg. } } ####################################### # # 16.1) Retrieve the hospital with the lowest value with sufficient sample size # for some condition/measure/metric of ANY date published. # # In this example: What hospital had the overall least percentage of MRIs # performed, where there is no note indicating insuffient sample size, # for outpatients suffering from lower back pain? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: PREFIX void: SELECT DISTINCT ?date ?hospitalId ?hospital ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital with the lowest value for some CMM # where there is no note indicating an insufficient sample. # { SELECT DISTINCT ?date ?bag ?hospitalId ?percentage WHERE { ?bag comp:condition . ?bag comp:measure . # ?bag comp:metric . GRAPH ?g { ?bag gd:percentage ?percentage. } ?g dcterms:issued ?date. { ?rs hoco:record ?bag. ?hospitalId hoco:recordset ?rs. ?hospitalId a hosp:Hospital. OPTIONAL { ?bag comp:footnote ?footId. } FILTER ( ! bound( ?footId ) OR ?footId != ) } UNION { ?hospitalId hoco:recordset ?bag. ?hospitalId a hosp:Hospital. OPTIONAL { ?bag comp:footnote ?footId. } FILTER ( ! bound( ?footId ) OR ?footId != ) } } ORDER BY ASC( ?percentage ) LIMIT 1 } # # Now retrieve all information about that record at that hospital # ?hospitalId rdfs:label ?hospital. OPTIONAL { ?bag comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?bag gd:denominator ?denominator. } OPTIONAL { ?bag hoco:ratio ?ratio. } OPTIONAL { ?bag hoco:medianTime ?medianTime. } OPTIONAL { ?bag hoco:admissions ?admissions. } OPTIONAL { ?bag hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?bag hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?bag hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?bag hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?bag hoco:stateCount ?stateCount. } OPTIONAL { ?bag hoco:medianPayment ?medianPayment. } OPTIONAL { ?bag hoco:msdrg ?msdrg. } } ####################################### # # 17) Retrieve the state that performs the highest percentage for some # condition/measure/metric for a date published. # # In this example: What state had the overall highest percentage of MRIs performed # outpatients suffering from lower back pain, as published on June 7, 2011? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?stateCode ?stateId ?percentage ?ratio ?medianTime ?stateCount ?nationalCount ?msdrg ?footnote ?footnoteId WHERE { # # First find the state with the highest percentage for some CMM on some date # { SELECT DISTINCT ?stateId ?percentage ?record WHERE { ?stateId a gd:State. GRAPH ?g { ?stateId hoco:recordset ?rs. } ?g dcterms:issued "2011-06-07"^^xsd:date. ?rs hoco:record ?record. ?record comp:condition . ?record comp:measure . ?record gd:percentage ?percentage. # ?record comp:metric <>. } ORDER BY DESC( ?percentage ) LIMIT 1 } # # Now retrieve all other information about that state statistic # ?stateId rdfs:label ?stateCode. OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ####################################### # # 18) Retrieve the Hospital that has the highest value for a # condition/measure/metric within a state, for a publication date. # # In this example: In Virginia, for June 7, 2011, what hospital had the highest # percentage of MRIs performed for outpatients suffering from lower back pain? # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT ?hospitalId ?hospital ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital that had the highest percentage for some CMM on a date # { SELECT DISTINCT ?hospitalId ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { ?hospitalId a hosp:Hospital. ?hospitalId gd:stateCode . GRAPH ?g { ?hospitalId hoco:recordset ?rs. } ?g dcterms:issued "2011-06-07"^^xsd:date. { ?rs hoco:record ?record. ?record comp:condition . ?record comp:measure . # ?record comp:metric <>. # No metric filter in this example OPTIONAL { ?record gd:percentage ?percentage.} OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } UNION { ?rs comp:condition . ?rs comp:measure . # ?rs comp:metric <>. # No metric filter in this example OPTIONAL { ?rs gd:percentage ?percentage.} OPTIONAL { ?rs comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } } ORDER BY DESC( ?percentage ) LIMIT 1 } ?hospitalId rdfs:label ?hospital. } LIMIT 1 ####################################### # # 19) what is the national data on some condition/measure/metric, # and how does my state compare? # # In this example: Compare the Survey results of Virginia and US averages over time; # What percentage of patients responded "Usually" when asked about communication with doctors. # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX hosp: PREFIX rdfs: SELECT DISTINCT ?date ?place # ?condition # No condition filter in this example ?measure ?metric ?percentage ?ratio ?medianTime ?nationalCount ?msdrg WHERE { # # Compare US and VA averages # { hoco:recordset ?rs. } UNION { hoco:recordset ?rs. } GRAPH ?g { ?rs hoco:record ?record. } ?place hoco:recordset ?rs. ?g dcterms:issued ?date. # # Select only the CMM we care about # ?record comp:condition <>. # No condition filter in this example # <> rdfs:comment ?condition. # No condition filter in this example ?record comp:measure . rdfs:comment ?measure. ?record comp:metric . rdfs:comment ?metric. # # Now get the rest of the information about these statistics # OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } ORDER BY ?date ####################################### # # 20) what is the national data on this condition/measure/metric, # and how does my hospital compare? # # In this example: Compare the Survey results from Fair Oaks Hospital and US averages over time; # What percentage of patients responded "Usually" when asked about communication with doctors. # ####################################### # PREFIX gd: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX hosp: PREFIX rdfs: SELECT DISTINCT ?date ?place # ?condition # No condition filter in this example ?measure ?metric ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?nationalCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Compare the US average with a hospital # { hoco:recordset ?rs. GRAPH ?g { ?rs hoco:record ?record. } ?place hoco:recordset ?rs. } UNION { hoco:recordset ?rs. GRAPH ?g { ?rs hoco:record ?record. } ?place hoco:recordset ?rs. } UNION { GRAPH ?g { hoco:recordset ?record. } ?place hoco:recordset ?record. } ?g dcterms:issued ?date. # # Select only the CMM we care about # # ?record comp:condition <>. # No condition filter in this example # <> rdfs:comment ?condition. # No condition filter in this example ?record comp:measure . rdfs:comment ?measure. ?record comp:metric . rdfs:comment ?metric. # # Now retrieve the rest of the information about these statistics # OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } } ORDER BY ?date ####################################### # # 21) of the 3 hospitals in my area, what are their stats on this condition/measure/metric? # # Presumably, some other script will figure out what 3 hospitals are near the user. # # In this example: Compare the median time to transfer to another facility for accute # coronary intervention for the local hospitals: Fair Oaks, Fairfax, and Reston # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?hospital ?condition ?measure # ?metric ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?hospitalId ?footnoteId WHERE { # # Specify the hospitals # { GRAPH ?g { hoco:recordset ?rs. } ?hospitalId hoco:recordset ?rs. ?rs hoco:record ?record. } UNION { GRAPH ?g { hoco:recordset ?record. } ?hospitalId hoco:recordset ?record. } UNION { GRAPH ?g { hoco:recordset ?rs. } ?hospitalId hoco:recordset ?rs. ?rs hoco:record ?record. } UNION { GRAPH ?g { hoco:recordset ?record. } ?hospitalId hoco:recordset ?record. } UNION { GRAPH ?g { hoco:recordset ?rs. } ?hospitalId hoco:recordset ?rs. ?rs hoco:record ?record. } UNION { GRAPH ?g { hoco:recordset ?record. } ?hospitalId hoco:recordset ?record. } ?g dcterms:issued ?date. # # Select only the CMM we are interested in # ?record comp:condition . rdfs:comment ?condition. ?record comp:measure . rdfs:comment ?measure. # ?record comp:metric <>. # No metric filter in this example # <> rdfs:comment ?metric. # No metric filter in this example ?hospitalId rdfs:label ?hospital. # # Now retrieve the rest of the information about these records from these hospitals # OPTIONAL { ?record gd:percentage ?percentage.} OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } ORDER BY ?date ####################################### # # 22) Retrieve all metadata/contact information for the hospital in some state # that is highest for some condition/measure/metric on a given publication date. # # THIS QUERY TIMES OUT. # # Instead, a developer will have to execute query #18 to obtain the hospitalId. # And then execute query #7 to get the information about the hospital ####################################### # PREFIX ns: PREFIX vcard: PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT ?lat ?lon ?hospital ?hospitalType ?hasER ?phone ?streetAddr ?city ?stateCode ?countyCode ?zip ?ownershipType ?hospitalId ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { { SELECT ?hospitalId ?hospital ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital that had the highest percentage for some CMM on a date # { SELECT DISTINCT ?hospitalId ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { ?hospitalId a hosp:Hospital. ?hospitalId gd:stateCode . GRAPH ?g { ?hospitalId hoco:recordset ?rs. } ?g dcterms:issued "2011-06-07"^^xsd:date. { ?rs hoco:record ?record. ?record comp:condition . ?record comp:measure . # ?record comp:metric <>. # No metric filter in this example OPTIONAL { ?record gd:percentage ?percentage.} OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } UNION { ?rs comp:condition . ?rs comp:measure . # ?rs comp:metric <>. # No metric filter in this example OPTIONAL { ?rs gd:percentage ?percentage.} OPTIONAL { ?rs comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } } ORDER BY DESC( ?percentage ) LIMIT 1 } ?hospitalId rdfs:label ?hospital. } LIMIT 1 } { SELECT ?lat ?lon ?phone ?city ?zip ?streetAddr WHERE { ?hospitalId hosp:site _:site. _:site ns:siteAddress _:vcard. OPTIONAL { _:vcard vcard:latitude ?lat. } OPTIONAL { _:vcard vcard:longitude ?lon. } OPTIONAL { _:vcard vcard:tel ?phone. } OPTIONAL { _:vcard vcard:locality ?city. } OPTIONAL { _:vcard vcard:postal-code ?zip. } OPTIONAL { _:vcard vcard:street-address ?streetAddr. } } LIMIT 1 } ?hospitalId rdfs:label ?hospital. ?hospitalId gd:stateCode ?stateId. ?stateId rdfs:label ?stateCode. ?hospitalId gd:countyCode ?countyCode. ?hospitalId hosp:ownership ?ownershipType. ?hospitalId hosp:type ?hospitalType. ?hospitalId hosp:emergencyServices ?hasER. } LIMIT 1 ####################################### # # 23) Retrieve all clinical information available, # about the hospital that had the best performance on a # condition/measure/metric on a publication date. # # In this example: In Virginia, for June 7, 2011, what hospital had the highest # percentage of MRIs performed for outpatients suffering from lower back pain? # And retrieve all clinical information for that hospital on that publication date. # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: PREFIX void: SELECT DISTINCT ?date ?hospitalId ?conditionId ?measureId ?metricId ?percentage ?footnote ?denominator ?ratio ?medianTime ?admissions ?rsmrLow ?rsmrHigh ?rsrrLow ?rsrrHigh ?stateCount ?medianPayment ?msdrg ?footnoteId WHERE { # # Find the hospital that had the best performance on a # condition/measure/metric on a publication date. # { SELECT ?hospitalId WHERE { ?bag comp:condition . ?bag comp:measure . # ?bag comp:metric . GRAPH ?g { ?bag gd:percentage ?percentage. } ?g dcterms:issued "2011-06-07"^^xsd:date. { ?rs hoco:record ?bag. ?hospitalId hoco:recordset ?rs. ?hospitalId a hosp:Hospital. } UNION { ?hospitalId hoco:recordset ?bag. ?hospitalId a hosp:Hospital. } } ORDER BY DESC( ?percentage ) LIMIT 1 } # # Retrieve all clinical information about that hospital # ?hospitalId hoco:recordset ?rs. { GRAPH ?g { ?rs comp:metric ?metricId. } ?g dcterms:issued ?date. OPTIONAL { ?rs gd:percentage ?percentage. } OPTIONAL { ?rs comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } } UNION { GRAPH ?g { ?rs hoco:record ?record. } ?g dcterms:issued ?date. OPTIONAL { ?record comp:condition ?conditionId. } OPTIONAL { ?record comp:measure ?measureId. } OPTIONAL { ?record comp:metric ?metricId. } OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } OPTIONAL { ?record gd:denominator ?denominator. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:admissions ?admissions. } OPTIONAL { ?record hoco:rsmrLow ?rsmrLow. } OPTIONAL { ?record hoco:rsmrHigh ?rsmrHigh. } OPTIONAL { ?record hoco:rsrrLow ?rsrrLow. } OPTIONAL { ?record hoco:rsrrHigh ?rsrrHigh. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:medianPayment ?medianPayment. } OPTIONAL { ?record hoco:msdrg ?msdrg. } } } ####################################### # # 24) Retrieve all data for the state that is highest # for a condition/measure/metric for a date published # # In this example: Search data published on June 7, 2011 for the state # with the highest percentage of MRIs performed for outpatients suffering # from lower back pain? And return all clinical data averages for that state, # for June 7, 2011. # ####################################### # PREFIX gd: PREFIX hosp: PREFIX hoco: PREFIX dcterms: PREFIX comp: PREFIX rdfs: SELECT DISTINCT ?date ?stateId ?conditionId ?measureId ?metricId ?percentage ?ratio ?medianTime ?stateCount ?nationalCount ?msdrg ?footnote ?footnoteId WHERE { # # Retrieve the state that performs the BEST for some # condition/measure/metric for a date published. # { SELECT DISTINCT ?stateId WHERE { ?stateId a gd:State. GRAPH ?g { ?stateId hoco:recordset ?rs. } ?g dcterms:issued "2011-06-07"^^xsd:date. ?rs hoco:record ?record. ?record comp:condition . ?record comp:measure . ?record gd:percentage ?percentage. # ?record comp:metric <>. # this example does not use a metric filter } ORDER BY DESC( ?percentage ) LIMIT 1 } # # Retrieve all clinical quality averages for that state # ?stateId rdfs:label ?stateCode. ?stateId hoco:recordset ?rs. GRAPH ?g { ?rs hoco:record ?record. } ?g dcterms:issued ?date. OPTIONAL { ?record comp:condition ?conditionId. } OPTIONAL { ?record comp:measure ?measureId. } OPTIONAL { ?record comp:metric ?metricId. } OPTIONAL { ?record gd:percentage ?percentage. } OPTIONAL { ?record hoco:stateCount ?stateCount. } OPTIONAL { ?record hoco:ratio ?ratio. } OPTIONAL { ?record hoco:medianTime ?medianTime. } OPTIONAL { ?record hoco:nationalCount ?nationalCount. } OPTIONAL { ?record hoco:msdrg ?msdrg. } OPTIONAL { ?record comp:footnote ?footnoteId. ?footnoteId rdfs:comment ?footnote. } }