SQL disease_progresses/employees

-- http://localhost:4000/api/v1/disease_progresses/employees

WITH "limited_assocs" AS (SELECT
    service_code,
    patient_id,
    assoc_type
FROM
    mv_connected_assocs_disease_progresses
WHERE performed_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999'), "limited_encounters" AS (SELECT
    patient_id,
    onset_date,
    prof_check
FROM mv_encounter_disease_progresses
WHERE onset_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') SELECT DISTINCT mv_stat_patient_personalities.id,
mv_stat_patient_personalities.full_name,
mv_stat_patient_personalities.employee_id,
mv_stat_patient_personalities.birth_date,
DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) as age,
"mv_stat_employees_parties"."DivisionId" as division_id,
  CASE
    WHEN (DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0 OR
          DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 50 AND mv_stat_patient_personalities.gender = 1)
    THEN CASE
           WHEN EXISTS (
                  SELECT 1 FROM limited_assocs WHERE
                    limited_assocs.patient_id = mv_stat_patient_personalities.id AND
                    limited_assocs.service_code in ('T34006','T34011','T34013','T34024','T340001','T34004') AND
                    limited_assocs.assoc_type in (0,1)
                )
                  THEN 2
                ELSE 1
    END ELSE 0
  END hypertension,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 45
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('T34005','T34023','T34025','T34038') AND
            limited_assocs.assoc_type in (0,1)
        ) OR EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.assoc_type in (2)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END diabetes,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('U67002','Y34011','Y34003') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END prostate_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 69 AND mv_stat_patient_personalities.gender = 1
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('TX41973','59300-00','55070-00','55076-00','Х41941') AND
            limited_assocs.assoc_type in (0)
      )
          THEN 2
        ELSE 1
    END ELSE 0
  END breast_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 75 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('D36003','D67006','32084-00','32090-00','32084-02','32090-02') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END colorectal_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 14
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('B33006','B33012') AND
            limited_assocs.assoc_type in (0,1)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END hiv,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_prof_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) as upto_65_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) AS upto_65_prof_visits FROM "mv_stat_patient_personalities" INNER JOIN "mv_stat_declarations" ON "mv_stat_declarations"."patient_id" = "mv_stat_patient_personalities"."id" AND ("mv_stat_declarations"."declaration_status_code" = 'ACTIVE') INNER JOIN "mv_stat_employees_parties" ON "mv_stat_employees_parties"."Id" = "mv_stat_patient_personalities"."employee_id" LEFT OUTER JOIN limited_assocs ON limited_assocs.patient_id = mv_stat_patient_personalities.id WHERE "mv_stat_patient_personalities"."id" IS NOT NULL AND ("mv_stat_employees_parties"."LegalEntityId" = 3228)

ELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE ("mv_stat_employees_parties"."LegalEntityId" = 3228) AND ("mv_stat_employees_parties"."EmployeeStatusRefValue" = 'APPROVED') AND ("mv_stat_employees_parties"."EmployeeTypeRefValue" = 'DOCTOR')