NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
AND customdefinedfield.name = 'CDF-Clearance Type'
)
hazreduc_imsmaenum.Type,
hazreduc_imsmaenum.Status,
hazreduc_cdfs.method_used,
my_view_organization.ShortName AS ShortName
</pre>
=== View annualreport_view_bac in Afghanistan ===__NOEDITSECTION__
Version with a very poor performance:
<pre>
SELECT progress.hazreduc_guid AS "ProgressGUID",
cast(progress.startdate as date) AS "StartofReportingPeriod",
cast(progress.enddate as date) AS "EndofReportingPeriod",
progress.areasize AS "AreaCleared",
sum(case when annualreport_view_devicesdestroyed.Model = 'SAA' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "SAA",
sum(case when annualreport_view_devicesdestroyed.Model = 'AP' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AP",
sum(case when annualreport_view_devicesdestroyed.Model = 'UXO' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "UXO",
sum(case when annualreport_view_devicesdestroyed.Model = 'AT' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AT",
sum(case when annualreport_view_devicesdestroyed.Model = 'Fragments' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "Fragments",
sum(case when annualreport_view_devicesdestroyed.Model = 'AIED' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AIED",
annualreport_view_clearancetype."Clearance Type" AS "ClearanceType",
my_view_organization.ShortName AS "Agency",
my_view_gazetteer.Region AS "Region",
my_view_gazetteer.Province AS "Province",
my_view_gazetteer.District AS "District",
my_view_gazetteer.Village AS "Village"
FROM hazreduc progress
JOIN imsmaenum on (progress.hazreductypeenum_guid = imsmaenum.imsmaenum_guid)
LEFT JOIN annualreport_view_devicesdestroyed on (annualreport_view_devicesdestroyed.ProgressGUID = progress.hazreduc_guid)
JOIN annualreport_view_clearancetype on (annualreport_view_clearancetype.Hazreduc_guid = progress.hazreduc_guid)
JOIN my_view_organization on (progress.org_guid = my_view_organization.org_guid)
JOIN my_view_gazetteer on (my_view_gazetteer.location_guid = progress.location_guid)
WHERE (imsmaenum.enumvalue = 'Progress Report')
GROUP BY progress.hazreduc_guid,
cast(progress.startdate as date),
cast(progress.enddate as date),
progress.areasize,
annualreport_view_clearancetype."Clearance Type",
my_view_organization.ShortName,
my_view_gazetteer.Region,
my_view_gazetteer.Province,
my_view_gazetteer.District,
my_view_gazetteer.Village;
</pre>
Optimised version:
<pre>
WITH progress as (
SELECT progress.hazreduc_guid AS "ProgressGUID",
progress.org_guid,
progress.location_guid,
cast(progress.startdate as date) AS "StartofReportingPeriod",
cast(progress.enddate as date) AS "EndofReportingPeriod",
progress.areasize AS "AreaCleared",
sum(case when annualreport_view_devicesdestroyed.Model = 'SAA' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "SAA",
sum(case when annualreport_view_devicesdestroyed.Model = 'AP' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AP",
sum(case when annualreport_view_devicesdestroyed.Model = 'UXO' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "UXO",
sum(case when annualreport_view_devicesdestroyed.Model = 'AT' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AT",
sum(case when annualreport_view_devicesdestroyed.Model = 'Fragments' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "Fragments",
sum(case when annualreport_view_devicesdestroyed.Model = 'AIED' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AIED",
annualreport_view_clearancetype."Clearance Type" AS "ClearanceType"
FROM hazreduc progress
LEFT JOIN annualreport_view_devicesdestroyed on (progress.hazreduc_guid = annualreport_view_devicesdestroyed.ProgressGUID)
JOIN annualreport_view_clearancetype on (progress.hazreduc_guid = annualreport_view_clearancetype.Hazreduc_guid)
JOIN imsmaenum on (imsmaenum.imsmaenum_guid = progress.hazreductypeenum_guid)
WHERE (imsmaenum.enumvalue = 'Progress Report')
GROUP BY progress.hazreduc_guid,
progress.org_guid,
progress.location_guid,
cast(progress.startdate as date),
cast(progress.enddate as date),
progress.areasize,
annualreport_view_clearancetype."Clearance Type")
SELECT
"ProgressGUID",
"StartofReportingPeriod",
"EndofReportingPeriod",
"AreaCleared",
"SAA",
"AP",
"UXO",
"AT",
"Fragments",
"AIED",
"ClearanceType",
my_view_organization.ShortName AS "Agency",
my_view_gazetteer.Region AS "Region",
my_view_gazetteer.Province AS "Province",
my_view_gazetteer.District AS "District",
my_view_gazetteer.Village AS "Village"
FROM progress
JOIN my_view_organization on (progress.org_guid = my_view_organization.org_guid)
JOIN my_view_gazetteer on (progress.location_guid = my_view_gazetteer.location_guid);
</pre>
{{NavBox Hub}}
[[Category:NoPublic]]
[[Category:VIE]]
