Editing Mental health expansion (section)

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

{| class="wikitable sortable"
! Item || Available
|-
| FTE || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| Number of sessions || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of people seen || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of new people seen || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of people waiting more than five days || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of service exits || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of rejected referrals || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| Number of onward referrals || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| Number of sessions involve whanau || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|-
| Number of new people by ethnicity || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Number of new people by age || style="text-align:center;" | <span style="color:#33d17a; font-weight:bold;">{{#far:circle-check fa-xl}}</span>
|-
| Flexifund usage || style="text-align:center;" | <span style="color:#e01b24; font-weight:bold;">{{#far:circle-xmark fa-xl}}</span>
|}

===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.<ref>Any identifiers or sensitive information are redacted.</ref>

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

=== Kotahi data ( CDP) ===

==== People seen ====
<syntaxhighlight lang="postgresql" line>
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;

</syntaxhighlight>

==== Service exits====
<syntaxhighlight lang="postgresql" line>
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";
</syntaxhighlight>

==== Individual sessions ====
<syntaxhighlight lang="postgresql" line>
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'
</syntaxhighlight>

==== group sessions ====
<syntaxhighlight lang="postgresql" line>
 WITH params AS (
  -- Set the first day of the reporting month here
  SELECT DATE '2025-06-01' AS rpt_start
),
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"
  CROSS JOIN params p
  WHERE ga."ServiceId" = 6
    AND ga."Date" >= p.rpt_start
    AND ga."Date" <  (p.rpt_start + INTERVAL '1 month')
    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         -- exclude 1-person sessions which I doubt will ever happen 
GROUP BY session_size_band
ORDER BY session_size_band;
</syntaxhighlight>

==== New people seen ====
<b> This relies on aiga data being migrated! </b> - So in the mean time this list needs to be filtered manually by cross referencing Aiga :'(
<syntaxhighlight lang="postgresql" line>
WITH params AS (
  SELECT DATE '2025-08-01' AS rpt_start  -- Set reporting period start
),
enrolled_in_month AS (
  SELECT
    a."ClientId",
    MIN(a."ActivityStart") AS enrolment_date   -- first enrolment form in the month
  FROM kotahi.activities a
  CROSS JOIN params p
  WHERE a."ActivityTypeId" = 60                -- enrolment form
    AND a."ClientId" IS NOT NULL
    AND a."ActivityStart" >= p.rpt_start
    AND a."ActivityStart" <  (p.rpt_start + INTERVAL '1 month')
  GROUP BY a."ClientId"
)
select
  c."ClientID" ,
  c."FirstName",
  c."LastName",
  e.enrolment_date AS "ActivityStart"
FROM enrolled_in_month e
JOIN kotahi.clients c
  ON c."ClientID" = e."ClientId"
CROSS JOIN params p
-- Exclude clients with any CDP (ServiceId = 6) activity in the prior 11 months
WHERE NOT EXISTS (
  SELECT 1
  FROM kotahi.activities a2
  JOIN kotahi.activitytypes aty2
    ON aty2."Id" = a2."ActivityTypeId"
  WHERE a2."ClientId"   = e."ClientId"
    AND aty2."ServiceId" = 6
    AND a2."ActivityStart" >= (p.rpt_start - INTERVAL '11 months')
    AND a2."ActivityStart" <  p.rpt_start
)
ORDER BY c."LastName", c."FirstName", e.enrolment_date;
</syntaxhighlight>

And then you can group the filtered clients by ethnicity:

<syntaxhighlight lang="postgresql" line>
WITH base AS (
    SELECT unnest(ARRAY[/*client id's here */])::int AS client_id
),
eth AS (
    SELECT 
        c."ClientID",
        e."Description" AS ethnicity
    FROM base b
    JOIN kotahi.clients c ON c."ClientID" = b.client_id
    LEFT JOIN kotahi.ethnicities e 
           ON e."ID" = c."PrioritisedEthnicity"
)
SELECT CASE
           WHEN ethnicity ILIKE 'Māori' THEN 'Māori'
           WHEN ethnicity ILIKE ANY (ARRAY[
                'Samoan','Tongan','Cook Island','Niuean','Tokelauan',
                'Fijian','Other Pacific Island','Pasifika'
           ]) THEN 'Pasifika'
           WHEN ethnicity ILIKE ANY (ARRAY['Chinese','Indian','Asian%']) THEN 'Asian'
           WHEN ethnicity ILIKE 'NZ European' OR ethnicity ILIKE 'New Zealand European' THEN 'NZ European'
           ELSE 'Other'
       END AS ethnicity_group,
       COUNT(*) AS people_count
FROM eth
GROUP BY ethnicity_group
ORDER BY ethnicity_group;
</syntaxhighlight>

And by age:
<syntaxhighlight lang="postgresql" line>


WITH params AS (
    SELECT DATE '2025-07-01' AS rpt_start
),
base AS (
    SELECT unnest(ARRAY[/* Client IDs here */])::int AS client_id
),
ages AS (
    SELECT
        c."ClientID",
        CASE
            WHEN c."DOB" IS NULL THEN 'Unknown'
            WHEN FLOOR(EXTRACT(YEAR FROM age(p.rpt_start, c."DOB"))) BETWEEN 0  AND 11 THEN '00–11'
            WHEN FLOOR(EXTRACT(YEAR FROM age(p.rpt_start, c."DOB"))) BETWEEN 12 AND 17 THEN '12–17'
            WHEN FLOOR(EXTRACT(YEAR FROM age(p.rpt_start, c."DOB"))) BETWEEN 18 AND 24 THEN '18–24'
            WHEN FLOOR(EXTRACT(YEAR FROM age(p.rpt_start, c."DOB"))) BETWEEN 25 AND 64 THEN '25–64'
            WHEN FLOOR(EXTRACT(YEAR FROM age(p.rpt_start, c."DOB"))) >= 65          THEN '65+'
            ELSE 'Unknown'
        END AS age_group
    FROM base b
    JOIN kotahi.clients c ON c."ClientID" = b.client_id
    CROSS JOIN params p
)
SELECT
    age_group,
    COUNT(*) AS people_count
FROM ages
GROUP BY age_group
ORDER BY
    CASE age_group
        WHEN '00–11' THEN 1
        WHEN '12–17' THEN 2
        WHEN '18–24' THEN 3
        WHEN '25–64' THEN 4
        WHEN '65+'   THEN 5
        ELSE 6
    END;

</syntaxhighlight>