Mental health expansion

From Kautepedia
Revision as of 03:33, 13 August 2025 by Solomon.pidoke (talk | contribs)
Jump to navigation Jump to search

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.[1]

At the current time, contact data for this service is split between two systems:

  • Kotahi – Community Day Programme (CDP) data is now recorded here.
  • Aiga – All other contact data for this service is still recorded here.

Reporting data must therefore be sourced from both Kotahi and Aiga.

Reporting

Funder reporting is required monthly, on the 20th of each month. Not all items are possible to obtain from the databases, 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 Kotahi and/or 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 both the Aiga and Kotahi 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 for Child and Youth MH contract is provided below.[2]

Aiga data

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
;

Kotahi data ( CDP)

People seen

WITH params AS (
  SELECT 
--SET REPORTING PERIOD HERE
    DATE '2025-07-01' AS start_date, 
    DATE '2025-07-31' AS end_date
),
group_participants AS (
  SELECT DISTINCT
    COALESCE(
      CAST("ClientID" AS TEXT),
      'other_' || CAST(participant_id AS TEXT)
    ) AS participant_key
  FROM kotahi.vw_groupactivity_clients, params
  WHERE "Attendance" = 1
    AND groupactivity_id IN (
      SELECT "Id"
      FROM kotahi.groupactivities, params
      WHERE "ServiceId" = 6
        AND "Date" BETWEEN params.start_date AND params.end_date
    )
),
one_on_one AS (
  SELECT DISTINCT
    CAST("ClientId" AS TEXT) AS participant_key
  FROM kotahi.activities a, params
  WHERE a."ActivityTypeId" IN (60, 61, 62, 63, 64, 65, 86)
    AND a."ActivityStart" BETWEEN params.start_date AND params.end_date
    AND lower(a."CustomFieldDataJson" ->> 'ClientPresent') = 'yes'
    AND "ClientId" IS NOT NULL
)
SELECT COUNT(DISTINCT participant_key) AS people_seen
FROM (
  SELECT participant_key FROM group_participants
  UNION
  SELECT participant_key FROM one_on_one
) combined;

Service exits

SELECT 
  s."Name" AS service_name,
  COUNT(*) AS exit_count
FROM kotahi.activities a
JOIN kotahi.activitytypes at ON a."ActivityTypeId" = at."Id"
JOIN kotahi.services s ON at."ServiceId" = s."Id"
WHERE a."ActivityTypeId" = 86  -- activity indicating exit
  AND a."ActivityStart" BETWEEN DATE '2025-01-01' AND DATE '2025-01-31' --SET REPORTING PERIOD HERE
GROUP BY s."Name"
ORDER BY s."Name";

Individual sessions

SELECT COUNT(*) AS individual_sessions
FROM kotahi.activities a
WHERE a."ActivityTypeId" = 62  -- Narrative Case Note
  AND a."ActivityStart" BETWEEN DATE '2025-07-01' AND DATE '2025-07-31'
  AND lower(a."CustomFieldDataJson" ->> 'ContactType') <> 'group session'
  AND lower(a."CustomFieldDataJson" ->> 'ContactType') <> 'did not attend'
  AND lower(a."CustomFieldDataJson" ->> 'ContactType') <> 'administration'
  AND lower(a."CustomFieldDataJson" ->> 'ContactMethod') <> 'email'

group sessions

 WITH session_attendance AS (
  SELECT
    ga."Id" AS group_id,
    ga."Date" AS session_date,
    COUNT(DISTINCT gcp."ClientParticipantId") AS attendees
  FROM kotahi.groupactivities ga
  JOIN kotahi.groupactivityclientparticipant gcp
    ON ga."Id" = gcp."GroupActivityId"
  WHERE ga."ServiceId" = 6
    AND ga."Date" BETWEEN DATE '2025-06-01' AND DATE '2025-06-30' -- SET REPORTING PERIOD
    AND gcp."Attendance" = 1
  GROUP BY ga."Id", ga."Date"
)
SELECT
  CASE
    WHEN attendees BETWEEN 2 AND 14 THEN '02–14'
    WHEN attendees >= 15 THEN '15+'
    ELSE 'Fewer than 2 (ignored)'
  END AS session_size_band,
  COUNT(*) AS session_count
FROM session_attendance
WHERE attendees >= 2  -- optional: exclude 1-person sessions
GROUP BY session_size_band
ORDER BY session_size_band;

New people seen

