test - Pastebin.com

WITH reporting_periods AS (SELECT rp.*,

toDate32('2025-05-01') AS period_end_date,

IF(original_period_type = 'DAY',

dateDiff('day', period_end_date - INTERVAL original_period_value DAY,

period_end_date),

dateDiff('day', period_end_date - INTERVAL original_period_value YEAR,

period_end_date)

) AS calculated_period_start_days,

(period_end_date - INTERVAL calculated_period_start_days DAY) AS period_start_date

FROM dict_reporting_periods rp),

period_interval AS (SELECT MIN(reporting_periods.period_start_date) AS period_start,

MAX(reporting_periods.period_end_date) AS period_end

FROM reporting_periods),

patients AS (SELECT DISTINCT ON (id) IF(declaration_status = 'ACTIVE', declaration_end_date,

declaration_updated_at) AS declaration_end_date,

period_interval.period_start,

period_interval.period_end,

declaration_start_date,

age('day', date_of_birth, period_interval.period_end) AS current_age_in_days,

age('year', date_of_birth, period_interval.period_end) AS current_age_in_years,

cascade_lvl02_patient_declarations.id,

cascade_lvl02_patient_declarations.gender,

cascade_lvl02_patient_declarations.full_address,

cascade_lvl02_patient_declarations.patient_full_name,

cascade_lvl02_patient_declarations.legal_entity_id,

cascade_lvl02_patient_declarations.employee_id,

cascade_lvl02_patient_declarations.doctor_full_name,

cascade_lvl02_patient_declarations.doctor_position,

cascade_lvl02_patient_declarations.division_id

FROM cascade_lvl02_patient_declarations,

period_interval

WHERE cascade_lvl02_patient_declarations.legal_entity_id = 3228

AND (declaration_start_date <= toDate32('2025-05-14 23:59:59') AND

declaration_end_date >= toDate32('2020-01-01 00:00:00'))),

joined_resources AS (SELECT cascade_lvl01_progress_diseases_services.id,

cascade_lvl01_progress_diseases_services.code,

cascade_lvl01_progress_diseases_services.employee_id,

cascade_lvl01_progress_diseases_services.legal_entity_id,

cascade_lvl01_progress_diseases_services.asserted_date,

cascade_lvl01_progress_diseases_services.encounter_id,

cascade_lvl01_progress_diseases_services.patient_id,

cascade_lvl01_progress_diseases_services.STATUS,

cascade_lvl01_progress_diseases_services.resource_type

FROM cascade_lvl01_progress_diseases_services,

period_interval

WHERE cascade_lvl01_progress_diseases_services.legal_entity_id = 3228

AND cascade_lvl01_progress_diseases_services.resource_type IN

('observation', 'diagnostic_report', 'service_request')

AND (asserted_date >= period_interval.period_start AND

asserted_date <= period_interval.period_end)),

progress_disease_codes AS (SELECT cascade_lvl00_progress_disease_codes.id,

cascade_lvl00_progress_disease_codes.code,

cascade_lvl00_progress_disease_codes.employee_id,

cascade_lvl00_progress_disease_codes.legal_entity_id,

cascade_lvl00_progress_disease_codes.asserted_date,

cascade_lvl00_progress_disease_codes.encounter_id,

cascade_lvl00_progress_disease_codes.patient_id,

cascade_lvl00_progress_disease_codes.ROLE,

cascade_lvl00_progress_disease_codes.code_type

FROM cascade_lvl00_progress_disease_codes,

period_interval

WHERE cascade_lvl00_progress_disease_codes.legal_entity_id = 3228

AND cascade_lvl00_progress_disease_codes.ehealth_status = 'finished'

AND (asserted_date <= period_interval.period_end)),

actions AS (SELECT cascade_lvl00_actions.id,

cascade_lvl00_actions.code,

cascade_lvl00_actions.patient_id,

cascade_lvl00_actions.asserted_date

FROM cascade_lvl00_actions,

period_interval

WHERE cascade_lvl00_actions.legal_entity_id = 3228

AND cascade_lvl00_actions.code IN ('K45', 'D45', 'T45')

AND (asserted_date >= period_interval.period_start AND asserted_date <= period_interval.period_end)),

