Patients SQL

WITH "mv_stat_lvl_0_employees" AS (SELECT "mv_stat_lvl_0_employees".* FROM "mv_stat_lvl_0_employees" WHERE "mv_stat_lvl_0_employees"."legal_entity_id" = 3228 AND "mv_stat_lvl_0_employees"."employee_type_ref_value" = 'DOCTOR'), "items" AS (SELECT "mv_stat_lvl_0_ehr_service_requests".* FROM ( (SELECT "mv_stat_lvl_0_ehr_service_requests".* FROM ( (SELECT mv_stat_lvl_0_ehr_service_requests.id, mv_stat_lvl_0_ehr_service_requests.asserted_date, mv_stat_lvl_0_ehr_service_requests.code, mv_stat_lvl_0_ehr_service_requests.item_type, mv_stat_lvl_0_ehr_service_requests.status::varchar, mv_stat_lvl_0_ehr_service_requests.title, mv_stat_lvl_0_ehr_service_requests.employee_id, mv_stat_lvl_0_ehr_service_requests.legal_entity_id, mv_stat_lvl_0_ehr_service_requests.patient_id FROM "mv_stat_lvl_0_ehr_service_requests" WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 3228) UNION (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id, mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date, mv_stat_lvl_0_ehr_diagnostic_reports.code, mv_stat_lvl_0_ehr_diagnostic_reports.item_type, mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar, mv_stat_lvl_0_ehr_diagnostic_reports.title, mv_stat_lvl_0_ehr_diagnostic_reports.employee_id, mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id, mv_stat_lvl_0_ehr_diagnostic_reports.patient_id FROM "mv_stat_lvl_0_ehr_diagnostic_reports" WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 3228) ) "mv_stat_lvl_0_ehr_service_requests") UNION (SELECT mv_stat_lvl_1_ehr_observations.id, mv_stat_lvl_1_ehr_observations.asserted_date, mv_stat_lvl_1_ehr_observations.code, 1 as item_type, 'final'::varchar as status, mv_stat_lvl_1_ehr_observations.title, mv_stat_lvl_1_ehr_observations.employee_id, mv_stat_lvl_1_ehr_observations.legal_entity_id, mv_stat_lvl_1_ehr_observations.patient_id 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-03-06 23:59:59.999999' AND "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 3228) ) "mv_stat_lvl_0_ehr_service_requests"), "hypertension_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004') AND ("mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 999) OR "mv_stat_lvl_1_patients"."gender" = 1 AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 999)) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "diabetes_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('T34005', 'T34023', 'T34025', 'T34038', '14743-9') AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 45 AND 999) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "prostate_cancer_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('U67002', 'Y34011', 'Y34003') AND "mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 999) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "breast_cancer_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('Х41941', 'X41973', '59300-00', '55070-00', '55076-00') AND "mv_stat_lvl_1_patients"."gender" = 1 AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 59 AND 69) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "colorectal_cancer_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02') AND "mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 75) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "hiv_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id 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" = 3228 AND "items"."code" IN ('B33006', 'B33012') AND (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 14 AND 999) AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "visits_from_40_to_64_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228), "selected_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64) AND "mv_stat_lvl_1_patients"."legal_entity_id" = 3228), "diagnoses" AS (SELECT "mv_stat_lvl_1_reception_conditions".* 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-03-06 23:59:59.999999' AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 3228), "reasons" AS (SELECT "mv_stat_lvl_1_ehr_reasons".* 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-03-06 23:59:59.999999' AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 3228), "diagnoses_c1_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter FROM diagnoses GROUP BY "diagnoses"."patient_id" HAVING (COUNT(DISTINCT diagnoses.code) > 0)), "observations" AS (SELECT "mv_stat_lvl_1_ehr_observations".* FROM "mv_stat_lvl_1_ehr_observations" WHERE "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 3228 AND "mv_stat_lvl_1_ehr_observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4') AND "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999'), "reports" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id, mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date, mv_stat_lvl_0_ehr_diagnostic_reports.code, mv_stat_lvl_0_ehr_diagnostic_reports.item_type, mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar, mv_stat_lvl_0_ehr_diagnostic_reports.title, mv_stat_lvl_0_ehr_diagnostic_reports.employee_id, mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id, mv_stat_lvl_0_ehr_diagnostic_reports.patient_id 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"."legal_entity_id" = 3228 AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999'), "observations_c1_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter FROM observations WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2') GROUP BY "observations"."patient_id" HAVING (COUNT(DISTINCT observations.code) >= 4)) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id LEFT JOIN diagnoses ON mv_stat_lvl_1_declarations.patient_id = diagnoses.patient_id AND diagnoses.code = 'A98' JOIN diagnoses_c1_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c1_counter.patient_id AND diagnoses_c1_counter.counter > 0 LEFT JOIN reasons ON reasons.patient_id = mv_stat_lvl_1_declarations.patient_id JOIN reports ON reports.patient_id = mv_stat_lvl_1_declarations.patient_id LEFT JOIN observations_c1_counter ON observations_c1_counter.patient_id = mv_stat_lvl_1_declarations.patient_id WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 3228 AND ((diagnoses.code = 'A98' OR reasons.code = 'A98') AND (diagnoses_c1_counter.counter > 0 AND observations_c1_counter.counter > 0)) GROUP BY "mv_stat_lvl_1_declarations"."patient_id"), "visits_upper_65_cnt" AS (WITH "mv_stat_lvl_1_patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228), "patients" AS (SELECT "mv_stat_lvl_1_patients".* FROM "mv_stat_lvl_1_patients" WHERE (DATE_PART('YEAR', AGE('2024-03-06 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999) AND "mv_stat_lvl_1_patients"."legal_entity_id" = 3228), "diagnoses" AS (SELECT "mv_stat_lvl_1_reception_conditions".* FROM "mv_stat_lvl_1_reception_conditions" WHERE "mv_stat_lvl_1_reception_conditions"."code" IN ('K86', 'K87', 'K76', 'K74', 'T89', 'T90', 'A98') AND "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999' AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 3228), "reasons" AS (SELECT "mv_stat_lvl_1_ehr_reasons".* 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-03-06 23:59:59.999999' AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 3228), "observations" AS (SELECT "mv_stat_lvl_1_ehr_observations".* FROM "mv_stat_lvl_1_ehr_observations" WHERE "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 3228 AND "mv_stat_lvl_1_ehr_observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4') AND "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999'), "actions" AS (SELECT "mv_stat_lvl_1_ehr_actions".* 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"."legal_entity_id" = 3228 AND "mv_stat_lvl_1_ehr_actions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999'), "reports" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id, mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date, mv_stat_lvl_0_ehr_diagnostic_reports.code, mv_stat_lvl_0_ehr_diagnostic_reports.item_type, mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar, mv_stat_lvl_0_ehr_diagnostic_reports.title, mv_stat_lvl_0_ehr_diagnostic_reports.employee_id, mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id, mv_stat_lvl_0_ehr_diagnostic_reports.patient_id 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"."legal_entity_id" = 3228 AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-06 23:59:59.999999'), "diagnoses_c1_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter FROM diagnoses WHERE "diagnoses"."code" IN ('K86', 'K87') GROUP BY "diagnoses"."patient_id" HAVING (COUNT(DISTINCT diagnoses.code) > 0)), "diagnoses_c2_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter FROM diagnoses WHERE "diagnoses"."code" IN ('K76', 'K74') GROUP BY "diagnoses"."patient_id" HAVING (COUNT(DISTINCT diagnoses.code) > 0)), "diagnoses_c3_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter FROM diagnoses WHERE "diagnoses"."code" IN ('T89', 'T90') GROUP BY "diagnoses"."patient_id" HAVING (COUNT(DISTINCT diagnoses.code) > 0)), "observations_c1_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter FROM observations WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6') GROUP BY "observations"."patient_id" HAVING (COUNT(DISTINCT observations.code) >= 4)), "observations_c2_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter FROM observations WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2') GROUP BY "observations"."patient_id" HAVING (COUNT(DISTINCT observations.code) >= 5)) SELECT COALESCE(COUNT(DISTINCT mv_stat_lvl_1_declarations.id), 0) as counter, mv_stat_lvl_1_declarations.patient_id as patient_id FROM "mv_stat_lvl_1_declarations" JOIN mv_stat_lvl_1_patients ON mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id INNER JOIN diagnoses ON mv_stat_lvl_1_declarations.patient_id = diagnoses.patient_id LEFT JOIN diagnoses_c1_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c1_counter.patient_id LEFT JOIN diagnoses_c2_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c2_counter.patient_id LEFT JOIN diagnoses_c3_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c3_counter.patient_id LEFT JOIN reasons ON reasons.patient_id = mv_stat_lvl_1_declarations.patient_id JOIN actions ON actions.patient_id = mv_stat_lvl_1_declarations.patient_id JOIN reports ON reports.patient_id = mv_stat_lvl_1_declarations.patient_id LEFT JOIN observations_c1_counter ON observations_c1_counter.patient_id = mv_stat_lvl_1_declarations.patient_id LEFT JOIN observations_c2_counter ON observations_c2_counter.patient_id = mv_stat_lvl_1_declarations.patient_id WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 3228 AND ((diagnoses.code = 'A98' OR reasons.code = 'A98') AND ( ((diagnoses_c1_counter.counter > 0 OR diagnoses_c2_counter.counter > 0) AND observations_c1_counter.counter > 0) OR (diagnoses_c3_counter.counter > 0 AND observations_c2_counter.counter > 0) )) GROUP BY "mv_stat_lvl_1_declarations"."patient_id") SELECT DISTINCT COALESCE(MAX(hypertension_cnt.counter),0) as hypertension_counter, COALESCE(MAX(diabetes_cnt.counter),0) as diabetes_counter, COALESCE(MAX(prostate_cancer_cnt.counter),0) as prostate_cancer_counter, COALESCE(MAX(breast_cancer_cnt.counter),0) as breast_cancer_counter, COALESCE(MAX(colorectal_cancer_cnt.counter),0) as colorectal_cancer_counter, COALESCE(MAX(hiv_cnt.counter),0) as hiv_counter, COALESCE(MAX(visits_from_40_to_64_cnt.counter),0) as visits_from_40_to_64_counter, COALESCE(MAX(visits_upper_65_cnt.counter),0) as visits_upper_65_counter, mv_stat_lvl_1_patients.age, mv_stat_lvl_1_patients.employee_id, mv_stat_lvl_1_patients.id, mv_stat_lvl_1_patients.gender, mv_stat_lvl_1_patients.full_name, mv_stat_lvl_1_patients.birth_date, mv_stat_lvl_1_patients.legal_entity_id FROM "mv_stat_lvl_1_patients" LEFT JOIN hypertension_cnt ON mv_stat_lvl_1_patients.id = hypertension_cnt.patient_id LEFT JOIN diabetes_cnt ON mv_stat_lvl_1_patients.id = diabetes_cnt.patient_id LEFT JOIN prostate_cancer_cnt ON mv_stat_lvl_1_patients.id = prostate_cancer_cnt.patient_id LEFT JOIN breast_cancer_cnt ON mv_stat_lvl_1_patients.id = breast_cancer_cnt.patient_id LEFT JOIN colorectal_cancer_cnt ON mv_stat_lvl_1_patients.id = colorectal_cancer_cnt.patient_id LEFT JOIN hiv_cnt ON mv_stat_lvl_1_patients.id = hiv_cnt.patient_id LEFT JOIN visits_from_40_to_64_cnt ON mv_stat_lvl_1_patients.id = visits_from_40_to_64_cnt.patient_id LEFT JOIN visits_upper_65_cnt ON mv_stat_lvl_1_patients.id = visits_from_40_to_64_cnt.patient_id WHERE "mv_stat_lvl_1_patients"."legal_entity_id" = 3228 GROUP BY mv_stat_lvl_1_patients.id, mv_stat_lvl_1_patients.age, mv_stat_lvl_1_patients.gender, mv_stat_lvl_1_patients.legal_entity_id, mv_stat_lvl_1_patients.birth_date, mv_stat_lvl_1_patients.full_name, mv_stat_lvl_1_patients.employee_id LIMIT 15 OFFSET 0

