﻿-- MFU_ICD9_HCPCS
-- METRIC_TYPE = Events, Members
-- SD_METRIC_TYPE = ev, mb
SELECT EnrollmentData.Period, EnrollmentData.Sex, EnrollmentData.AgeGroup, SummaryData.Setting, SummaryData.%CODE_FIELD%, SummaryData.%NAME_FIELD%,
SUM(IIF(ISNULL(SummaryData.mb), 0, SummaryData.mb)) AS Members, 
SUM(IIF(ISNULL(SummaryData.ev), 0, SummaryData.ev)) AS Events,
SUM(IIF(ISNULL(EnrollmentData.SumOfMembers), 0, EnrollmentData.SumOfMembers)) AS [Total Enrollment in Strata(Members)],
SUM(IIF(ISNULL(EnrollmentData.DaysCovered), 0, EnrollmentData.DaysCovered)) AS [Days Covered],
ROUND(IIF([Total Enrollment in Strata(Members)] = 0, 0, Members / [Total Enrollment in Strata(Members)] * 1000), 1) AS [User Rate (Users per 1000 enrollees)],
ROUND(IIF([Total Enrollment in Strata(Members)] = 0, 0, Events / [Total Enrollment in Strata(Members)] * 1000), 1) AS [Event Rate (Events per 1000 enrollees)],
ROUND(IIF(Members = 0, 0, Events / Members), 1) AS [Events per member]
FROM

	--
	-- Age Group and Enrollment Data Section
	--
	-- This part makes sure that all age groups for all desired enrollment years, genders and codes/names are represented in the result table
	-- even if there is no summary data.
	--

	(
		SELECT AgeGroups.AgeGroup, AgeGroups.AgeGroupSort, AgeGroups.Sex, AgeGroups.Period, 
		Sum(ed.Members) AS SumOfMembers, 
		Sum(ed.DaysCovered) AS DaysCovered 
		FROM

		(
			-- Cross join ensures all age groups for all desired enrollment years, genders and codes/names are represented in the result table.

			(
				SELECT distinct ag.id AS AgeGroupId, ag.%STRATIFICATION%_name AS AgeGroup, ag.%STRATIFICATION%_sort_order AS AgeGroupSort,  %SEX_AGGREGATED% AS Sex, en.Year AS Period
				FROM age_groups AS ag, 

				%CJC%

				--WHERE en.year in (%YEARS%) AND en.sex IN (%SEX%) AND en.medcov='Y'
			) AS AgeGroups

			LEFT JOIN

				-- Add the enrollment data to the rows (where medical coverage is Y).

				(SELECT * FROM enrollment WHERE medcov='Y') AS ed
				ON ed.age_group_id = AgeGroups.AgeGroupId %MATCH_SEX%  AND ed.Year = AgeGroups.Period
		)

		GROUP BY AgeGroups.AgeGroup, AgeGroups.AgeGroupSort, AgeGroups.Sex, AgeGroups.Period
	
	) AS EnrollmentData

LEFT JOIN

	--
	-- Summary Data Section
	--
	-- Add the top N rows (by events or members) for each age group stratum.
	--

	(

	%STRATIFICATION_CLAUSE%

	) AS SummaryData

ON (SummaryData.age_group = EnrollmentData.agegroup %MATCH_SEX2% and SummaryData.Period = EnrollmentData.Period)

GROUP BY EnrollmentData.AgeGroup, EnrollmentData.Sex, EnrollmentData.Period, SummaryData.%CODE_FIELD%, SummaryData.%NAME_FIELD%, SummaryData.Setting, EnrollmentData.AgeGroupSort
ORDER BY AgeGroups.Period, AgeGroups.Sex, AgeGroups.AgeGroupSort, SUM(IIF(ISNULL(SummaryData.%SD_METRIC_TYPE%), 0, SummaryData.%SD_METRIC_TYPE%)) DESC
