WITH
-- TOTAL PESERTA
-- total peserta berumur < 30 tahun
total_peserta_30tahun AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta30th
  FROM
    one_etl.mgm_header
  WHERE
    Mgm_HeaderMgm_McuID = PID
    AND Mgm_HeaderIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) < 30
)
, -- total peserta berumur 30~40 tahun
  total_peserta_3040tahun AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta3040th
  FROM
    one_etl.mgm_header
  WHERE
    Mgm_HeaderMgm_McuID = PID
    AND Mgm_HeaderIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) >= 30
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) < 40
)
, -- total peserta berumur 40~50 tahun
  total_peserta_4050tahun AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta4050th
  FROM
    one_etl.mgm_header
  WHERE
    Mgm_HeaderMgm_McuID = PID
    AND Mgm_HeaderIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) >= 40
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) < 50
)
, -- total peserta berumur 50 tahun
  total_peserta_50tahun AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta50th
  FROM
    one_etl.mgm_header
  WHERE
    Mgm_HeaderMgm_McuID = PID
    AND Mgm_HeaderIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(Mgm_HeaderT_OrderHeaderM_PatientAge, ' tahun', 1) AS UNSIGNED
    ) >= 50
)
, -- total peserta obesitas
  total_peserta_obesitas AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS TOTAL_OBESE
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanID = 3
    OR mk.Mcu_KelainanID = 4
    AND mk.Mcu_KelainanIsActive = 'Y'
)
, -- total peserta tidak berolahraga
  total_peserta_tidak_berolahraga AS (
  WITH berolahraga AS (
    SELECT
      count(distinct mh.Mgm_HeaderT_OrderHeaderID) as berolahraga
    FROM
      one_etl.mgm_riwayat mr
      JOIN one_etl.mgm_header mh
    ON mr.Mgm_RiwayatMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderIsActive = 'Y'
    WHERE
      mh.Mgm_HeaderMgm_McuID = PID
      AND mr.Mgm_RiwayatMcu_RiwayatGroupID = 6
      AND mr.Mgm_RiwayatMcu_RiwayatID IN (145, 146, 147, 148)
      AND mr.Mgm_RiwayatIsActive = 'Y'
  )
  , total_peserta AS (
    SELECT
      COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta
    FROM
      one_etl.mcu_kelainan mk
      JOIN one_etl.mgm_detail md
    ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
    AND md.Mgm_DetailIsActive = 'Y'
    JOIN one_etl.mgm_header mh
    ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderMgm_McuID = PID
    AND mh.Mgm_HeaderIsActive = 'Y'
  )
  SELECT
    tp.total_peserta - bo.berolahraga AS tidak_berolahraga
  FROM
    total_peserta tp
    , berolahraga bo
)
,-- total peserta laki laki
  total_peserta_lakilaki AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'L'
  AND mh.Mgm_HeaderIsActive = 'Y'
)
,-- total peserta perempuan
  total_peserta_perempuan AS (
  SELECT
    COUNT(DISTINCT Mgm_HeaderT_OrderHeaderID) AS total_peserta
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'P'
  AND mh.Mgm_HeaderIsActive = 'Y'
)
  -- TOTAL RISIKO
