Editing Mental health expansion (section)

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