WITH seen_in_july AS (
  SELECT DISTINCT
    COALESCE(CAST(vgc."ClientID" AS TEXT), 'other_' || vgc.participant_id::TEXT) AS participant_key,
    vgc."ClientID"
  FROM kotahi.vw_groupactivity_clients vgc
  WHERE vgc."Attendance" = 1
    AND vgc.groupactivity_id IN (
      SELECT "Id"
      FROM kotahi.groupactivities
      WHERE "ServiceId" = 6
        AND "Date" BETWEEN DATE '2025-07-01' AND DATE '2025-07-31'
    )

  UNION

  SELECT DISTINCT
    CAST("ClientId" AS TEXT) AS participant_key,
    "ClientId"
  FROM kotahi.activities a
  WHERE a."ActivityTypeId" IN (60, 61, 62, 63, 64, 65, 86)
    AND a."ActivityStart" BETWEEN DATE '2025-07-01' AND DATE '2025-07-31'
    AND lower(a."CustomFieldDataJson" ->> 'ClientPresent') = 'yes'
    AND "ClientId" IS NOT NULL
),

seen_prior_11_months AS (
  SELECT DISTINCT
    COALESCE(CAST(vgc."ClientID" AS TEXT), 'other_' || vgc.participant_id::TEXT) AS participant_key
  FROM kotahi.vw_groupactivity_clients vgc
  WHERE vgc."Attendance" = 1
    AND vgc.groupactivity_id IN (
      SELECT "Id"
      FROM kotahi.groupactivities
      WHERE "ServiceId" = 6
        AND "Date" BETWEEN DATE '2024-08-01' AND DATE '2025-06-30'
    )

  UNION

  SELECT DISTINCT
    CAST("ClientId" AS TEXT)
  FROM kotahi.activities a
  WHERE a."ActivityTypeId" IN (60, 61, 62, 63, 64, 65, 86)
    AND a."ActivityStart" BETWEEN DATE '2024-08-01' AND DATE '2025-06-30'
    AND lower(a."CustomFieldDataJson" ->> 'ClientPresent') = 'yes'
    AND "ClientId" IS NOT NULL
),

new_people AS (
  SELECT sj.participant_key, sj."ClientID"
  FROM seen_in_july sj
  LEFT JOIN seen_prior_11_months sp ON sj.participant_key = sp.participant_key
  WHERE sp.participant_key IS NULL
    AND sj."ClientID" IS NOT NULL
)

SELECT
  CASE
    WHEN e."Description" ILIKE ANY (ARRAY[
      '%samoan%', '%tongan%', '%cook island%', '%niuean%', '%tokelauan%', '%fijian%', '%pacific%'
    ]) THEN 'Pacific'
    WHEN e."Description" ILIKE '%maori%' THEN 'Māori'
    WHEN e."Description" ILIKE ANY (ARRAY['%chinese%', '%indian%', '%asian%', '%filipino%']) THEN 'Asian'
    WHEN e."Description" ILIKE ANY (ARRAY['%european%', '%nz european%', '%new zealand european%']) THEN 'NZ European'
    ELSE 'Other'
  END AS ethnicity_group,

  CASE
    WHEN EXTRACT(YEAR FROM AGE(DATE '2025-07-31', c."DOB")) BETWEEN 0 AND 11 THEN '0–11'
    WHEN EXTRACT(YEAR FROM AGE(DATE '2025-07-31', c."DOB")) BETWEEN 12 AND 17 THEN '12–17'
    WHEN EXTRACT(YEAR FROM AGE(DATE '2025-07-31', c."DOB")) BETWEEN 18 AND 24 THEN '18–24'
    WHEN EXTRACT(YEAR FROM AGE(DATE '2025-07-31', c."DOB")) BETWEEN 25 AND 64 THEN '25–64'
    WHEN EXTRACT(YEAR FROM AGE(DATE '2025-07-31', c."DOB")) >= 65 THEN '65+'
    ELSE 'Unknown'
  END AS age_group,

  COUNT(*) AS new_people_seen

FROM new_people np
JOIN kotahi.clients c ON c."ClientID" = np."ClientID"
LEFT JOIN kotahi.ethnicities e ON e."ID" = c."PrioritisedEthnicity"

GROUP BY ethnicity_group, age_group
ORDER BY ethnicity_group, age_group;

References

  1. ↑ Note that mental health contracts have seen a large number of modifications and revisions, and this service is also referred to as 'Access and Choice'.
  2. ↑ Any identifiers or sensitive information are redacted.
Retrieved from "https://wiki.kautepasifika.com/mediawiki/index.php?title=Mental_health_expansion&oldid=3501"