, -- total risiko umur < 30 tahun
  total_kelainan_30tahun AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 30
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 30
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 3
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 30
)
, -- total risiko umur 30 ~ 40 tahun
  total_kelainan_3040tahun AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 30
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 40
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 30
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 40
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  AND CAST(
    SUBSTRING_INDEX(
      mh.Mgm_HeaderT_OrderHeaderM_PatientAge
      , ' tahun'
      , 1
    ) AS UNSIGNED
  ) >= 30
  AND CAST(
    SUBSTRING_INDEX(
      mh.Mgm_HeaderT_OrderHeaderM_PatientAge
      , ' tahun'
      , 1
    ) AS UNSIGNED
  ) < 40
)
, -- total risiko umur 40 ~ 50 tahun
  total_kelainan_4050tahun AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 40
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 50
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 40
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) < 50
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  AND CAST(
    SUBSTRING_INDEX(
      mh.Mgm_HeaderT_OrderHeaderM_PatientAge
      , ' tahun'
      , 1
    ) AS UNSIGNED
  ) >= 40
  AND CAST(
    SUBSTRING_INDEX(
      mh.Mgm_HeaderT_OrderHeaderM_PatientAge
      , ' tahun'
      , 1
    ) AS UNSIGNED
  ) < 50
)
, -- total risiko umur > 50 tahun
  total_kelainan_50tahun AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 50
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND CAST(
      SUBSTRING_INDEX(
        mh.Mgm_HeaderT_OrderHeaderM_PatientAge
        , ' tahun'
        , 1
      ) AS UNSIGNED
    ) >= 50
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , mh.Mgm_HeaderT_OrderHeaderM_PatientAge
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  AND CAST(
    SUBSTRING_INDEX(
      mh.Mgm_HeaderT_OrderHeaderM_PatientAge
      , ' tahun'
      , 1
    ) AS UNSIGNED
  ) >= 50
)
, -- total risiko obesitas
  total_kelainan_obesitas AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , 'METABOLISME LEMAK' AS Grup
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanClasification = 'asia_pacific'
    AND mk.Mcu_KelainanMcu_KelainanGroupID = 1
    AND (
      mk.Mcu_KelainanID = 3
      OR mk.Mcu_KelainanID = 4
    )
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND mh.Mgm_HeaderT_OrderHeaderID IN (
      SELECT
        mh2.Mgm_HeaderT_OrderHeaderID
      FROM
        one_etl.mcu_kelainan mk2
        JOIN one_etl.mgm_detail md2
      ON mk2.Mcu_KelainanID = md2.Mgm_DetailMcu_KelainanID
      AND md2.Mgm_DetailIsActive = 'Y'
      JOIN one_etl.mgm_header mh2
      ON md2.Mgm_DetailMgm_HeaderID = mh2.Mgm_HeaderID
      AND mh2.Mgm_HeaderMgm_McuID = PID
      AND mh2.Mgm_HeaderIsActive = 'Y'
      WHERE
        mk2.Mcu_KelainanMcu_KelainanGroupID = 17
        AND mk2.Mcu_KelainanIsActive = 'Y'
    )
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , "Hipertensi" as Grup
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanClasification = 'asia_pacific'
    AND mk.Mcu_KelainanMcu_KelainanGroupID = 1
    AND (
      mk.Mcu_KelainanID = 3
      OR mk.Mcu_KelainanID = 4
    )
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND mh.Mgm_HeaderT_OrderHeaderID IN (
      SELECT
        mh2.Mgm_HeaderT_OrderHeaderID
      FROM
        one_etl.mcu_kelainan mk2
        JOIN one_etl.mgm_detail md2
      ON mk2.Mcu_KelainanID = md2.Mgm_DetailMcu_KelainanID
      AND md2.Mgm_DetailIsActive = 'Y'
      JOIN one_etl.mgm_header mh2
      ON md2.Mgm_DetailMgm_HeaderID = mh2.Mgm_HeaderID
      AND mh2.Mgm_HeaderMgm_McuID = PID
      AND mh2.Mgm_HeaderIsActive = 'Y'
      WHERE
        mk2.Mcu_KelainanMcu_KelainanGroupID = 17
        AND mk2.Mcu_KelainanIsActive = 'Y'
    )
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , "Refraksi" AS Grup
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanClasification = 'asia_pacific'
    AND mk.Mcu_KelainanMcu_KelainanGroupID = 1
    AND (
      mk.Mcu_KelainanID = 3
      OR mk.Mcu_KelainanID = 4
    )
    AND mk.Mcu_KelainanIsActive = 'Y'
    AND mh.Mgm_HeaderT_OrderHeaderID IN (
      SELECT
        mh2.Mgm_HeaderT_OrderHeaderID
      FROM
        one_etl.mcu_kelainan mk2
        JOIN one_etl.mgm_detail md2
      ON mk2.Mcu_KelainanID = md2.Mgm_DetailMcu_KelainanID
      AND md2.Mgm_DetailIsActive = 'Y'
      JOIN one_etl.mgm_header mh2
      ON md2.Mgm_DetailMgm_HeaderID = mh2.Mgm_HeaderID
      AND mh2.Mgm_HeaderMgm_McuID = PID
      AND mh2.Mgm_HeaderIsActive = 'Y'
      WHERE
        mk2.Mcu_KelainanMcu_KelainanGroupID = 2
        AND mk2.Mcu_KelainanIsActive = 'Y'
    )
)
, -- total risiko tidak berolahraga
  total_kelainan_tidak_berolahraga AS (
  WITH berolahraga AS (
    SELECT DISTINCT
      mh.Mgm_HeaderT_OrderHeaderID
    FROM
      one_etl.mgm_riwayat mr
      JOIN one_etl.mgm_header mh
    ON mr.Mgm_RiwayatMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderIsActive = 'Y'
    WHERE
      mh.Mgm_HeaderMgm_McuID = PID
      AND mr.Mgm_RiwayatMcu_RiwayatGroupID = 6
      AND mr.Mgm_RiwayatMcu_RiwayatID IN (145, 146, 147, 148)
      AND mr.Mgm_RiwayatIsActive = 'Y'
  )
  , total_peserta_grup_17 AS (
    SELECT DISTINCT
      mh.Mgm_HeaderT_OrderHeaderID
      , Mcu_KelainanMcu_KelainanGroupID
    FROM
      one_etl.mcu_kelainan mk
      JOIN one_etl.mgm_detail md
    ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
    AND md.Mgm_DetailIsActive = 'Y'
    JOIN one_etl.mgm_header mh
    ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderMgm_McuID = PID
    AND mh.Mgm_HeaderIsActive = 'Y'
    WHERE
      mk.Mcu_KelainanMcu_KelainanGroupID = 17
  )
  , total_peserta_grup_3 AS (
    SELECT DISTINCT
      mh.Mgm_HeaderT_OrderHeaderID
      , Mcu_KelainanMcu_KelainanGroupID
    FROM
      one_etl.mcu_kelainan mk
      JOIN one_etl.mgm_detail md
    ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
    AND md.Mgm_DetailIsActive = 'Y'
    JOIN one_etl.mgm_header mh
    ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderMgm_McuID = PID
    AND mh.Mgm_HeaderIsActive = 'Y'
    WHERE
      mk.Mcu_KelainanMcu_KelainanGroupID = 3
  )
  , total_peserta_grup_2 AS (
    SELECT DISTINCT
      mh.Mgm_HeaderT_OrderHeaderID
      , Mcu_KelainanMcu_KelainanGroupID
    FROM
      one_etl.mcu_kelainan mk
      JOIN one_etl.mgm_detail md
    ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
    AND md.Mgm_DetailIsActive = 'Y'
    JOIN one_etl.mgm_header mh
    ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
    AND mh.Mgm_HeaderMgm_McuID = PID
    AND mh.Mgm_HeaderIsActive = 'Y'
    WHERE
      mk.Mcu_KelainanMcu_KelainanGroupID = 2
  )
  SELECT
    tp.Mgm_HeaderT_OrderHeaderID
    , tp.Mcu_KelainanMcu_KelainanGroupID
  FROM
    total_peserta_grup_17 tp
    LEFT JOIN berolahraga bo
  ON tp.Mgm_HeaderT_OrderHeaderID = bo.Mgm_HeaderT_OrderHeaderID
  WHERE
    bo.Mgm_HeaderT_OrderHeaderID IS NULL
  UNION
  SELECT
    tp.Mgm_HeaderT_OrderHeaderID
    , tp.Mcu_KelainanMcu_KelainanGroupID
  FROM
    total_peserta_grup_3 tp
    LEFT JOIN berolahraga bo
  ON tp.Mgm_HeaderT_OrderHeaderID = bo.Mgm_HeaderT_OrderHeaderID
  WHERE
    bo.Mgm_HeaderT_OrderHeaderID IS NULL
  UNION
  SELECT
    tp.Mgm_HeaderT_OrderHeaderID
    , tp.Mcu_KelainanMcu_KelainanGroupID
  FROM
    total_peserta_grup_2 tp
    LEFT JOIN berolahraga bo
  ON tp.Mgm_HeaderT_OrderHeaderID = bo.Mgm_HeaderT_OrderHeaderID
  WHERE
    bo.Mgm_HeaderT_OrderHeaderID IS NULL
)
,-- total risiko laki laki
  total_kelainan_lakilaki AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'L'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'L'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 3
    AND mk.Mcu_KelainanIsActive = 'Y'
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'L'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
)
,-- total risiko perempuan
  total_kelainan_perempuan AS (
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'P'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 17
    AND mk.Mcu_KelainanIsActive = 'Y'
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'P'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 3
    AND mk.Mcu_KelainanIsActive = 'Y'
  UNION
  SELECT
    mh.Mgm_HeaderT_OrderHeaderID
    , mk.Mcu_KelainanName
    , Mcu_KelainanMcu_KelainanGroupID
  FROM
    one_etl.mcu_kelainan mk
    JOIN one_etl.mgm_detail md
  ON mk.Mcu_KelainanID = md.Mgm_DetailMcu_KelainanID
  AND md.Mgm_DetailIsActive = 'Y'
  JOIN one_etl.mgm_header mh
  ON md.Mgm_DetailMgm_HeaderID = mh.Mgm_HeaderID
  AND mh.Mgm_HeaderMgm_McuID = PID
  AND mh.Mgm_HeaderM_SexCode = 'P'
  AND mh.Mgm_HeaderIsActive = 'Y'
  WHERE
    mk.Mcu_KelainanMcu_KelainanGroupID = 2
    AND mk.Mcu_KelainanIsActive = 'Y'
)
  -- TERDAMPAK 30
