API tools faq
paste
Advertisement
temaon_lieto

SQP_TOP_1.txt

temaon_lieto
Jun 30th, 2024
233
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 12.67 KB | None | 0 0
raw download clone embed print report
  1. SELECT * FROM (WITH "items" AS (SELECT mv_stat_lvl_2_joined_items.id, mv_stat_lvl_2_joined_items.asserted_date, mv_stat_lvl_2_joined_items.code, mv_stat_lvl_2_joined_items.item_type, mv_stat_lvl_2_joined_items.status::varchar, mv_stat_lvl_2_joined_items.title, mv_stat_lvl_2_joined_items.employee_id, mv_stat_lvl_2_joined_items.legal_entity_id, mv_stat_lvl_2_joined_items.patient_id FROM "mv_stat_lvl_2_joined_items" WHERE "mv_stat_lvl_2_joined_items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_2_joined_items"."legal_entity_id" = 8225), "main_counters" AS (SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) AS counter,
  2.                mv_stat_lvl_1_declarations.patient_id                      AS patient_id,
  3.                CASE WHEN (("mv_stat_lvl_1_patients"."gender" = 1 AND DATE_PART('YEAR', AGE('2024-06-26 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-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 999)) THEN
  4.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004'))) > 0 THEN 'done'
  5.                              ELSE 'need_done'
  6. END
  7. ELSE
  8.                         'no_need_done'
  9. END hypertension_state,
  10.                CASE WHEN (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 45 AND 999) THEN
  11.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('T34005', 'T34023', 'T34025', 'T34038', '14743-9'))) > 0 THEN 'done'
  12.                              ELSE 'need_done'
  13. END
  14. ELSE
  15.                         'no_need_done'
  16. END AS diabetes_state,
  17.                CASE WHEN ("mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 999)) THEN
  18.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('U67002', 'Y34011', 'Y34003'))) > 0 THEN 'done'
  19.                              ELSE 'need_done'
  20. END
  21. ELSE
  22.                         'no_need_done'
  23. END AS prostate_cancer_state,
  24.                CASE WHEN ("mv_stat_lvl_1_patients"."gender" = 1 AND (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 69)) THEN
  25.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('Х41941', 'X41973', '59300-00', '55070-00', '55076-00'))) > 0 THEN 'done'
  26.                              ELSE 'need_done'
  27. END
  28. ELSE
  29.                         'no_need_done'
  30. END AS breast_cancer_state,
  31.                CASE WHEN (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 75) THEN
  32.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02'))) > 0 THEN 'done'
  33.                              ELSE 'need_done'
  34. END
  35. ELSE
  36.                         'no_need_done'
  37. END AS colorectal_cancer_state,
  38.                CASE WHEN (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 14 AND 999) THEN
  39.                         CASE WHEN (COUNT(DISTINCT items.code) filter (WHERE "items"."code" IN ('B33006', 'B33012'))) > 0 THEN 'done'
  40.                              ELSE 'need_done'
  41. END
  42. ELSE
  43.                         'no_need_done'
  44. END AS hiv_state FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id
  45. LEFT JOIN items ON items.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND items.patient_id = mv_stat_lvl_1_declarations.patient_id WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 8225 GROUP BY mv_stat_lvl_1_declarations.patient_id, mv_stat_lvl_1_patients.birth_date, mv_stat_lvl_1_patients.gender), "diagnoses" AS (SELECT mv_stat_lvl_1_reception_conditions.patient_id, mv_stat_lvl_1_reception_conditions.code FROM "mv_stat_lvl_1_reception_conditions" WHERE "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 8225), "observations" AS (SELECT mv_stat_lvl_1_ehr_observations.patient_id, mv_stat_lvl_1_ehr_observations.code FROM "mv_stat_lvl_1_ehr_observations" WHERE "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 8225 AND "mv_stat_lvl_1_ehr_observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4', '14647-2')), "diagnosis_cnt" AS (SELECT diagnoses.patient_id,
  46. COUNT(DISTINCT diagnoses.code) AS diagnoses_c1_64_counter,
  47. COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('A98')) AS diagnoses_a98_counter,
  48. COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('K86', 'K87')) AS diagnoses_c1_65_counter,
  49. COUNT(DISTINCT diagnoses.code) filter (WHERE "diagnoses"."code" IN ('K76', 'K74')) AS diagnoses_c2_65_counter,
  50. COUNT(DISTINCT diagnoses.code) filter (WHERE "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,
  51. COUNT(DISTINCT observations.code) filter (WHERE "observations"."code" IN ('14647-2')) AS cholesterol_obs_counter,
  52. COUNT(DISTINCT observations.code) filter (WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')) AS observations_c1_64_counter,
  53. COUNT(DISTINCT observations.code) filter (WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')) AS observations_c1_65_counter,
  54. COUNT(DISTINCT observations.code) filter (WHERE "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 mv_stat_lvl_1_ehr_reasons.patient_id, COUNT(DISTINCT mv_stat_lvl_1_ehr_reasons.code) AS a98_counter FROM "mv_stat_lvl_1_ehr_reasons" WHERE "mv_stat_lvl_1_ehr_reasons"."code" = 'A98' AND "mv_stat_lvl_1_ehr_reasons"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 8225 GROUP BY "mv_stat_lvl_1_ehr_reasons"."patient_id" HAVING (COUNT(DISTINCT mv_stat_lvl_1_ehr_reasons.code) > 0)), "reports_cnt" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.patient_id, COUNT(DISTINCT mv_stat_lvl_0_ehr_diagnostic_reports.code) AS counter FROM "mv_stat_lvl_0_ehr_diagnostic_reports" WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."code" IN ('T34001', 'T34006', 'T34024', 'T34011', 'T34013') AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 8225 GROUP BY "mv_stat_lvl_0_ehr_diagnostic_reports"."patient_id" HAVING (COUNT(DISTINCT mv_stat_lvl_0_ehr_diagnostic_reports.code) > 0)), "actions_cnt" AS (SELECT mv_stat_lvl_1_ehr_actions.patient_id, COUNT(DISTINCT mv_stat_lvl_1_ehr_actions.code) AS counter FROM "mv_stat_lvl_1_ehr_actions" WHERE "mv_stat_lvl_1_ehr_actions"."code" IN ('K45', 'D45', 'T45') AND "mv_stat_lvl_1_ehr_actions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-06-26 23:59:59.999999' AND "mv_stat_lvl_1_ehr_actions"."legal_entity_id" = 8225 GROUP BY "mv_stat_lvl_1_ehr_actions"."patient_id" HAVING (COUNT(DISTINCT mv_stat_lvl_1_ehr_actions.code) > 0)), "groups_counter" AS (SELECT mv_stat_lvl_1_patients.id AS patient_id,
  55. CASE WHEN (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64) THEN
  56.         CASE WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND ((reports_cnt.counter > 0 OR observations_cnt.cholesterol_obs_counter > 0) AND observations_cnt.observations_c1_64_counter > 0) THEN 'done'
  57.              ELSE 'need_done'
  58. END
  59. ELSE 'no_need_done'
  60. END AS visits_from_40_to_64_state,
  61. CASE WHEN (DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999) THEN
  62.         CASE WHEN (diagnosis_cnt.diagnoses_a98_counter > 0 OR reasons_cnt.a98_counter > 0) AND ((observations_cnt.cholesterol_obs_counter > 0 OR reports_cnt.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'
  63.              WHEN (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'
  64.              ELSE 'no_need_done'
  65. END
  66. ELSE 'no_need_done'
  67. END AS visits_upper_65_state FROM "mv_stat_lvl_1_patients" LEFT JOIN observations_cnt ON mv_stat_lvl_1_patients.id = observations_cnt.patient_id AND (observations_cnt.cholesterol_obs_counter > 0 OR observations_cnt.observations_c1_64_counter >=5 OR observations_cnt.observations_c1_65_counter >=5 OR observations_cnt.observations_c2_65_counter >=6 )
  68. 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)
  69. LEFT JOIN reasons_cnt ON reasons_cnt.patient_id = mv_stat_lvl_1_patients.id
  70. LEFT JOIN reports_cnt ON reports_cnt.patient_id = mv_stat_lvl_1_patients.id
  71. LEFT JOIN actions_cnt ON actions_cnt.patient_id = mv_stat_lvl_1_patients.id WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 8225 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 mv_stat_lvl_1_patients.id, mv_stat_lvl_1_patients.age, mv_stat_lvl_1_patients.birth_date, visits_from_40_to_64_state, visits_upper_65_state) SELECT DISTINCT mv_stat_lvl_1_patients.id,
  72.     mv_stat_lvl_1_patients.full_name,
  73.     mv_stat_lvl_1_patients.age,
  74.     mv_stat_lvl_1_patients.employee_id,
  75.     mv_stat_lvl_1_patients.gender,
  76.     mv_stat_lvl_1_patients.full_name,
  77.     mv_stat_lvl_1_patients.birth_date,
  78.     mv_stat_lvl_1_patients.legal_entity_id,
  79.     main_counters.hypertension_state,
  80.     main_counters.diabetes_state,
  81.     main_counters.prostate_cancer_state,
  82.     main_counters.breast_cancer_state,
  83.     main_counters.colorectal_cancer_state,
  84.     main_counters.hiv_state,
  85.     CASE WHEN groups_counter.visits_from_40_to_64_state IS NULL THEN
  86.              CASE WHEN DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64 THEN 'need_done'
  87.                   ELSE 'no_need_done'
  88. END
  89. ELSE groups_counter.visits_from_40_to_64_state
  90. END visits_from_40_to_64_state,
  91.     DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) as current_age,
  92.     CASE WHEN groups_counter.visits_upper_65_state IS NULL THEN
  93.              CASE WHEN DATE_PART('YEAR', AGE('2024-06-26 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) 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'
  94.                   ELSE 'no_need_done'
  95. END
  96. ELSE groups_counter.visits_upper_65_state
  97. END visits_upper_65_state FROM "mv_stat_lvl_1_patients" LEFT JOIN main_counters ON mv_stat_lvl_1_patients.id = main_counters.patient_id
  98. LEFT JOIN groups_counter ON mv_stat_lvl_1_patients.id = groups_counter.patient_id
  99. LEFT JOIN diagnosis_cnt ON mv_stat_lvl_1_patients.id = diagnosis_cnt.patient_id WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 8225 AND "mv_stat_lvl_1_patients"."employee_id" = 190173 ORDER BY mv_stat_lvl_1_patients.full_name ASC) mv_stat_lvl_1_patients WHERE ("mv_stat_lvl_1_patients"."hypertension_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."diabetes_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."prostate_cancer_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."breast_cancer_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."colorectal_cancer_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."visits_from_40_to_64_state" IN ('need_done', 'no_need_done') OR "mv_stat_lvl_1_patients"."visits_upper_65_state" IN ('need_done', 'no_need_done')) LIMIT 15 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!