dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 1996
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2010-11-03 21:27:57 +0100
message:
  Added handy integrity check sql queries
added:
  resources/sql/integritychecks.sql


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

=== added file 'resources/sql/integritychecks.sql'
--- resources/sql/integritychecks.sql	1970-01-01 00:00:00 +0000
+++ resources/sql/integritychecks.sql	2010-11-03 20:27:57 +0000
@@ -0,0 +1,38 @@
+
+-- Get name of datasets for a dataelement
+
+select ds.name from dataset ds
+join datasetmembers dm on (ds.datasetid=dm.datasetid) 
+join dataelement de on (dm.dataelementid=de.dataelementid)
+where de.name = 'Adverse Events Following Immunization';
+
+-- Get dataelement name and category combo for a section
+
+select de.name as dataelementname, cc.name as categorycomboname from dataelement de
+join categorycombo cc on(de.categorycomboid=cc.categorycomboid)
+join sectiondataelements sd on(de.dataelementid=sd.dataelementid)
+join section sc on(sd.sectionid=sc.sectionid)
+where sc.name = 'OPD Diagnoses';
+
+-- Get dataset memberships for data elements with more than one membership
+
+select de.name, ds.name from dataelement de
+join datasetmembers dm on(de.dataelementid=dm.dataelementid)
+join dataset ds on(dm.datasetid=ds.datasetid)
+where de.dataelementid in (
+  select de.dataelementid from dataelement de
+  full join datasetmembers ds on (de.dataelementid=ds.dataelementid)
+  group by de.dataelementid
+  having(count(de.dataelementid) > 1) );
+
+-- Get dataelements which are members of a section but not the section's dataset
+
+select de.name as dataelementname, sc.name as sectionname from sectiondataelements sd
+join dataelement de on(sd.dataelementid=de.dataelementid)
+join section sc on (sd.sectionid=sc.sectionid)
+where sd.dataelementid not in (
+  select dm.dataelementid from datasetmembers dm
+  join dataset ds on(dm.datasetid=ds.datasetid)
+  where sc.datasetid=ds.datasetid);
+
+