, total_terdampak_risiko_30th AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_30tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_30tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_30tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_30tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_30tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_30tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  -- TERDAMPAK 3040
, total_terdampak_risiko_3040th AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_3040tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_3040tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_3040tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_3040tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_3040tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_3040tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  -- TERDAMPAK 4050
, total_terdampak_risiko_4050th AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_4050tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_4050tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_4050tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_4050tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_4050tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_4050tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  -- TERDAMPAK 50
, total_terdampak_risiko_50th AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_50tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_50tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_50tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_50tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_50tahun.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_50tahun
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  --  TERDAMPAK OBESITAS
, total_terdampak_risiko_obesitas AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_obesitas.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_obesitas
  WHERE
    Grup = 'METABOLISME LEMAK'
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_obesitas.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_obesitas
  WHERE
    Grup = 'Hipertensi'
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_obesitas.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_obesitas
  WHERE
    Grup = 'Refraksi'
)
  -- TERDAMPAK TIDAK BEROLAHRAGA
, total_terdampak_risiko_tidak_berolahraga AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_tidak_berolahraga.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_tidak_berolahraga
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_tidak_berolahraga.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_tidak_berolahraga
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_tidak_berolahraga.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_tidak_berolahraga
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  -- TERDAMPAK LAKI-LAKI
, total_terdampak_risiko_lakilaki AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_lakilaki.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_lakilaki
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_lakilaki.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_lakilaki
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_lakilaki.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_lakilaki
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
  -- TERDAMPAK PEREMPUAN