SELECT "mv_stat_lvl_0_employees".* FROM "mv_stat_lvl_0_employees" WHERE "mv_stat_lvl_0_employees"."id" IN (180499, 181286)

 SELECT "mv_stat_lvl_1_reception_conditions".* 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-03-06 23:59:59.999999' AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 3228 AND "mv_stat_lvl_1_reception_conditions"."code" IN (K86, K87, K76, K74, T89, T90, A98) AND "mv_stat_lvl_1_reception_conditions"."patient_id" IN (11395824, 11395822, 11464974, 11464994, 11464994, 11465106, 11464976)

SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id, mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date, mv_stat_lvl_0_ehr_diagnostic_reports.code, mv_stat_lvl_0_ehr_diagnostic_reports.item_type, mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar, mv_stat_lvl_0_ehr_diagnostic_reports.title, mv_stat_lvl_0_ehr_diagnostic_reports.employee_id, mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id, mv_stat_lvl_0_ehr_diagnostic_reports.patient_id FROM "mv_stat_lvl_0_ehr_diagnostic_reports" WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN $1 AND $2 AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 3228 AND "mv_stat_lvl_0_ehr_diagnostic_reports"."code" IN ('T34006', 'T34011', 'T34013', 'T34024', 'T34001', 'T34004', 'T34005', 'T34023', 'T34025', 'T34038', 'U67002', 'Y34011', 'Y34003', 'ТХ41973', '59300-00', '55070-00', '55076-00', 'Х41941', 'D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02', 'B33006', 'B33012') AND "mv_stat_lvl_0_ehr_diagnostic_reports"."patient_id" IN (11395824, 11395822, 11464974, 11464994, 11464994, 11465106, 11464976)

SELECT "mv_stat_lvl_1_ehr_observations".* FROM "mv_stat_lvl_1_ehr_observations" WHERE "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN $1 AND $2 AND "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = $3 AND "mv_stat_lvl_1_ehr_observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4', '14647-2') AND "mv_stat_lvl_1_ehr_observations"."patient_id" IN ($10, $11, $12, $13, $14, $15, $16)