﻿-- Prev_ICD9_HCPCS
-- SD_CODE_FIELD = code, px_code
-- CODE_FIELD = DXCode, PXCode
-- NAME_FIELD = DXName, PXName
-- SD_TABLE = ICD9_Diagnosis, ICD9_Diagnosis_4_Digit, ICD9_Diagnosis_5_Digit, ICD9_Procedure_4_Digit, ICD9_Procedure, HCPCS

SELECT EnrollmentData.Period, EnrollmentData.Sex, EnrollmentData.AgeGroup, EnrollmentData.Setting, EnrollmentData.%CODE_FIELD%, EnrollmentData.%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 [Prevalence 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.AgeGroupId, AgeGroups.AgeGroup, AgeGroups.AgeGroupSort, AgeGroups.Sex, AgeGroups.Period, 
		AgeGroups.%CODE_FIELD% AS %CODE_FIELD%, AgeGroups.%NAME_FIELD%, AgeGroups.Setting, 
		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, 
				sd.Code AS %CODE_FIELD%, sd.Name AS %NAME_FIELD%, %SETTING% AS Setting
				FROM age_groups AS ag,

				%CJC%

			) 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
		)

		--LEFT JOIN

			-- Add the names to the rows by matching against summary data.

			--(SELECT distinct %SD_CODE_FIELD%, %NAME_FIELD% FROM %SD_TABLE% WHERE %SD_CODE_FIELD% in (%CODES%)) AS nm
			--ON nm.%SD_CODE_FIELD%=AgeGroups.%CODE_FIELD%

		GROUP BY AgeGroups.AgeGroupId, AgeGroups.AgeGroup, AgeGroups.AgeGroupSort, AgeGroups.Sex, AgeGroups.Period, AgeGroups.%CODE_FIELD%, AgeGroups.%NAME_FIELD%, AgeGroups.Setting
		
	) AS EnrollmentData

LEFT JOIN

	--
	-- Summary Data Section
	--
	-- Now add the corresponding summary data to the table (for those with medical coverage enrollment).
	--

	(
		SELECT %SD_CODE_FIELD%, %NAME_FIELD%, Setting, age_group_id, age_group, %MATCH_SEX3% period, 
		SUM(Members) AS mb, SUM(Events) AS ev
		FROM %SD_TABLE% AS sd
		WHERE %SD_CODE_FIELD% IN (%CODES%)  AND period in (%PERIODS%) AND  SETTING IN (%SETTING%) AND 
		      ((SELECT COUNT(age_group_id) FROM enrollment WHERE age_group_id=sd.age_group_id and sex=sd.sex and year=sd.period and medcov = 'Y') > 0)
		GROUP BY %SD_CODE_FIELD%, %NAME_FIELD%, Setting, age_group_id, age_group, %MATCH_SEX3% period
	) AS SummaryData

ON (SummaryData.age_group_id = EnrollmentData.agegroupid %MATCH_SEX2% and SummaryData.Period = EnrollmentData.Period and SummaryData.%SD_CODE_FIELD% = EnrollmentData.%CODE_FIELD%)

GROUP BY EnrollmentData.AgeGroup, EnrollmentData.Sex, EnrollmentData.Period, EnrollmentData.%CODE_FIELD%, EnrollmentData.%NAME_FIELD%, EnrollmentData.Setting, EnrollmentData.AgeGroupSort
ORDER BY EnrollmentData.Period, EnrollmentData.Sex, EnrollmentData.AgeGroupSort