Z_PATID.FullEnroll1115.AGE_BAND_CD789.NONCOM.Dep.MEMBER_SDDV2_1115 from HCCI 180717.sql

--#@@ [Research] HCCI MDD annual prevalence 180501.pptm -p128.sql ----
select count(z_patid), count(distinct z_patid)
from hcci.MEMBER_SDDV2_1115
where (
	z_patid in (
		select z_patid
		from hcci.MEMBER_SDDV2_1115
		where (
			gdr in ('1', '2')
			and age_band_cd not in ('99')
		)
		group by z_patid
		having count(distinct concat(yr, mnth)) = 12*5
		and count(distinct gdr) = 1
		and count(distinct age_band_cd) in (1, 2)
	)
) 




--#@@ select z_patid inclusion criteria (included if where condition is met at least once) and data cleaned for full enrollment criteria & impossible count(distinct varname) ----

select z_patid
from hcci.MEMBER_SDDV2_1115
where (
	z_patid in (
		select distinct z_patid
		from hcci.MEMBER_SDDV2_1115
		where (
			gdr in ('1', '2')
			and age_band_cd in ('07', '08', '09')
			and noncom_flag = '1'
			and yr in ('2011', '2012', '2013', '2014', '2015')
		)
	) 
	and gdr in ('1', '2')  -- ignore unknown in count(distinct varname)
	and age_band_cd in ('01', '02', '03', '04', '05', '06', '07', '08', '09')  -- ignore unknown in count(distinct varname)
)
group by z_patid
having count(distinct concat(yr, mnth)) = 12*5  -- count(distinct concat(yr, mnth)) used for full enrollment criteria
and count(distinct gdr) = 1  -- count(distinct varname) used for ambiguous gender
and count(distinct age_band_cd) in (1, 2)  -- count(distinct varname) used for impossible n_distinct of age_band_cd


--#@@ select the records for z_patid that has any diagnosis INTERSECT z_patid inclusion criteria (included if where condition is met at least once) and data cleaned for full enrollment criteria & impossible count(distinct varname) ----

select *
from iterate_for_every_tables
where (
	z_patid in  (
		(
			select z_patid
			from hcci.MEMBER_SDDV2_1115
			where (
				z_patid in (
					select distinct z_patid
					from hcci.MEMBER_SDDV2_1115
					where (
						gdr in ('1', '2')
						and age_band_cd in ('07', '08', '09')
						and noncom_flag = '1'
						and yr in ('2011', '2012', '2013', '2014', '2015')
					)
				)
				and gdr in ('1', '2')  -- ignore unknown in count(distinct varname)
				and age_band_cd in ('01', '02', '03', '04', '05', '06', '07', '08', '09')  -- ignore unknown in count(distinct varname)
			)
			group by z_patid
			having count(distinct concat(yr, mnth)) = 12*5  -- count(distinct concat(yr, mnth)) used for full enrollment criteria
			and count(distinct gdr) = 1  -- count(distinct varname) used for ambiguous gender
			and count(distinct age_band_cd) in (1, 2)  -- count(distinct varname) used for impossible n_distinct of age_band_cd
		)
		INTERSECT (
			select distinct z_patid
			from 
			(
				(
					select distinct z_patid -- z_clmid, clmseq, z_patid, diag1, diag2, diag3
					from hcci.IP_SDDV2_1115
					where yr in ('2011')
					-- and noncom_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					-- and over65_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					and (
						diag1 in ICDcode_list
						or diag2 in ICDcode_list
						or diag3 in ICDcode_list
					)
				) union all (
					select distinct z_patid -- z_clmid, clmseq, z_patid, diag1, diag2, diag3
					from hcci.OP_SDDV2_1115
					where yr in ('2011')
					-- and noncom_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					-- and over65_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					and (
						diag1 in ICDcode_list
						or diag2 in ICDcode_list
						or diag3 in ICDcode_list
					)
				) union all (
					select distinct z_patid -- z_clmid, clmseq, z_patid, diag1, diag2, diag3
					from hcci.PH_SDDV2_1115
					where yr in ('2011')
					-- and noncom_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					-- and over65_flag = '1' -- unneccessary because this will be intersected with z_patid inclusion criteria
					and (
						diag1 in ICDcode_list
						or diag2 in ICDcode_list
						or diag3 in ICDcode_list
					)
				)
			) as from_select
		)
	)
)