Mental health expansion

Background

Mental health expansion is a contract/service provided by KPT.

Because we have a multidisciplinary Mental Health team, working across a range of contracts, we tend to separate this service out in reporting by staff member and demographic of client group.

At the current time, all contact data for this service is being recorded exclusively in Aiga and therefore reporting data must be sourced from there too.

Reporting

Funder reporting is required monthly, on the 20th of each month. Not all items are possible to obtain from the database, but a script is being used for those that are available. This is specified below.

Data points

Per the reporting template, the following is a summary of items required alongside a summary of whether or not we can obtain them from Aiga data.

Item	Available
FTE
Number of sessions
Number of people seen
Number of new people seen
Number of people waiting more than five days
Number of service exits
Number of rejected referrals
Number of onward referrals
Number of sessions involve whanau
Number of new people by ethnicity
Number of new people by age
Flexifund usage

Getting the data

At the present time data must be run directly from the Aiga database. This would probably benefit from automating into Power BI at some point, but the impending Integrated contract means that the cost may not outweigh benefit in the short term.

Current draft SQL is provided below.

Base data for report month

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

New starts

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

People seen

select
ContractName,
count(distinct clientid)

from #mhbase

where [direct contact] = 1
group by ContractName;

Service exits

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;

Individual sessions

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;

Group sessions (CDP only)

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
;

New people by ethnicity

select 
ContractName,
EthnicityGroup,
count(*)

from #mhnewpts
group by 
ContractName,
EthnicityGroup

order by 1,2;

New people by age group

select 
ContractName,
AgeGroup,
count(*)

from #mhnewpts
group by 
ContractName,
AgeGroup

order by 1,2;

People waiting >5 days for first contact

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
;