Editing Mental health expansion (section)

====New starts====

<syntaxhighlight lang="sql" line>
-- new starts block
declare @MonthEndDate as date
declare @PriorYear as date

set @MonthEndDate = '2025-01-31' --> ## SET LAST DAY OF REPORT MONTH HERE
set @PriorYear = dateadd(mm,-11,@MonthEndDate)

print @MonthEndDate
print @PriorYear;

with ctea as
(
	-- find new starts in report month
	select 
	eco.ClientID,
	eco.EpisodeContractStartOrder,
	ec.*
	from vEpisodeContracts ec
	join vClientEpisodeContractOrder eco
	on ec.EpisodeContractID = eco.EpisodeContractID

	where
		ec.ContractID in (13,11) -- MHE and CDP contract IDs
		and 
		datepart(mm,ec.EpisodeContractStartDate) = datepart(mm,@MonthEndDate)
		and
		datepart(yy,ec.EpisodeContractStartDate) = datepart(yy,@MonthEndDate)
		and
		ec.EpisodeContractID in (select distinct EpisodeContractID from #mhbase)
)
,
cteb as
(
	-- check clientids from above have not had previous contract episode in last year
	select eco.*
	from vClientEpisodeContractOrder eco
	join ctea c
	on eco.EpisodeID = c.EpisodeID
	and
	eco.ContractID = c.ContractID
	where 
		eco.ContractID in (13,11)
		and
		eco.EpisodeContractStartOrder < c.EpisodeContractStartOrder
		and
		eco.EpisodeContractStartDate > @PriorYear
)

-- write summary data to temp table
select 
a.clientid,
a.contractname,
case
	when c.AgeInt between 0 and 11 then '0-11'
	when c.AgeInt between 12 and 17 then '12-17'
	when c.AgeInt between 18 and 24 then '18-24'
	when c.AgeInt between 25 and 64 then '25-64'
	when c.AgeInt > 64 then '65+'
	end as AgeGroup,
case
	when c.EthnicityID in (2) then 'Maori'
	when c.EthnicityID in (1) then 'NZ European'
	when c.EthnicityID not in (1,2,99,98) then 'Pasifika'
	when c.EthnicityID in (98,99) then 'Other'
	end as EthnicityGroup

into #mhnewpts
from ctea a
left join cteb b
on a.ClientID = b.ClientID
join vClients c
on a.ClientID = c.ClientID
where b.ClientID is null;
</syntaxhighlight>