Editing Mental health expansion (section)

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