Warehouse add age specific enrolments

To calculate the ASER (nation wide and by district) I could not find anything in the warehouse. This is the indicator.

Screen Shot 2018-06-07 at 4.09.32 PM.png

I wrote the following queries albeit under time pressure so please double check correctness. Script at the end.

By district

Screen Shot 2018-06-07 at 4.07.00 PM.png

Nation-wide

Screen Shot 2018-06-07 at 4.07.36 PM.png

Need to build support to add this to warehouse like this

warehouse.AgeSpecificEnrolmentsNation warehouse.AgeSpecificEnrolmentsDistrict warehouse.AgeSpecificEnrolmentsSchool (not included above but desired)

--- ASER by district
SELECT [SurveyYear]
      ,[Age]
      --,[GenderCode]
      ,[DistrictCode]
      --,SUM(E1.[Enrol]) Enrollments
      ,SUM(CASE WHEN GenderCode = 'M' THEN E1.[Enrol] END) AS enrolM
      ,SUM(CASE WHEN GenderCode = 'F' THEN E1.[Enrol] END) AS enrolF
      ,SUM(E1.[Enrol]) AS enrol
      ,E2.popM
      ,E2.popF
      ,E2.popSum
  FROM [warehouse].[EnrolAndPop] AS E1
  INNER JOIN (SELECT [dID]
      ,[popYear]
      ,[popAge]
      ,[popM]
      ,[popF]
      ,[popSum]
  FROM [dbo].[Population]) AS E2 ON E1.SurveyYear = E2.popYear AND E1.Age = E2.popAge and E1.DistrictCode = E2.dID
  WHERE E1.Enrol IS NOT NULL AND Age > 4
  GROUP BY SurveyYear, Age, DistrictCode,E2.popM,E2.popF,E2.popSum
  ORDER BY SurveyYear, Age, DistrictCode

--- ASER nation
SELECT [SurveyYear]
      ,[Age]
      ,SUM(enrolM) AS enrolM
      ,SUM(enrolF) AS enrolF
      ,SUM(enrol) AS enrol
      ,SUM(popM) AS popM
      ,SUM(popF) AS popF
      ,SUM(popSum) AS popSum
FROM 
(SELECT [SurveyYear]
      ,[Age]
      ---,SUM(E1.[Enrol]) Enrollments
      ,SUM(CASE WHEN GenderCode = 'M' THEN E1.[Enrol] END) AS enrolM
      ,SUM(CASE WHEN GenderCode = 'F' THEN E1.[Enrol] END) AS enrolF
      ,SUM(E1.[Enrol]) AS enrol
      ,E2.popM AS popM
      ,E2.popF AS popF
      ,E2.popSum AS popSum
  FROM [warehouse].[EnrolAndPop] AS E1
  INNER JOIN (SELECT [dID]
      ,[popYear]
      ,[popAge]
      ,[popM]   
      ,[popF]
      ,[popSum]
  FROM [dbo].[Population]) AS E2 ON E1.SurveyYear = E2.popYear AND E1.Age = E2.popAge and E1.DistrictCode = E2.dID
  WHERE E1.Enrol IS NOT NULL AND Age > 4
  GROUP BY SurveyYear, Age, E2.popM,E2.popF,E2.popSum) AS T  
  GROUP BY SurveyYear, Age
  ORDER BY SurveyYear, Age

Comments (3)