joined_groups AS (SELECT cascade_lvl00_progress_disease_groups.*,

reporting_periods.original_period_type,

reporting_periods.original_period_value,

reporting_periods.calculated_period_start_days,

reporting_periods.period_start_date,

reporting_periods.period_end_date

FROM cascade_lvl00_progress_disease_groups

JOIN reporting_periods ON cascade_lvl00_progress_disease_groups.date_restriction =

reporting_periods.period_name),

observations AS (SELECT joined_resources.code AS code,

joined_resources.legal_entity_id AS legal_entity_id,

joined_resources.asserted_date AS asserted_date,

joined_resources.patient_id AS patient_id,

joined_resources.STATUS AS STATUS,

joined_resources.resource_type AS resource_type

FROM joined_resources

WHERE joined_resources.resource_type = 'observation'

AND joined_resources.STATUS = 'valid'),

diagnostic_reports AS (SELECT joined_resources.code AS code,

joined_resources.legal_entity_id AS legal_entity_id,

joined_resources.asserted_date AS asserted_date,

joined_resources.patient_id AS patient_id,

joined_resources.STATUS AS STATUS,

joined_resources.resource_type AS resource_type

FROM joined_resources

WHERE joined_resources.resource_type = 'diagnostic_report'

AND joined_resources.STATUS = 'final'),

service_requests AS (SELECT joined_resources.code AS code,

joined_resources.legal_entity_id AS legal_entity_id,

joined_resources.asserted_date AS asserted_date,

joined_resources.patient_id AS patient_id,

joined_resources.STATUS AS STATUS,

joined_resources.resource_type AS resource_type

FROM joined_resources

WHERE joined_resources.resource_type = 'service_request'

AND joined_resources.STATUS IN ('active', 'completed')),

patient_joined_groups AS (SELECT patients.patient_full_name,

patients.current_age_in_years,

patients.id AS patient_id,

joined_groups.*,

patients.gender AS patient_gender

FROM patients,

joined_groups),

patient_joined_groups_with_services AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

jrsr.*

FROM patient_joined_groups

JOIN service_requests jrsr

ON patient_joined_groups.need_to_check_service_request_codes =

TRUE AND

jrsr.patient_id = patient_joined_groups.patient_id AND

jrsr.asserted_date >=

patient_joined_groups.period_start_date AND

jrsr.asserted_date <=

patient_joined_groups.period_end_date AND

(patient_joined_groups.need_to_check_service_statuses AND

has(patient_joined_groups.service_statuses, jrsr.STATUS))

WHERE (patient_joined_groups.need_to_check_service_request_codes = TRUE AND

arrayExists(x -> x = jrsr.code,

patient_joined_groups.condition_service_request_codes)) SETTINGS allow_experimental_join_condition = 1),

patient_joined_groups_with_dos AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

jrdo.*

FROM patient_joined_groups

JOIN diagnostic_reports jrdo

ON (patient_joined_groups.need_to_check_diagnostic_report_codes =

TRUE OR

patient_joined_groups.need_to_check_optional_observation_codes =

TRUE) AND

jrdo.patient_id = patient_joined_groups.patient_id AND

has(patient_joined_groups.condition_diagnostic_report_codes,

jrdo.code) AND jrdo.asserted_date >=

patient_joined_groups.period_start_date AND

jrdo.asserted_date <= patient_joined_groups.period_end_date

WHERE (patient_joined_groups.need_to_check_diagnostic_report_codes = TRUE AND

arrayExists(x -> x = jrdo.code,

patient_joined_groups.condition_diagnostic_report_codes) AND

(NOT patient_joined_groups.need_to_check_optional_observation_codes = TRUE OR

jrdo.asserted_date <

toDate('2024-06-01'))) SETTINGS allow_experimental_join_condition = 1),

patient_joined_groups_with_observations AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

jro.*

FROM patient_joined_groups

JOIN observations jro

ON (patient_joined_groups.need_to_check_observation_codes =

TRUE OR

patient_joined_groups.need_to_check_optional_observation_codes =

TRUE) AND

jro.patient_id = patient_joined_groups.patient_id AND

jro.asserted_date >=

patient_joined_groups.period_start_date AND

jro.asserted_date <=

patient_joined_groups.period_end_date AND

(has(patient_joined_groups.condition_observation_codes, jro.code) OR

has(

patient_joined_groups.condition_optional_observation_codes,

jro.code))

WHERE ((patient_joined_groups.need_to_check_observation_codes AND

arrayExists(x -> x = jro.code,

patient_joined_groups.condition_observation_codes)) OR

(patient_joined_groups.need_to_check_optional_observation_codes AND

arrayExists(x -> x = jro.code,

patient_joined_groups.condition_optional_observation_codes))) SETTINGS allow_experimental_join_condition = 1),

patient_joined_groups_with_reasons AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

pdrc.*

FROM patient_joined_groups

JOIN progress_disease_codes pdrc

ON patient_joined_groups.need_to_check_reason_code = TRUE AND

pdrc.patient_id = patient_joined_groups.patient_id AND

has(patient_joined_groups.condition_reason_code, pdrc.code) AND

pdrc.asserted_date >=

patient_joined_groups.period_start_date AND

pdrc.asserted_date <= patient_joined_groups.period_end_date

WHERE (patient_joined_groups.need_to_check_reason_code AND

arrayExists(x -> x = pdrc.code,

patient_joined_groups.condition_reason_code)) SETTINGS allow_experimental_join_condition = 1),

patient_joined_groups_with_actions AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

a.*

FROM patient_joined_groups

JOIN actions a

ON patient_joined_groups.need_to_check_condition_action_codes =

TRUE AND

a.patient_id = patient_joined_groups.patient_id AND

a.asserted_date >=

patient_joined_groups.period_start_date AND

a.asserted_date <=

patient_joined_groups.period_end_date AND

has(patient_joined_groups.condition_action_codes, a.code)

WHERE (patient_joined_groups.need_to_check_condition_action_codes = TRUE AND

arrayExists(x -> x = a.code,

patient_joined_groups.condition_action_codes)) SETTINGS allow_experimental_join_condition = 1),

patient_joined_groups_with_pdc AS (SELECT patient_joined_groups.patient_id,

patient_joined_groups.indicator_name,

patient_joined_groups.group_name,

pdc.*

FROM patient_joined_groups

JOIN progress_disease_codes pdc

ON (patient_joined_groups.need_to_check_condition_codes = TRUE OR

patient_joined_groups.need_to_check_condition_exclude_codes =

TRUE) AND

pdc.patient_id = patient_joined_groups.patient_id AND

pdc.asserted_date <= patient_joined_groups.period_end_date AND

(has(patient_joined_groups.condition_codes, pdc.code) OR

has(patient_joined_groups.condition_exclude_codes, pdc.code))

WHERE (pdc.asserted_date <= patient_joined_groups.period_end_date AND

((patient_joined_groups.need_to_check_condition_codes = TRUE AND

arrayExists(x -> x = pdc.code,

patient_joined_groups.condition_codes)) OR

(patient_joined_groups.need_to_check_condition_exclude_codes = TRUE AND

arrayExists(x -> x = pdc.code,

patient_joined_groups.condition_exclude_codes)))) SETTINGS allow_experimental_join_condition = 1),

patient_groups AS (SELECT first_value(patient_joined_groups.patient_full_name),

first_value(patient_joined_groups.current_age_in_years),

patient_joined_groups.patient_id AS patient_id,

patient_joined_groups.indicator_name AS indicator_name,

patient_joined_groups.group_name AS group_name,

groupUniqArray(patient_joined_groups_with_dos.code) AS result_condition_diagnostic_report_codes,

groupUniqArray(patient_joined_groups_with_observations.code) AS result_condition_observation_codes,

groupUniqArray(patient_joined_groups_with_services.code) AS result_condition_service_request_codes,

groupUniqArray(patient_joined_groups_with_reasons.code) AS result_reason_codes,

groupUniqArray(patient_joined_groups_with_actions.code) AS result_action_codes,

groupUniqArray(patient_joined_groups_with_pdc.code) AS result_condition_codes,

first_value(patient_gender) AS patient_gender,

first_value(patient_joined_groups.current_age_in_years) AS patient_age,

first_value(patient_joined_groups.need_to_check_female_age) AS need_to_check_female_age,

first_value(patient_joined_groups.need_to_check_male_age) AS need_to_check_male_age,

first_value(patient_joined_groups.need_to_check_reason_code) AS need_to_check_reason_code,

first_value(patient_joined_groups.need_to_check_condition_codes) AS need_to_check_condition_codes,

first_value(patient_joined_groups.need_to_check_condition_exclude_codes) AS need_to_check_condition_exclude_codes,

first_value(patient_joined_groups.need_to_check_observation_codes) AS need_to_check_observation_codes,

first_value(patient_joined_groups.need_to_check_service_request_codes) AS need_to_check_service_request_codes,

first_value(patient_joined_groups.need_to_check_diagnostic_report_codes) AS need_to_check_diagnostic_report_codes,

first_value(patient_joined_groups.need_to_check_condition_action_codes) AS need_to_check_action_codes,

first_value(patient_joined_groups.need_to_check_optional_observation_codes) AS need_to_check_optional_observation_codes,

first_value(patient_joined_groups.condition_female_age_from) AS condition_female_age_from,

first_value(patient_joined_groups.condition_female_age_to) AS condition_female_age_to,

first_value(patient_joined_groups.condition_male_age_from) AS condition_male_age_from,

first_value(patient_joined_groups.condition_male_age_to) AS condition_male_age_to,

(patient_gender IS NULL OR

(first_value(patient_joined_groups.skip_male) AND patient_gender = 0)

OR (group_name IN

('hiv_screening_no_risk', 'checkup_40_64_with_risk', 'checkup_65_plus_no_risk',

'tb_screening_no_risk',

'hypertension_eval_no_risk'))

OR (first_value(patient_joined_groups.skip_female) AND patient_gender = 1)

OR (need_to_check_male_age AND patient_gender = 0 AND

patient_age NOT BETWEEN condition_male_age_from AND condition_male_age_to)

OR (need_to_check_female_age AND patient_gender = 1 AND

patient_age NOT BETWEEN condition_female_age_from AND condition_female_age_to)

) AS skip_check,

first_value(patient_joined_groups.is_no_risk) AS is_no_risk,

first_value(patient_joined_groups.condition_reason_code) AS condition_reason_codes,

first_value(patient_joined_groups.condition_action_codes) AS condition_action_codes,

first_value(patient_joined_groups.condition_codes) AS condition_codes,

first_value(patient_joined_groups.condition_exclude_codes) AS condition_exclude_codes,

first_value(patient_joined_groups.condition_observation_codes) AS condition_observation_codes,

first_value(patient_joined_groups.condition_optional_observation_codes) AS condition_optional_observation_codes,

first_value(patient_joined_groups.condition_diagnostic_report_codes) AS condition_diagnostic_report_codes,

first_value(patient_joined_groups.condition_service_request_codes) AS condition_service_request_codes,

(need_to_check_reason_code AND LENGTH(result_reason_codes) = 0) AS force_quit_reason_check,

(need_to_check_action_codes AND LENGTH(result_action_codes) = 0) AS force_quit_action_check,

(need_to_check_condition_codes AND LENGTH(result_condition_codes) = 0) AS force_quit_condition_check,

(need_to_check_service_request_codes AND

LENGTH(result_condition_service_request_codes) =

0) AS force_quit_service_request_check,

(need_to_check_observation_codes AND LENGTH(result_condition_observation_codes) =

0) AS force_quit_observation_check,

(need_to_check_diagnostic_report_codes AND

LENGTH(result_condition_diagnostic_report_codes) =

0) AS force_quit_diagnostic_report_check,

(need_to_check_condition_exclude_codes AND LENGTH(result_condition_codes) =

0) AS force_quit_condition_exclude_check,

IF(skip_check OR NOT need_to_check_diagnostic_report_codes, TRUE,

IF(force_quit_diagnostic_report_check, FALSE,

need_to_check_diagnostic_report_codes AND

arrayExists(x -> has(result_condition_diagnostic_report_codes, x),

condition_diagnostic_report_codes)

)) AS diagnostic_report_check,

IF(skip_check OR NOT need_to_check_reason_code, TRUE,

IF(force_quit_reason_check, FALSE,

arrayExists(x -> has(condition_reason_codes, x), result_condition_codes) OR

arrayExists(x -> has(condition_reason_codes, x), result_reason_codes)

)) AS reason_check,

IF(skip_check OR NOT need_to_check_action_codes, TRUE,

IF(force_quit_action_check, FALSE,

arrayExists(x -> has(condition_action_codes, x), result_action_codes)

)) AS action_check,

IF(skip_check OR NOT need_to_check_condition_codes, TRUE,

IF(force_quit_condition_check, FALSE, arrayExists(x -> has(condition_codes, x),

result_condition_codes))) AS condition_check,

IF(skip_check OR NOT need_to_check_service_request_codes, TRUE,

IF(force_quit_service_request_check, FALSE,

need_to_check_service_request_codes AND

arrayExists(x -> has(condition_service_request_codes, x),

result_condition_service_request_codes))) AS service_request_check,

IF(skip_check OR NOT need_to_check_condition_exclude_codes, TRUE,

IF(force_quit_condition_exclude_check, TRUE,

NOT arrayExists(x -> has(condition_exclude_codes, x),

result_condition_codes))) AS condition_exclude_check,

IF(skip_check OR NOT need_to_check_observation_codes, TRUE,

IF(force_quit_observation_check, FALSE,

(

(NOT need_to_check_optional_observation_codes AND

LENGTH(arrayIntersect(condition_observation_codes,

result_condition_observation_codes)) =

LENGTH(condition_observation_codes))

OR (

need_to_check_optional_observation_codes AND

(

(LENGTH(arrayIntersect(condition_observation_codes,

result_condition_observation_codes)) =

LENGTH(condition_observation_codes) AND

((need_to_check_diagnostic_report_codes AND diagnostic_report_check) OR

(need_to_check_service_request_codes AND service_request_check)))

OR LENGTH(arrayIntersect(arrayConcat(condition_observation_codes,

condition_optional_observation_codes),

result_condition_observation_codes)) =

LENGTH(arrayConcat(condition_observation_codes,

condition_optional_observation_codes))

)

) AS observation_check,

CASE

WHEN skip_check OR

(need_to_check_condition_exclude_codes AND NOT condition_exclude_check)

THEN 'no_need_done'

WHEN condition_check AND action_check AND reason_check AND condition_exclude_check

AND IF(need_to_check_optional_observation_codes = TRUE, observation_check,

(need_to_check_observation_codes AND observation_check) OR

((need_to_check_service_request_codes AND service_request_check) OR

(need_to_check_diagnostic_report_codes AND diagnostic_report_check)))

THEN 'done'

WHEN need_to_check_condition_codes AND condition_check AND

need_to_check_condition_exclude_codes AND condition_exclude_check

THEN 'need_done'

WHEN (is_no_risk AND NOT condition_exclude_check) OR

(NOT is_no_risk AND NOT condition_check) THEN 'no_need_done'

ELSE 'need_done'

END AS STATUS,

ROW_NUMBER() OVER (PARTITION BY patient_id, indicator_name ORDER BY IF(STATUS = 'done', 1, IF(STATUS = 'need_done', 2, 3))) AS status_rank

FROM patient_joined_groups

LEFT JOIN patient_joined_groups_with_dos

USING patient_id, indicator_name, group_name LEFT

JOIN patient_joined_groups_with_services

USING patient_id, indicator_name, group_name LEFT

JOIN patient_joined_groups_with_observations

USING patient_id, indicator_name, group_name LEFT

JOIN patient_joined_groups_with_reasons

USING patient_id, indicator_name, group_name LEFT

JOIN patient_joined_groups_with_actions

USING patient_id, indicator_name, group_name LEFT

JOIN patient_joined_groups_with_pdc USING patient_id, indicator_name, group_name

GROUP BY patient_joined_groups.patient_id, patient_joined_groups.indicator_name,

patient_joined_groups.group_name)

SELECT patients.id,

patients.patient_full_name,

patients.legal_entity_id,

patients.current_age_in_years,

patients.doctor_full_name,

any(IF(indicators.indicator_name = 'breast_completed', indicators.STATUS, NULL)) AS breast_completed_status,

any(IF(indicators.indicator_name = 'breast_referral', indicators.STATUS, NULL)) AS breast_referral_status,

any(IF(indicators.indicator_name = 'checkup_40_64', indicators.STATUS, NULL)) AS checkup_40_64_status,

any(IF(indicators.indicator_name = 'checkup_65_plus', indicators.STATUS, NULL)) AS checkup_65_plus_status,

any(IF(indicators.indicator_name = 'colorectal_completed', indicators.STATUS,

NULL)) AS colorectal_completed_status,

any(IF(indicators.indicator_name = 'colorectal_referral', indicators.STATUS,

NULL)) AS colorectal_referral_status,

any(IF(indicators.indicator_name = 'cv_risk_assessment', indicators.STATUS,

NULL)) AS cv_risk_assessment_status,

any(IF(indicators.indicator_name = 'diabetes_screening', indicators.STATUS,

NULL)) AS diabetes_screening_status,

any(IF(indicators.indicator_name = 'hiv_screening', indicators.STATUS, NULL)) AS hiv_screening_status,

any(IF(indicators.indicator_name = 'hypertension_eval', indicators.STATUS, NULL)) AS hypertension_eval_status,

any(IF(indicators.indicator_name = 'prostate_completed', indicators.STATUS,

NULL)) AS prostate_completed_status,

any(IF(indicators.indicator_name = 'prostate_referral', indicators.STATUS, NULL)) AS prostate_referral_status,

any(IF(indicators.indicator_name = 'tb_screening', indicators.STATUS, NULL)) AS tb_screening_status,

'{' || arrayStringConcat(

arrayMap(

(name, STATUS, sr_codes, do_codes, obs_codes, condition_codes, action_codes) ->

concat('"', name, '": {',

'"status": "', STATUS, '",',

'"sr_codes": [', arrayStringConcat(arrayMap(x -> concat('"', x, '"'), sr_codes), ','),

'],',

'"do_codes": [', arrayStringConcat(arrayMap(x -> concat('"', x, '"'), do_codes), ','),

'],',

'"obs_codes": [',

arrayStringConcat(arrayMap(x -> concat('"', x, '"'), obs_codes), ','), '],',

'"action_codes": [',

arrayStringConcat(arrayMap(x -> concat('"', x, '"'), action_codes), ','), ']',

'}'),

groupArray(indicators.indicator_name),

groupArray(indicators.STATUS),

groupArray(indicators.result_condition_service_request_codes),

groupArray(indicators.result_condition_diagnostic_report_codes),

groupArray(indicators.result_condition_observation_codes),

groupArray(indicators.result_condition_codes),

groupArray(indicators.result_action_codes)

),

','

) || '}' AS indicator_data_json

FROM patients

JOIN patient_groups indicators ON status_rank = 1 AND patients.id = patient_groups.patient_id

WHERE patient_groups.status_rank = 1

GROUP BY patients.id, patients.patient_full_name, patients.legal_entity_id, patients.current_age_in_years,

patients.doctor_full_name

ORDER BY patients.patient_full_name ASC NULLS LAST COLLATE 'uk'

LIMIT 15 OFFSET 0