Editing Mental health expansion (section)

=== Aiga data ===

====Base data for report month====
<syntaxhighlight lang="sql" line>
-- get base data into temp table
select
ContractID,
ContractName,
ClientID,
EpisodeContractID,
NoteDate,
ContactTypeID,
ContactTypeActive,
case
	-- find contact types that are 'direct contacts'
	when ContactTypeID in (
	  2,
      3,
      4,
      13,
      15,
      19,
      20,
      24,
      27,
      34,
      35,
      37,
      38,
      39,
      41,
      63,
      68,
      69,
      72,
      75,
      76,
      77,
      83,
      85,
      86,
      88,
      89,
      90,
      93,
      94,
      97,
      98) then 1
      else 0
      end as 'Direct contact'

into #mhbase
from vClientNotesDocuments
where 
ContractID = 11 -- MHE contract ID
and datepart(mm,NoteDate) = 1 --> ## SET REPORT MONTH HERE
and datepart(yy,NoteDate) = 2025 --> ## SET REPORT YEAR HERE
and UserID <> 1111 -- exclude this user
;
</syntaxhighlight>

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

====People seen====

<syntaxhighlight lang="sql" line>
select
ContractName,
count(distinct clientid)

from #mhbase

where [direct contact] = 1
group by ContractName;
</syntaxhighlight>

====Service exits====

<syntaxhighlight lang="sql" line>
select
ContractName,
count(*)
from vEpisodeContracts
where 
ContractID in (13,11)
and
	(datepart(yy,episodecontractenddate) = 2025 and 
	datepart(mm,episodecontractenddate) = 1)
and UserID <> 1111

group by ContractName
order by 1;
</syntaxhighlight>

====Individual sessions====

<syntaxhighlight lang="sql" line>
select 
ContractName,
sum(case when [direct contact] = 1 and contacttypeid not in (19) then 1 else 0 end) IndividualSessions,
sum(case when contacttypeid = 19 and ContractName = 'Child and Adolescent Mental Health ' then 1 else 0 end) GroupSessions -- exclude calculating this for CDP, see next block

from #mhbase

group by ContractName;
</syntaxhighlight>

====Group sessions (CDP only)====

<syntaxhighlight lang="sql" line>

with ctea as
-- group sessions/attendees for CDP
(
select
distinct cast(NoteDate as date) sessiondate,
count(*) attendees

from #mhbase

where ContractID = 13
and ContactTypeID = 19

group by NoteDate
)

select
case
	when attendees between 2 and 14 then '02-14'
	when attendees > 14 then '15+'
end as sessions,
count(*)

from ctea

group by
case
	when attendees between 2 and 14 then '02-14'
	when attendees > 14 then '15+'
end
;
</syntaxhighlight>

====New people by ethnicity====

<syntaxhighlight lang="sql" line>
select 
ContractName,
EthnicityGroup,
count(*)

from #mhnewpts
group by 
ContractName,
EthnicityGroup

order by 1,2;
</syntaxhighlight>

====New people by age group====

<syntaxhighlight lang="sql" line>
select 
ContractName,
AgeGroup,
count(*)

from #mhnewpts
group by 
ContractName,
AgeGroup

order by 1,2;
</syntaxhighlight>

====People waiting >5 days for first contact====

<syntaxhighlight lang="sql" line>
select
  ContractID,
  ContractName,
  ClientID,
  EpisodeContractID,
  NoteDate,
  ContactTypeID,
  ContactTypeActive,
  case
    when ContactTypeID in (
      2, 3, 4, 13, 15, 19, 20, 24, 27, 34, 35, 37, 38, 39,
      41, 63, 68, 69, 72, 75, 76, 77, 83, 85, 86, 88, 89,
      90, 93, 94, 97, 98
    ) then 1
    else 0
  end as [Direct contact]
into #mhbase
from vClientNotesDocuments
where 
  ContractID in (13,11)
  and datepart(mm, NoteDate) = 6
  and datepart(yy, NoteDate) = 2025
  and UserID <> 1111;

select 
  eco.EpisodeContractID,
  ec.ContractName,
  ec.EpisodeContractStartDate
into #mhnewec
from vEpisodeContracts ec
join vClientEpisodeContractOrder eco
  on ec.EpisodeContractID = eco.EpisodeContractID
where ec.ContractID in (13,11)
  and datepart(mm, ec.EpisodeContractStartDate) = 6
  and datepart(yy, ec.EpisodeContractStartDate) = 2025
  and ec.EpisodeContractID in (select distinct EpisodeContractID from #mhbase);


-- Get earliest direct contact for each EpisodeContract
with ctea as (
    select
        cnd.EpisodeContractID,
        min(cnd.NoteDate) as firstnote
    from vClientNotesDocuments cnd
    where 
        cnd.EpisodeContractID in (select EpisodeContractID from #mhnewec)
        and ContactTypeID in (
            2, 3, 4, 13, 15, 19, 20, 24, 27, 34,
            35, 37, 38, 39, 41, 63, 68, 69, 72,
            75, 76, 77, 83, 85, 86, 88, 89, 90,
            93, 94, 97, 98
        )
    group by cnd.EpisodeContractID
)

-- Count people who waited >5 days for first contact, only if there's enough time in the month
select
    ec.ContractName,
    count(*) as ClientsWaitingOver5Days
from #mhnewec ec
left join ctea c on ec.EpisodeContractID = c.EpisodeContractID
where 
    datediff(day, ec.EpisodeContractStartDate, c.firstnote) > 5
    and datediff(day, ec.EpisodeContractStartDate, eomonth(ec.EpisodeContractStartDate)) > 5
group by ec.ContractName
;
</syntaxhighlight>