, total_terdampak_risiko_perempuan AS (
  SELECT
    COUNT(
      DISTINCT total_kelainan_perempuan.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 17 AS KelainanGroupID
  FROM
    total_kelainan_perempuan
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 17
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_perempuan.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 3 AS KelainanGroupID
  FROM
    total_kelainan_perempuan
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 3
  UNION
  SELECT
    COUNT(
      DISTINCT total_kelainan_perempuan.Mgm_HeaderT_OrderHeaderID
    ) AS terdampak
    , 2 AS KelainanGroupID
  FROM
    total_kelainan_perempuan
  WHERE
    Mcu_KelainanMcu_KelainanGroupID = 2
)
-- UNION SHOW
SELECT
  '1' AS Nomor
  , 'Kelompok Umur' AS faktor_risiko
  , NULL AS Metabolisme_Lemak_Y
  , NULL AS Metabolisme_Lemak_N
  , NULL AS Refraksi_Y
  , NULL AS Refraksi_N
  , NULL AS Hipertensi_Y
  , NULL AS Hipertensi_N
UNION
-- 30 TAHUN
SELECT
  NULL AS Nomor
  , '< 30 Tahun' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta30th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta30th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta30th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_30tahun total
  LEFT JOIN total_terdampak_risiko_30th total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- 3040 TAHUN
SELECT
  NULL AS Nomor
  , '30 ~ 40 Tahun' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta3040th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta3040th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta3040th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_3040tahun total
  LEFT JOIN total_terdampak_risiko_3040th total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- 4050 TAHUN
SELECT
  NULL AS Nomor
  , '40 ~ 50 Tahun' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta4050th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta4050th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta4050th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_4050tahun total
  LEFT JOIN total_terdampak_risiko_4050th total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- 50 TAHUN
SELECT
  NULL AS Nomor
  , '> 50 Tahun' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta50th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta50th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta50th - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_50tahun total
  LEFT JOIN total_terdampak_risiko_50th total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- OBESITAS
SELECT
  '2' AS Nomor
  , 'Obesitas' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.TOTAL_OBESE - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.TOTAL_OBESE - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.TOTAL_OBESE - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_obesitas total
  LEFT JOIN total_terdampak_risiko_obesitas total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- TIDAK BEROLAHRAGA
SELECT
  '3' AS Nomor
  , 'Tidak berolah-raga' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.tidak_berolahraga - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.tidak_berolahraga - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.tidak_berolahraga - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_tidak_berolahraga total
  LEFT JOIN total_terdampak_risiko_tidak_berolahraga total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
SELECT
  '4' AS Nomor
  , 'Kelompok Gender' AS faktor_risiko
  , NULL AS Metabolisme_Lemak_Y
  , NULL AS Metabolisme_Lemak_N
  , NULL AS Refraksi_Y
  , NULL AS Refraksi_N
  , NULL AS Hipertensi_Y
  , NULL AS Hipertensi_N
UNION
-- LAKI-LAKI
SELECT
  NULL AS Nomor
  , 'Laki-Laki' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_lakilaki total
  LEFT JOIN total_terdampak_risiko_lakilaki total_p
ON total_p.KelainanGroupID IN (17, 3, 2)
UNION
-- PEREMPUAN
SELECT
  NULL AS Nomor
  , 'Perempuan' AS faktor_risiko
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 17 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Metabolisme_Lemak_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 3 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Refraksi_N
  , SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_Y
  , total.total_peserta - SUM(
    CASE
      WHEN total_p.KelainanGroupID = 2 THEN total_p.terdampak
      ELSE 0
    END
  ) AS Hipertensi_N
FROM
  total_peserta_perempuan total
  LEFT JOIN total_terdampak_risiko_perempuan total_p
ON total_p.KelainanGroupID IN (17, 3, 2)