API tools faq
paste
Advertisement
temaon_lieto

New Patient sql

temaon_lieto
Mar 14th, 2024
90
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 18.96 KB | None | 0 0
raw download clone embed print report
  1. EXPLAIN ANALYSE
  2. WITH
  3.     "items" AS (SELECT "mv_stat_lvl_0_ehr_service_requests".*
  4.                 FROM ((SELECT "mv_stat_lvl_0_ehr_service_requests".*
  5.                        FROM ((SELECT "mv_stat_lvl_0_ehr_service_requests".*
  6.                               FROM ((SELECT mv_stat_lvl_0_ehr_service_requests.id,
  7.                                             mv_stat_lvl_0_ehr_service_requests.asserted_date,
  8.                                             mv_stat_lvl_0_ehr_service_requests.code,
  9.                                             mv_stat_lvl_0_ehr_service_requests.item_type,
  10.                                             mv_stat_lvl_0_ehr_service_requests.status :: VARCHAR,
  11.                                             mv_stat_lvl_0_ehr_service_requests.title,
  12.                                             mv_stat_lvl_0_ehr_service_requests.employee_id,
  13.                                             mv_stat_lvl_0_ehr_service_requests.legal_entity_id,
  14.                                             mv_stat_lvl_0_ehr_service_requests.patient_id
  15.                                      FROM "mv_stat_lvl_0_ehr_service_requests"
  16.                                      WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  17.                                        AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 3493
  18.                                        AND mv_stat_lvl_0_ehr_service_requests.code IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004', 'T34005', 'T34023', 'T34025', 'T34038',
  19.                                                                                        'U67002', 'Y34011', 'Y34003', 'Х41941', 'X41973', '59300-00', '55070-00', '55076-00', 'D36003',
  20.                                                                                        'D67006', '32084-00', '32090-00', '32084-02', '32090-02', 'B33006', 'B33012', '14743-9')
  21.                                     )
  22.                                     UNION
  23.                                     (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id,
  24.                                             mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date,
  25.                                             mv_stat_lvl_0_ehr_diagnostic_reports.code,
  26.                                             mv_stat_lvl_0_ehr_diagnostic_reports.item_type,
  27.                                             mv_stat_lvl_0_ehr_diagnostic_reports.status :: VARCHAR,
  28.                                             mv_stat_lvl_0_ehr_diagnostic_reports.title,
  29.                                             mv_stat_lvl_0_ehr_diagnostic_reports.employee_id,
  30.                                             mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id,
  31.                                             mv_stat_lvl_0_ehr_diagnostic_reports.patient_id
  32.                                      FROM "mv_stat_lvl_0_ehr_diagnostic_reports"
  33.                                      WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  34.                                        AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 3493
  35.                                        AND mv_stat_lvl_0_ehr_diagnostic_reports.code IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004', 'T34005', 'T34023', 'T34025', 'T34038',
  36.                                                                                          'U67002', 'Y34011', 'Y34003', 'Х41941', 'X41973', '59300-00', '55070-00', '55076-00', 'D36003',
  37.                                                                                          'D67006', '32084-00', '32090-00', '32084-02', '32090-02', 'B33006', 'B33012', '14743-9')
  38.                                     )) "mv_stat_lvl_0_ehr_service_requests")
  39.                              UNION
  40.                              (SELECT mv_stat_lvl_1_ehr_observations.id,
  41.                                      mv_stat_lvl_1_ehr_observations.asserted_date,
  42.                                      mv_stat_lvl_1_ehr_observations.code,
  43.                                      1                  AS item_type,
  44.                                      'final' :: VARCHAR AS status,
  45.                                      mv_stat_lvl_1_ehr_observations.title,
  46.                                      mv_stat_lvl_1_ehr_observations.employee_id,
  47.                                      mv_stat_lvl_1_ehr_observations.legal_entity_id,
  48.                                      mv_stat_lvl_1_ehr_observations.patient_id
  49.                               FROM "mv_stat_lvl_1_ehr_observations"
  50.                               WHERE "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  51.                                 AND "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 3493
  52.                                 AND mv_stat_lvl_1_ehr_observations.code IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004', 'T34005', 'T34023', 'T34025', 'T34038',
  53.                                                                             'U67002', 'Y34011', 'Y34003', 'Х41941', 'X41973', '59300-00', '55070-00', '55076-00', 'D36003',
  54.                                                                             'D67006', '32084-00', '32090-00', '32084-02', '32090-02', 'B33006', 'B33012', '14743-9')
  55.                              )) "mv_stat_lvl_0_ehr_service_requests")
  56.                      ) "mv_stat_lvl_0_ehr_service_requests"),
  57.     main_counters AS (
  58.         SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) AS counter,
  59.                mv_stat_lvl_1_declarations.patient_id                      AS patient_id,
  60.                CASE WHEN (("mv_stat_lvl_1_patients"."gender" = 1 AND DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 999) OR ("mv_stat_lvl_1_patients"."gender" = 0 AND DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 999)) THEN
  61.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004'))) > 0 THEN 'done'
  62.                              ELSE 'need_done'
  63.                             END
  64.                     ELSE
  65.                         'no_need_done'
  66.                    END hypertension_state,
  67.                CASE WHEN (DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 45 AND 999) THEN
  68.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('T34005', 'T34023', 'T34025', 'T34038', '14743-9'))) > 0 THEN 'done'
  69.                              ELSE 'need_done'
  70.                             END
  71.                     ELSE
  72.                         'no_need_done'
  73.                    END AS diabetes_state,
  74.                CASE WHEN ("mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2023-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 999)) THEN
  75.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('U67002', 'Y34011', 'Y34003'))) > 0 THEN 'done'
  76.                              ELSE 'need_done'
  77.                             END
  78.                     ELSE
  79.                         'no_need_done'
  80.                    END AS prostate_cancer_state,
  81.                CASE WHEN ("mv_stat_lvl_1_patients"."gender" = 1 AND (DATE_PART('YEAR', AGE('2023-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 999)) THEN
  82.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('Х41941', 'X41973', '59300-00', '55070-00', '55076-00'))) > 0 THEN 'done'
  83.                              ELSE 'need_done'
  84.                             END
  85.                     ELSE
  86.                         'no_need_done'
  87.                    END AS breast_cancer_state,
  88.                CASE WHEN ("mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2023-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 999)) THEN
  89.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02'))) > 0 THEN 'done'
  90.                              ELSE 'need_done'
  91.                             END
  92.                     ELSE
  93.                         'no_need_done'
  94.                    END AS colorectal_cancer_state,
  95.                CASE WHEN (DATE_PART('YEAR', AGE('2023-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 14 AND 999) THEN
  96.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('B33006', 'B33012'))) > 0 THEN 'done'
  97.                              ELSE 'need_done'
  98.                             END
  99.                     ELSE
  100.                         'no_need_done'
  101.                    END AS hiv_state
  102.         FROM "mv_stat_lvl_1_declarations"
  103.                  JOIN mv_stat_lvl_1_patients
  104.                       ON mv_stat_lvl_1_patients.legal_entity_id =
  105.                          mv_stat_lvl_1_declarations.legal_entity_id
  106.                           AND
  107.                          mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id
  108.                  LEFT JOIN items
  109.                            ON items.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id
  110.                                AND items.patient_id = mv_stat_lvl_1_declarations.patient_id
  111.         WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3493
  112.         GROUP BY mv_stat_lvl_1_declarations.patient_id,
  113.                  mv_stat_lvl_1_patients.birth_date,
  114.                  mv_stat_lvl_1_patients.gender
  115.     ),
  116.     "diagnoses" AS (SELECT "mv_stat_lvl_1_reception_conditions".*
  117.                     FROM "mv_stat_lvl_1_reception_conditions"
  118.                     WHERE "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2023-12-31 23:59:59.999999'
  119.                       AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 3493
  120.     ), "observations" AS (SELECT "mv_stat_lvl_1_ehr_observations".*
  121.                           FROM "mv_stat_lvl_1_ehr_observations"
  122.                           WHERE "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 3493
  123.                             AND "mv_stat_lvl_1_ehr_observations"."code" IN
  124.                                 ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4')
  125.                             AND "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'),
  126.     "observations_cnt" AS (SELECT observations.patient_id,
  127.                                   COUNT(DISTINCT observations.code) AS observations_c1_64_counter,
  128.                                   COUNT(DISTINCT observations.code) filter (WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6')) AS observations_c1_65_counter,
  129.                                   COUNT(DISTINCT observations.code) filter (WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')) AS observations_c2_65_counter
  130.                            FROM observations
  131.                            GROUP BY "observations"."patient_id"
  132.                            HAVING (COUNT(DISTINCT observations.code) > 0)),
  133.     "diagnosis_cnt" AS (SELECT diagnoses.patient_id,
  134.                                COUNT(DISTINCT diagnoses.code) AS diagnoses_c1_64_counter,
  135.                                COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('A98')) AS diagnoses_a98_counter,
  136.                                COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('K86', 'K87')) AS diagnoses_c1_65_counter,
  137.                                COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('K76', 'K74')) AS diagnoses_c2_65_counter,
  138.                                COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('T89', 'T90')) AS diagnoses_c3_65_counter
  139.                         FROM diagnoses
  140.                         GROUP BY "diagnoses"."patient_id"
  141.                         HAVING (COUNT(DISTINCT diagnoses.code) > 0)),
  142.     "reasons_cnt" AS (SELECT mv_stat_lvl_1_ehr_reasons.patient_id,
  143.                              COUNT(DISTINCT mv_stat_lvl_1_ehr_reasons.code) AS a98_counter
  144.                       FROM "mv_stat_lvl_1_ehr_reasons"
  145.                       WHERE "mv_stat_lvl_1_ehr_reasons"."code" = 'A98'
  146.                         AND "mv_stat_lvl_1_ehr_reasons"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  147.                         AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 3493
  148.                       GROUP BY "mv_stat_lvl_1_ehr_reasons"."patient_id"
  149.                       HAVING (COUNT(DISTINCT mv_stat_lvl_1_ehr_reasons.code) > 0)
  150.     ),
  151.     "reports_cnt" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.patient_id,
  152.                              COUNT(DISTINCT mv_stat_lvl_0_ehr_diagnostic_reports.code) AS counter
  153.                       FROM "mv_stat_lvl_0_ehr_diagnostic_reports"
  154.                       WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."code" IN
  155.                             ('T34001', 'T34006', 'T34024', 'T34011', 'T34013')
  156.                         AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 3493
  157.                         AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  158.                       GROUP BY "mv_stat_lvl_0_ehr_diagnostic_reports"."patient_id"
  159.                       HAVING (COUNT(DISTINCT mv_stat_lvl_0_ehr_diagnostic_reports.code) > 0)
  160.     ),
  161.     "actions_cnt" AS (SELECT "mv_stat_lvl_1_ehr_actions".patient_id,
  162.                              COUNT(DISTINCT mv_stat_lvl_1_ehr_actions.code) AS counter
  163.                       FROM "mv_stat_lvl_1_ehr_actions"
  164.                       WHERE "mv_stat_lvl_1_ehr_actions"."code" IN ('K45', 'D45', 'T45')
  165.                         AND "mv_stat_lvl_1_ehr_actions"."legal_entity_id" = 3493
  166.                         AND "mv_stat_lvl_1_ehr_actions"."asserted_date" BETWEEN '2019-01-01 00:00:00' AND '2024-12-31 23:59:59.999999'
  167.                       GROUP BY "mv_stat_lvl_1_ehr_actions"."patient_id"
  168.                       HAVING (COUNT(DISTINCT mv_stat_lvl_1_ehr_actions.code) > 0)
  169.     ),
  170.     groups_counter AS (
  171.         SELECT mv_stat_lvl_1_patients.id                      AS patient_id,
  172.                CASE WHEN (DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64) THEN
  173.                         CASE WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND diagnosis_cnt.diagnoses_c1_64_counter > 0 AND observations_cnt.observations_c1_64_counter > 0 THEN 'done'
  174.                              ELSE 'need_done'
  175.                             END
  176.                     ELSE 'no_need_done'
  177.                END AS visits_from_40_to_64_state,
  178.                CASE WHEN (DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999) THEN
  179.                         CASE WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND ( ((diagnosis_cnt.diagnoses_c1_65_counter > 0 OR diagnosis_cnt.diagnoses_c2_65_counter > 0) AND observations_cnt.observations_c1_65_counter > 0) OR (diagnosis_cnt.diagnoses_c3_65_counter > 0 AND observations_cnt.observations_c2_65_counter > 0)) THEN 'done'
  180.                              ELSE 'need_done'
  181.                             END
  182.                     ELSE 'no_need_done'
  183.                END AS visits_upper_65_state
  184.         FROM "mv_stat_lvl_1_patients"
  185.                  LEFT JOIN observations_cnt ON mv_stat_lvl_1_patients.id = observations_cnt.patient_id AND (observations_cnt.observations_c1_64_counter > 0 OR observations_cnt.observations_c1_65_counter >=4 OR observations_cnt.observations_c2_65_counter >=5 )
  186.                  LEFT JOIN diagnosis_cnt ON mv_stat_lvl_1_patients.id = diagnosis_cnt.patient_id AND  (diagnosis_cnt.diagnoses_c1_64_counter > 0 OR diagnosis_cnt.diagnoses_c1_65_counter > 0 OR diagnosis_cnt.diagnoses_c2_65_counter > 0 OR diagnosis_cnt.diagnoses_c3_65_counter > 0)
  187.                  LEFT JOIN reasons_cnt ON reasons_cnt.patient_id = mv_stat_lvl_1_patients.id
  188.                  LEFT JOIN reports_cnt ON reports_cnt.counter > 0 AND reports_cnt.patient_id = mv_stat_lvl_1_patients.id
  189.                  LEFT JOIN actions_cnt ON actions_cnt.counter > 0 AND actions_cnt.patient_id = mv_stat_lvl_1_patients.id
  190.         WHERE diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0 OR actions_cnt.counter > 0 OR reports_cnt.counter > 0
  191.         GROUP BY mv_stat_lvl_1_patients.id,
  192.                  mv_stat_lvl_1_patients.age,
  193.                  mv_stat_lvl_1_patients.birth_date,
  194.                  visits_from_40_to_64_state,
  195.                  visits_upper_65_state
  196.     )
  197.  
  198. SELECT DISTINCT ON (mv_stat_lvl_1_patients.id)
  199.     mv_stat_lvl_1_patients.id,
  200.     mv_stat_lvl_1_patients.full_name,
  201.     mv_stat_lvl_1_patients.age,
  202.     mv_stat_lvl_1_patients.employee_id,
  203.     mv_stat_lvl_1_patients.gender,
  204.     mv_stat_lvl_1_patients.full_name,
  205.     mv_stat_lvl_1_patients.birth_date,
  206.     mv_stat_lvl_1_patients.legal_entity_id,
  207.     main_counters.hypertension_state,
  208.     main_counters.diabetes_state,
  209.     main_counters.prostate_cancer_state,
  210.     main_counters.breast_cancer_state,
  211.     main_counters.colorectal_cancer_state,
  212.     main_counters.hiv_state,
  213.     CASE WHEN groups_counter.visits_from_40_to_64_state IS NULL THEN
  214.         CASE WHEN DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64 THEN 'need_done'
  215.             ELSE 'no_need_done'
  216.         END
  217.     ELSE groups_counter.visits_from_40_to_64_state
  218.     END visits_from_40_to_64_state,
  219.     DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) as current_age,
  220.     CASE WHEN groups_counter.visits_upper_65_state IS NULL THEN
  221.              CASE WHEN DATE_PART('YEAR', AGE('2024-12-31 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999 THEN 'need_done'
  222.                   ELSE 'no_need_done'
  223.                  END
  224.          ELSE groups_counter.visits_upper_65_state
  225.         END visits_upper_65_state
  226.  
  227. FROM mv_stat_lvl_1_patients
  228.          LEFT JOIN main_counters ON mv_stat_lvl_1_patients.id = main_counters.patient_id
  229.          LEFT JOIN groups_counter ON mv_stat_lvl_1_patients.id = groups_counter.patient_id
  230. WHERE ("main_counters"."hypertension_state" IN ('done', 'need_done', 'no_need_done')
  231.     OR "main_counters"."diabetes_state" IN ('done', 'need_done', 'no_need_done')
  232.     OR "main_counters"."prostate_cancer_state" IN ('done', 'need_done', 'no_need_done')
  233.     OR "main_counters"."breast_cancer_state" IN ('done', 'need_done', 'no_need_done')
  234.     OR "main_counters"."colorectal_cancer_state" IN ('done', 'need_done', 'no_need_done')
  235.     OR "groups_counter"."visits_from_40_to_64_state" IN ('done', 'need_done', 'no_need_done')
  236.     OR "groups_counter"."visits_upper_65_state" IN ('done', 'need_done', 'no_need_done')
  237.           )
  238. LIMIT 1000 OFFSET 0
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!