﻿-- MFU_Pharma
-- METRIC_TYPE = DaysSupply, Dispensing
-- SD_METRIC_TYPE = ds, dp
SELECT EnrollmentData.Period, EnrollmentData.Sex, EnrollmentData.AgeGroup, SummaryData.%NAME_FIELD%, 
SUM(IIF(ISNULL(SummaryData.dp), 0, SummaryData.dp)) AS Dispensings, 
SUM(IIF(ISNULL(SummaryData.ds), 0, SummaryData.ds)) AS DaysSupply, 
SUM(IIF(ISNULL(SummaryData.mb), 0, SummaryData.mb)) AS Members, 
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 [Prevalence Rate (Users per 1000 enrollees)],
ROUND(IIF([Total Enrollment in Strata(Members)] = 0, 0, Dispensings / [Total Enrollment in Strata(Members)] * 1000), 1) AS [Dispensing Rate (Dispensings per 1000 enrollees)],
ROUND(IIF(Dispensings = 0, 0, DaysSupply / Dispensings), 1) AS [Days Per Dispensing],
ROUND(IIF(Members = 0, 0, DaysSupply / Members), 1) AS [Days per user]
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%
				--enrollment AS en
				--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 drug coverage is Y).

			(SELECT * FROM enrollment WHERE drugcov='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.%NAME_FIELD%, EnrollmentData.AgeGroupSort
ORDER BY AgeGroups.Period, AgeGroups.Sex, AgeGroups.AgeGroupSort, SUM(IIF(ISNULL(SummaryData.%SD_METRIC_TYPE%), 0, SummaryData.%SD_METRIC_TYPE%)) DESC
