Skip to content

Instantly share code, notes, and snippets.

@akbertram
Created November 8, 2012 19:33
Show Gist options
  • Save akbertram/4041006 to your computer and use it in GitHub Desktop.
Save akbertram/4041006 to your computer and use it in GitHub Desktop.
PivotTable Query of Death
SELECT Indicator.aggregation c1,
Sum(V.value) c2,
Avg(V.value) c3,
AdminLevel1.adminentityid c4,
AdminLevel1.name c5,
partner.partnerid c6,
partner.name c7,
activity.databaseid c8,
userdatabase.name c9,
Indicator.indicatorid c10,
Indicator.name c11,
Indicator.sortorder c12,
activity.activityid c13,
activity.name c14,
activity.sortorder c15
FROM indicatorvalue V
LEFT JOIN reportingperiod Period
ON ( Period.reportingperiodid = V.reportingperiodid )
LEFT JOIN (SELECT indicatorid SourceId,
indicatorid,
name,
sortorder,
aggregation,
activityid
FROM indicator
WHERE datedeleted IS NULL
AND indicatorid IN ( 275, 274, 278, 277,
276, 1894, 1134, 305,
1135, 304, 1132, 1133,
1130, 309, 1131, 311,
1129, 310, 313, 312,
4041, 314, 317, 319,
318, 296, 1141, 1140,
1139, 1138, 1137, 302,
1136, 303, 342, 341,
338, 344, 326, 327,
322, 323, 334, 1040,
335, 332, 333, 330,
328, 329, 119, 118,
125, 124, 123, 122,
121, 953, 948, 129,
949, 1503, 950, 1500,
946, 947, 943, 942,
937, 938, 933, 932,
934, 929, 928, 931,
930, 926, 927, 652,
649, 1520, 1521, 2102,
651, 446, 1522, 650,
447, 2105, 2111, 1258,
2108, 1510, 2086, 1504,
1507, 2085, 1517, 1519,
1518, 1512, 1515, 2093,
747, 2133, 746, 745,
744, 2130, 738, 737,
2143, 736, 2142, 743,
742, 2136, 741, 465,
2117, 2114, 455, 2124,
452, 2127, 451, 450,
449, 448, 2148, 2149,
2146, 2147, 2144, 735,
2145, 2156, 960, 2152, 2153 )
UNION ALL
SELECT L.sourceindicatorid SourceId,
D.indicatorid,
D.name,
D.sortorder,
D.aggregation,
D.activityid
FROM indicator D
INNER JOIN indicatorlink L
ON ( D.indicatorid = L.destinationindicatorid )
INNER JOIN indicator S
ON ( S.indicatorid = L.sourceindicatorid )
WHERE D.datedeleted IS NULL
AND S.datedeleted IS NULL
AND D.indicatorid IN ( 275, 274, 278, 277,
276, 1894, 1134, 305,
1135, 304, 1132, 1133,
1130, 309, 1131, 311,
1129, 310, 313, 312,
4041, 314, 317, 319,
318, 296, 1141, 1140,
1139, 1138, 1137, 302,
1136, 303, 342, 341,
338, 344, 326, 327,
322, 323, 334, 1040,
335, 332, 333, 330,
328, 329, 119, 118,
125, 124, 123, 122,
121, 953, 948, 129,
949, 1503, 950, 1500,
946, 947, 943, 942,
937, 938, 933, 932,
934, 929, 928, 931,
930, 926, 927, 652,
649, 1520, 1521, 2102,
651, 446, 1522, 650,
447, 2105, 2111, 1258,
2108, 1510, 2086, 1504,
1507, 2085, 1517, 1519,
1518, 1512, 1515, 2093,
747, 2133, 746, 745,
744, 2130, 738, 737,
2143, 736, 2142, 743,
742, 2136, 741, 465,
2117, 2114, 455, 2124,
452, 2127, 451, 450,
449, 448, 2148, 2149,
2146, 2147, 2144, 735,
2145, 2156, 960, 2152, 2153 ))
AS
Indicator
ON ( Indicator.sourceid = V.indicatorid )
LEFT JOIN site Site
ON ( Period.siteid = site.siteid )
LEFT JOIN partner Partner
ON ( site.partnerid = partner.partnerid )
LEFT JOIN project Project
ON ( site.projectid = project.projectid )
LEFT JOIN location Location
ON ( location.locationid = site.locationid )
LEFT JOIN activity Activity
ON ( activity.activityid = Indicator.activityid )
LEFT JOIN userdatabase UserDatabase
ON ( activity.databaseid = userdatabase.databaseid )
LEFT JOIN (SELECT L.locationid,
E.adminentityid,
E.name
FROM locationadminlink L
LEFT JOIN adminentity E
ON ( L.adminentityid = E.adminentityid )
WHERE E.adminlevelid = 1) AS AdminLevel1
ON ( location.locationid = AdminLevel1.locationid )
WHERE ( ( V.value <> 0
AND Indicator.aggregation = 0 )
OR Indicator.aggregation = 1 )
AND site.datedeleted IS NULL
AND activity.datedeleted IS NULL
AND userdatabase.datedeleted IS NULL
AND ( userdatabase.owneruserid = 167
OR 167 IN (SELECT p.userid
FROM userpermission p
WHERE p.allowview
AND p.userid = 167
AND p.databaseid = userdatabase.databaseid) )
AND Period.date2 >= '2012-02-01'
AND Period.date2 <= '2012-10-05'
GROUP BY Indicator.indicatorid,
Indicator.aggregation,
AdminLevel1.adminentityid,
AdminLevel1.name,
partner.partnerid,
partner.name,
activity.databaseid,
userdatabase.name,
Indicator.indicatorid,
Indicator.name,
Indicator.sortorder,
activity.activityid,
activity.name,
activity.sortorder
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment