hummus-btn.sql

SELECT project.code                    AS project_id, 
       subproject.code                 AS subproject_id, 
       project.recordtype, 
       project.atype                   AS type, 
       lwrregion.NAME                  AS region, 
       lwrregion2.NAME                 AS region2, 
       Getprojectcountries(project.id) AS countries, 
       country2.NAME                   AS country, 
       COALESCE(agriculture, 0)        AS agriculture, 
       agriculture_count, 
       eoperations_count, 
       climatechange_count, 
       project.NAME                    AS project, 
       subproject.NAME                 AS subproject, 
       project.owner, 
       organization.NAME               AS implementer, 
       subproject.start, 
       subproject.end, 
       project.status, 
       ben.actualmen, 
       ben.actualwomen, 
       ben.actualimen, 
       ben.actualiwomen, 
       fiscalyear, 
       quarter 
FROM   subproject 
       LEFT OUTER JOIN organization 
                    ON subproject.organization_id = organization.id 
       LEFT OUTER JOIN project 
                    ON subproject.project_id = project.id 
       LEFT OUTER JOIN lwrregion 
                    ON project.lwrregion_id = lwrregion.id 
       LEFT OUTER JOIN lwrregion AS lwrregion2 
                    ON subproject.lwrregion_id = lwrregion2.id 
       LEFT OUTER JOIN project_countries 
                    ON project.id = project_countries.project_id 
       LEFT OUTER JOIN country 
                    ON project_countries.country_id = country.id 
       LEFT OUTER JOIN country AS country2 
                    ON subproject.country_id = country2.id 
       LEFT OUTER JOIN latest_beneficiaries AS ben 
                    ON ben.subproject_id = subproject.id 
WHERE  subproject.end > '2018-9-30' 
       AND subproject.start < '2019-10-1' 
       AND project.recordtype <> 'Non-Project' 
       AND project.status <> 'Terminated' 
       AND subproject.recordtype <> 'Admin'