API tools faq
paste
Advertisement
temaon_lieto

disease_progresses/patients

temaon_lieto
Feb 11th, 2025
90
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 11.39 KB | None | 0 0
raw download clone embed print report
  1. WITH items AS (SELECT cascade_lvl01_joined_reports.* FROM cascade_lvl01_joined_reports WHERE cascade_lvl01_joined_reports.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl01_joined_reports.legal_entity_id = 3228), main_counters AS (SELECT COALESCE(COUNT(DISTINCT cascade_lvl02_declarations.id), 0) AS counter,
  2. cascade_lvl02_declarations.patient_id AS patient_id,
  3. CASE
  4.   WHEN ((cascade_lvl01_patients.gender = 1 AND age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 50 AND 999) OR
  5.         (cascade_lvl01_patients.gender = 0 AND age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 40 AND 999))
  6.       THEN
  7.       CASE
  8.           WHEN (countIf(DISTINCT items.code, items.code IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004'))) > 0 THEN 'done'
  9.           ELSE 'need_done'
  10. END
  11. ELSE 'no_need_done'
  12. END AS hypertension_state,
  13. CASE
  14.   WHEN (age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 45 AND 999)
  15.       THEN
  16.       CASE
  17.           WHEN (countIf(DISTINCT items.code, items.code IN ('T34005', 'T34023', 'T34025', 'T34038', '14743-9'))) > 0 THEN 'done'
  18.           ELSE 'need_done'
  19. END
  20. ELSE 'no_need_done'
  21. END AS diabetes_state,
  22. CASE
  23.   WHEN (cascade_lvl01_patients.gender = 0 AND age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 50 AND 999)
  24.       THEN
  25.       CASE
  26.           WHEN (countIf(DISTINCT items.code, items.code IN ('U67002', 'Y34011', 'Y34003'))) > 0 THEN 'done'
  27.           ELSE 'need_done'
  28. END
  29. ELSE 'no_need_done'
  30. END AS prostate_cancer_state,
  31. CASE
  32.   WHEN (cascade_lvl01_patients."gender" = 1 AND age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 50 AND 69)
  33.       THEN
  34.       CASE
  35.           WHEN (countIf(DISTINCT items.code, items.code IN ('X41941', 'X41973', '59300-00', '55070-00', '55076-00'))) > 0 THEN 'done'
  36.           ELSE 'need_done'
  37. END
  38. ELSE 'no_need_done'
  39. END AS breast_cancer_state,
  40. CASE
  41.   WHEN (age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 50 AND 75)
  42.       THEN
  43.       CASE
  44.           WHEN (countIf(DISTINCT items.code, items.code IN ('D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02'))) > 0
  45.               THEN 'done'
  46.           ELSE 'need_done'
  47. END
  48. ELSE 'no_need_done'
  49. END AS colorectal_cancer_state,
  50. CASE
  51.   WHEN (age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 14 AND 999)
  52.       THEN
  53.       CASE
  54.           WHEN (countIf(DISTINCT items.code, items.code IN ('B33006', 'B33012'))) > 0
  55.               THEN 'done'
  56.           ELSE 'need_done'
  57. END
  58. ELSE 'no_need_done'
  59. END AS hiv_state
  60.  FROM cascade_lvl02_declarations JOIN cascade_lvl01_patients ON cascade_lvl01_patients.legal_entity_id = cascade_lvl02_declarations.legal_entity_id AND cascade_lvl01_patients.id = cascade_lvl02_declarations.patient_id
  61. LEFT JOIN items ON items.legal_entity_id = cascade_lvl02_declarations.legal_entity_id AND items.patient_id = cascade_lvl02_declarations.patient_id WHERE cascade_lvl02_declarations.status_code = 'ACTIVE' AND cascade_lvl02_declarations.legal_entity_id = 3228 GROUP BY cascade_lvl02_declarations.patient_id, cascade_lvl01_patients.birth_date, cascade_lvl01_patients.gender), diagnoses AS (SELECT cascade_lvl00_encounter_conditions.patient_id, cascade_lvl00_encounter_conditions.code FROM cascade_lvl00_encounter_conditions WHERE cascade_lvl00_encounter_conditions.ehealth_status = 1 AND cascade_lvl00_encounter_conditions.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl00_encounter_conditions.legal_entity_id = 3228), observations AS (SELECT cascade_lvl00_observations.patient_id, cascade_lvl00_observations.code FROM cascade_lvl00_observations WHERE cascade_lvl00_observations.code IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4', '14647-2') AND cascade_lvl00_observations.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl00_observations.legal_entity_id = 3228), diagnosis_cnt AS (SELECT diagnoses.patient_id,
  62. COUNT(DISTINCT diagnoses.code) AS diagnoses_c1_64_counter,
  63. countIf(DISTINCT diagnoses.code, diagnoses.code IN ('A98')) AS diagnoses_a98_counter,
  64. countIf(DISTINCT diagnoses.code, diagnoses.code IN ('K86', 'K87')) AS diagnoses_c1_65_counter,
  65. countIf(DISTINCT diagnoses.code, diagnoses.code IN ('K76', 'K74')) AS diagnoses_c2_65_counter,
  66. countIf(DISTINCT diagnoses.code, diagnoses.code IN ('T89', 'T90')) AS diagnoses_c3_65_counter FROM diagnoses GROUP BY diagnoses.patient_id HAVING (COUNT(DISTINCT diagnoses.code) > 0)), observations_cnt AS (SELECT observations.patient_id,
  67. countIf(DISTINCT observations.code, observations.code IN ('14647-2')) AS cholesterol_obs_counter,
  68. countIf(DISTINCT observations.code, observations.code IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')) AS observations_c1_64_counter,
  69. countIf(DISTINCT observations.code, observations.code IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')) AS observations_c1_65_counter,
  70. countIf(DISTINCT observations.code, observations.code IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4')) AS observations_c2_65_counter FROM observations GROUP BY observations.patient_id HAVING (COUNT(DISTINCT observations.code) > 0)), reasons_cnt AS (SELECT cascade_lvl00_reasons.patient_id, COUNT(DISTINCT cascade_lvl00_reasons.code) AS a98_counter FROM cascade_lvl00_reasons WHERE cascade_lvl00_reasons.code = 'A98' AND cascade_lvl00_reasons.encounter_status = 1 AND cascade_lvl00_reasons.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl00_reasons.legal_entity_id = 3228 GROUP BY cascade_lvl00_reasons.patient_id HAVING (COUNT(DISTINCT cascade_lvl00_reasons.code) > 0)), reports_cnt AS (SELECT cascade_lvl00_diagnostic_reports.patient_id, COUNT(DISTINCT cascade_lvl00_diagnostic_reports.code) AS counter FROM cascade_lvl00_diagnostic_reports WHERE cascade_lvl00_diagnostic_reports.code IN ('T34001', 'T34006', 'T34024', 'T34011', 'T34013') AND cascade_lvl00_diagnostic_reports.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl00_diagnostic_reports.legal_entity_id = 3228 GROUP BY cascade_lvl00_diagnostic_reports.patient_id HAVING (COUNT(DISTINCT cascade_lvl00_diagnostic_reports.code) > 0)), actions_cnt AS (SELECT cascade_lvl00_actions.patient_id, COUNT(DISTINCT cascade_lvl00_actions.code) AS counter FROM cascade_lvl00_actions WHERE cascade_lvl00_actions.code IN ('K45', 'D45', 'T45') AND cascade_lvl00_actions.asserted_date BETWEEN '2020-01-01 00:00:00' AND '2025-02-11 23:59:59' AND cascade_lvl00_actions.legal_entity_id = 3228 GROUP BY cascade_lvl00_actions.patient_id HAVING (COUNT(DISTINCT cascade_lvl00_actions.code) > 0)), groups_counter AS (SELECT cascade_lvl01_patients.id AS patient_id,
  71. CASE
  72. WHEN (age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 40 AND 64)
  73.     THEN
  74.         CASE
  75.             WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND
  76.               ((reports_cnt.counter > 0 OR observations_cnt.cholesterol_obs_counter > 0) AND
  77.                observations_cnt.observations_c1_64_counter > 0) THEN 'done'
  78.             ELSE 'need_done'
  79. END
  80. ELSE 'no_need_done'
  81. END AS visits_from_40_to_64_state,
  82. CASE
  83. WHEN (age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) BETWEEN 65 AND 999)
  84.     THEN
  85.         CASE
  86.             WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND
  87.               ((observations_cnt.cholesterol_obs_counter > 0 OR reports_cnt.counter > 0) AND
  88.                (((diagnosis_cnt.diagnoses_c1_65_counter > 0 OR
  89.                   diagnosis_cnt.diagnoses_c2_65_counter > 0) AND
  90.                  observations_cnt.observations_c1_65_counter > 0) OR
  91.                 (diagnosis_cnt.diagnoses_c3_65_counter > 0 AND
  92.                  observations_cnt.observations_c2_65_counter > 0))) THEN 'done'
  93.             WHEN (diagnosis_cnt.diagnoses_c1_65_counter > 0 OR
  94.                diagnosis_cnt.diagnoses_c2_65_counter > 0 OR
  95.                diagnosis_cnt.diagnoses_c3_65_counter > 0) THEN 'need_done'
  96.     ELSE 'no_need_done'
  97. END
  98. ELSE 'no_need_done'
  99. END AS visits_upper_65_state FROM cascade_lvl01_patients LEFT JOIN observations_cnt ON cascade_lvl01_patients.id = observations_cnt.patient_id AND
  100.             (observations_cnt.cholesterol_obs_counter > 0 OR
  101.              observations_cnt.observations_c1_64_counter >= 5 OR
  102.              observations_cnt.observations_c1_65_counter >= 5 OR
  103.              observations_cnt.observations_c2_65_counter >= 6)
  104. LEFT JOIN diagnosis_cnt ON cascade_lvl01_patients.id = diagnosis_cnt.patient_id AND
  105.                           (diagnosis_cnt.diagnoses_c1_64_counter > 0 OR
  106.                            diagnosis_cnt.diagnoses_c1_65_counter > 0 OR
  107.                            diagnosis_cnt.diagnoses_c2_65_counter > 0 OR
  108.                            diagnosis_cnt.diagnoses_c3_65_counter > 0)
  109. LEFT JOIN reasons_cnt ON reasons_cnt.patient_id = cascade_lvl01_patients.id
  110. LEFT JOIN reports_cnt ON reports_cnt.patient_id = cascade_lvl01_patients.id
  111. LEFT JOIN actions_cnt ON actions_cnt.patient_id = cascade_lvl01_patients.id WHERE cascade_lvl01_patients.declaration_status = 'ACTIVE' AND cascade_lvl01_patients.legal_entity_id = 3228 AND (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0 OR actions_cnt.counter > 0 OR reports_cnt.counter > 0) GROUP BY cascade_lvl01_patients.id, cascade_lvl01_patients.age, cascade_lvl01_patients.birth_date, visits_from_40_to_64_state, visits_upper_65_state) SELECT DISTINCT cascade_lvl01_patients.id,
  112.     cascade_lvl01_patients.full_name,
  113.     cascade_lvl01_patients.age,
  114.     cascade_lvl01_patients.employee_id,
  115.     cascade_lvl01_patients.gender,
  116.     cascade_lvl01_patients.full_name,
  117.     cascade_lvl01_patients.birth_date,
  118.     cascade_lvl01_patients.legal_entity_id,
  119.     main_counters.hypertension_state,
  120.     main_counters.diabetes_state,
  121.     main_counters.prostate_cancer_state,
  122.     main_counters.breast_cancer_state,
  123.     main_counters.colorectal_cancer_state,
  124.     main_counters.hiv_state,
  125.     CASE WHEN nullIf(groups_counter.visits_from_40_to_64_state, '') IS NULL THEN
  126.              CASE WHEN age('year', cascade_lvl01_patients.birth_date, toDate32('2025-02-11 23:59:59')) BETWEEN 40 AND 64 THEN 'need_done'
  127.                   ELSE 'no_need_done'
  128. END
  129. ELSE groups_counter.visits_from_40_to_64_state
  130. END visits_from_40_to_64_state,
  131.     age('year', cascade_lvl01_patients.birth_date, toDate('2025-02-11 23:59:59')) as current_age,
  132.     CASE WHEN nullIf(groups_counter.visits_upper_65_state, '') IS NULL THEN
  133.              CASE WHEN age('year', cascade_lvl01_patients.birth_date, toDate32('2025-02-11 23:59:59')) BETWEEN 65 AND 999 AND (diagnosis_cnt.diagnoses_c1_65_counter > 0 OR diagnosis_cnt.diagnoses_c2_65_counter > 0 OR diagnosis_cnt.diagnoses_c3_65_counter > 0) THEN 'need_done'
  134.                   ELSE 'no_need_done'
  135. END
  136. ELSE groups_counter.visits_upper_65_state
  137. END visits_upper_65_state FROM cascade_lvl01_patients LEFT JOIN main_counters ON cascade_lvl01_patients.id = main_counters.patient_id
  138. LEFT JOIN groups_counter ON cascade_lvl01_patients.id = groups_counter.patient_id
  139. LEFT JOIN diagnosis_cnt ON cascade_lvl01_patients.id = diagnosis_cnt.patient_id WHERE cascade_lvl01_patients.declaration_status = 'ACTIVE' AND cascade_lvl01_patients.legal_entity_id = 3228 ORDER BY cascade_lvl01_patients.full_name ASC LIMIT 15 OFFSET 0
Tags: test
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!