API tools faq
paste
Advertisement
temaon_lieto

Test new sql for employees

temaon_lieto
Mar 9th, 2024
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 22.01 KB | None | 0 0
raw download clone embed print report
  1. Gather  (cost=1000.87..6418.26 rows=4 width=39) (actual time=1.465..85.471 rows=26214 loops=1)
  2. SELECT "mv_stat_lvl_1_declarations".* 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 WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528 AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64) AND "mv_stat_lvl_1_patients"."legal_entity_id" = 2528;
  3.  
  4. Gather  (cost=1000.87..6418.26 rows=4 width=39) (actual time=0.960..218.431 rows=53275 loops=1)
  5. SELECT "mv_stat_lvl_1_declarations".* 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 WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528 AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 14 AND 999);
  6.  
  7. Gather  (cost=1000.87..5682.91 rows=1 width=39) (actual time=4.957..68.131 rows=9428 loops=1)
  8. SELECT "mv_stat_lvl_1_declarations".* 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 WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528 AND "mv_stat_lvl_1_patients"."gender" = 0 AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 75);
  9.  
  10. SELECT "mv_stat_lvl_1_declarations".* 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 WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528 AND "mv_stat_lvl_1_patients"."gender" = 1 AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 69)
  11.  
  12.  
  13. Nested Loop  (cost=17.85..34.50 rows=1 width=39) (actual time=0.119..0.123 rows=0 loops=1)
  14. WITH "items" AS (SELECT "mv_stat_lvl_0_ehr_service_requests".*
  15.                  FROM ((SELECT mv_stat_lvl_0_ehr_service_requests.id,
  16.                                mv_stat_lvl_0_ehr_service_requests.asserted_date,
  17.                                mv_stat_lvl_0_ehr_service_requests.code,
  18.                                mv_stat_lvl_0_ehr_service_requests.item_type,
  19.                                mv_stat_lvl_0_ehr_service_requests.status::varchar,
  20.                                mv_stat_lvl_0_ehr_service_requests.title,
  21.                                mv_stat_lvl_0_ehr_service_requests.employee_id,
  22.                                mv_stat_lvl_0_ehr_service_requests.legal_entity_id,
  23.                                mv_stat_lvl_0_ehr_service_requests.patient_id
  24.                         FROM "mv_stat_lvl_0_ehr_service_requests"
  25.                         WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  26.                           AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 2528)
  27.                        UNION
  28.                        (SELECT mv_stat_lvl_0_ehr_service_requests.id,
  29.                                mv_stat_lvl_0_ehr_service_requests.asserted_date,
  30.                                mv_stat_lvl_0_ehr_service_requests.code,
  31.                                mv_stat_lvl_0_ehr_service_requests.item_type,
  32.                                mv_stat_lvl_0_ehr_service_requests.status::varchar,
  33.                                mv_stat_lvl_0_ehr_service_requests.title,
  34.                                mv_stat_lvl_0_ehr_service_requests.employee_id,
  35.                                mv_stat_lvl_0_ehr_service_requests.legal_entity_id,
  36.                                mv_stat_lvl_0_ehr_service_requests.patient_id
  37.                         FROM "mv_stat_lvl_0_ehr_service_requests"
  38.                         WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  39.                           AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 2528)) "mv_stat_lvl_0_ehr_service_requests")
  40. SELECT "mv_stat_lvl_1_declarations".*
  41. FROM "mv_stat_lvl_1_declarations"
  42.          JOIN mv_stat_lvl_1_patients
  43.               ON mv_stat_lvl_1_patients.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  44.                  mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id
  45.          JOIN items ON items.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  46.                        items.patient_id = mv_stat_lvl_1_declarations.patient_id
  47. WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528
  48.   AND "items"."code" IN ('D36003', 'D67006', '32084-00', '32090-00', '32084-02', '32090-02')
  49.   AND "mv_stat_lvl_1_patients"."gender" = 0
  50.   AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 50 AND 75)
  51.   AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.99999
  52.  
  53. Nested Loop  (cost=26.47..47.52 rows=1 width=39) (actual time=0.075..0.079 rows=0 loops=1)
  54. WITH "items" AS (SELECT "mv_stat_lvl_0_ehr_service_requests".*
  55.                 FROM ((SELECT "mv_stat_lvl_0_ehr_service_requests".*
  56.                        FROM ((SELECT mv_stat_lvl_0_ehr_service_requests.id,
  57.                                      mv_stat_lvl_0_ehr_service_requests.asserted_date,
  58.                                      mv_stat_lvl_0_ehr_service_requests.code,
  59.                                      mv_stat_lvl_0_ehr_service_requests.item_type,
  60.                                      mv_stat_lvl_0_ehr_service_requests.status::varchar,
  61.                                      mv_stat_lvl_0_ehr_service_requests.title,
  62.                                      mv_stat_lvl_0_ehr_service_requests.employee_id,
  63.                                      mv_stat_lvl_0_ehr_service_requests.legal_entity_id,
  64.                                      mv_stat_lvl_0_ehr_service_requests.patient_id
  65.                               FROM "mv_stat_lvl_0_ehr_service_requests"
  66.                               WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  67.                                 AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 2528)
  68.                              UNION
  69.                              (SELECT mv_stat_lvl_0_ehr_service_requests.id,
  70.                                      mv_stat_lvl_0_ehr_service_requests.asserted_date,
  71.                                      mv_stat_lvl_0_ehr_service_requests.code,
  72.                                      mv_stat_lvl_0_ehr_service_requests.item_type,
  73.                                      mv_stat_lvl_0_ehr_service_requests.status::varchar,
  74.                                      mv_stat_lvl_0_ehr_service_requests.title,
  75.                                      mv_stat_lvl_0_ehr_service_requests.employee_id,
  76.                                      mv_stat_lvl_0_ehr_service_requests.legal_entity_id,
  77.                                      mv_stat_lvl_0_ehr_service_requests.patient_id
  78.                               FROM "mv_stat_lvl_0_ehr_service_requests"
  79.                               WHERE "mv_stat_lvl_0_ehr_service_requests"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  80.                                 AND "mv_stat_lvl_0_ehr_service_requests"."legal_entity_id" = 2528)) "mv_stat_lvl_0_ehr_service_requests")
  81.                       UNION
  82.                       (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id,
  83.                               mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date,
  84.                               mv_stat_lvl_0_ehr_diagnostic_reports.code,
  85.                               mv_stat_lvl_0_ehr_diagnostic_reports.item_type,
  86.                               mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar,
  87.                               mv_stat_lvl_0_ehr_diagnostic_reports.title,
  88.                               mv_stat_lvl_0_ehr_diagnostic_reports.employee_id,
  89.                               mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id,
  90.                               mv_stat_lvl_0_ehr_diagnostic_reports.patient_id
  91.                        FROM "mv_stat_lvl_0_ehr_diagnostic_reports"
  92.                        WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  93.                          AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 2528)) "mv_stat_lvl_0_ehr_service_requests")
  94. SELECT "mv_stat_lvl_1_declarations".*
  95. FROM "mv_stat_lvl_1_declarations"
  96.         JOIN mv_stat_lvl_1_patients
  97.              ON mv_stat_lvl_1_patients.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  98.                 mv_stat_lvl_1_patients.id = mv_stat_lvl_1_declarations.patient_id
  99.         JOIN items ON items.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  100.                       items.patient_id = mv_stat_lvl_1_declarations.patient_id
  101. WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528
  102.  AND "items"."code" IN ('B33006', 'B33012')
  103.  AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 14 AND 999)
  104.  AND "items"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999';
  105.  
  106. Nested Loop  (cost=18.67..45.73 rows=1 width=39) (actual time=0.077..0.082 rows=0 loops=1)
  107. WITH "selected_patients" AS (SELECT "mv_stat_lvl_1_patients".*
  108.                             FROM "mv_stat_lvl_1_patients"
  109.                             WHERE (DATE_PART('YEAR',
  110.                                              AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 40 AND 64)
  111.                               AND "mv_stat_lvl_1_patients"."legal_entity_id" = 2528),
  112.     "diagnoses" AS (SELECT "mv_stat_lvl_1_reception_conditions".*
  113.                     FROM "mv_stat_lvl_1_reception_conditions"
  114.                     WHERE "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  115.                       AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 2528),
  116.     "reasons" AS (SELECT "mv_stat_lvl_1_ehr_reasons".*
  117.                   FROM "mv_stat_lvl_1_ehr_reasons"
  118.                   WHERE "mv_stat_lvl_1_ehr_reasons"."code" = 'A98'
  119.                     AND "mv_stat_lvl_1_ehr_reasons"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  120.                     AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 2528),
  121.     "diagnoses_c1_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter
  122.                                FROM diagnoses
  123.                                GROUP BY "diagnoses"."patient_id"
  124.                                HAVING (COUNT(DISTINCT diagnoses.code) > 0)),
  125.     "observations" AS (SELECT "mv_stat_lvl_1_ehr_observations".*
  126.                        FROM "mv_stat_lvl_1_ehr_observations"
  127.                        WHERE "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 2528
  128.                          AND "mv_stat_lvl_1_ehr_observations"."code" IN
  129.                              ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4')
  130.                          AND "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'),
  131.     "reports" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id,
  132.                          mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date,
  133.                          mv_stat_lvl_0_ehr_diagnostic_reports.code,
  134.                          mv_stat_lvl_0_ehr_diagnostic_reports.item_type,
  135.                          mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar,
  136.                          mv_stat_lvl_0_ehr_diagnostic_reports.title,
  137.                          mv_stat_lvl_0_ehr_diagnostic_reports.employee_id,
  138.                          mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id,
  139.                          mv_stat_lvl_0_ehr_diagnostic_reports.patient_id
  140.                   FROM "mv_stat_lvl_0_ehr_diagnostic_reports"
  141.                   WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."code" IN
  142.                         ('T34001', 'T34006', 'T34024', 'T34011', 'T34013')
  143.                     AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 2528
  144.                     AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'),
  145.     "observations_c1_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter
  146.                                   FROM observations
  147.                                   WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')
  148.                                   GROUP BY "observations"."patient_id"
  149.                                   HAVING (COUNT(DISTINCT observations.code) >= 4))
  150. SELECT "mv_stat_lvl_1_declarations".*
  151. FROM "mv_stat_lvl_1_declarations"
  152.         JOIN selected_patients ON selected_patients.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  153.                                   mv_stat_lvl_1_declarations.patient_id = selected_patients.id
  154.         LEFT JOIN diagnoses ON mv_stat_lvl_1_declarations.patient_id = diagnoses.patient_id AND diagnoses.code = 'A98'
  155.         JOIN diagnoses_c1_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c1_counter.patient_id AND
  156.                                      diagnoses_c1_counter.counter > 0
  157.         LEFT JOIN reasons ON reasons.patient_id = mv_stat_lvl_1_declarations.patient_id
  158.         JOIN reports ON reports.patient_id = mv_stat_lvl_1_declarations.patient_id
  159.         LEFT JOIN observations_c1_counter ON observations_c1_counter.patient_id = mv_stat_lvl_1_declarations.patient_id
  160. WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528
  161.  AND ((diagnoses.code = 'A98' OR reasons.code = 'A98') AND
  162.       (diagnoses_c1_counter.counter > 0 AND observations_c1_counter.counter > 0));
  163.  
  164.  
  165.  
  166. Nested Loop  (cost=1.29..16.17 rows=1 width=39) (actual time=0.037..0.039 rows=0 loops=1)
  167. WITH "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') AND "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999' AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 2528) SELECT "mv_stat_lvl_1_declarations".* 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 JOIN diagnoses ON mv_stat_lvl_1_declarations.patient_id = diagnoses.patient_id WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528 AND (DATE_PART('YEAR', AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999);
  168.  
  169. Nested Loop Left Join  (cost=19.23..54.46 rows=1 width=39) (actual time=0.034..0.040 rows=0 loops=1)
  170. WITH "selected_patients" AS (SELECT "mv_stat_lvl_1_patients".*
  171.                             FROM "mv_stat_lvl_1_patients"
  172.                             WHERE (DATE_PART('YEAR',
  173.                                              AGE('2024-03-09 23:59:59.999999', mv_stat_lvl_1_patients.birth_date)) BETWEEN 65 AND 999)
  174.                               AND "mv_stat_lvl_1_patients"."legal_entity_id" = 2528),
  175.     "diagnoses" AS (SELECT "mv_stat_lvl_1_reception_conditions".*
  176.                     FROM "mv_stat_lvl_1_reception_conditions"
  177.                     WHERE "mv_stat_lvl_1_reception_conditions"."code" IN
  178.                           ('K86', 'K87', 'K76', 'K74', 'T89', 'T90', 'A98')
  179.                       AND "mv_stat_lvl_1_reception_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  180.                       AND "mv_stat_lvl_1_reception_conditions"."legal_entity_id" = 2528),
  181.     "reasons" AS (SELECT "mv_stat_lvl_1_ehr_reasons".*
  182.                   FROM "mv_stat_lvl_1_ehr_reasons"
  183.                   WHERE "mv_stat_lvl_1_ehr_reasons"."code" = 'A98'
  184.                     AND "mv_stat_lvl_1_ehr_reasons"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'
  185.                     AND "mv_stat_lvl_1_ehr_reasons"."legal_entity_id" = 2528),
  186.     "observations" AS (SELECT "mv_stat_lvl_1_ehr_observations".*
  187.                        FROM "mv_stat_lvl_1_ehr_observations"
  188.                        WHERE "mv_stat_lvl_1_ehr_observations"."legal_entity_id" = 2528
  189.                          AND "mv_stat_lvl_1_ehr_observations"."code" IN
  190.                              ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2', '4548-4')
  191.                          AND "mv_stat_lvl_1_ehr_observations"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'),
  192.     "actions" AS (SELECT "mv_stat_lvl_1_ehr_actions".*
  193.                   FROM "mv_stat_lvl_1_ehr_actions"
  194.                   WHERE "mv_stat_lvl_1_ehr_actions"."code" IN ('K45', 'D45', 'T45')
  195.                     AND "mv_stat_lvl_1_ehr_actions"."legal_entity_id" = 2528
  196.                     AND "mv_stat_lvl_1_ehr_actions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'),
  197.     "reports" AS (SELECT mv_stat_lvl_0_ehr_diagnostic_reports.id,
  198.                          mv_stat_lvl_0_ehr_diagnostic_reports.asserted_date,
  199.                          mv_stat_lvl_0_ehr_diagnostic_reports.code,
  200.                          mv_stat_lvl_0_ehr_diagnostic_reports.item_type,
  201.                          mv_stat_lvl_0_ehr_diagnostic_reports.status::varchar,
  202.                          mv_stat_lvl_0_ehr_diagnostic_reports.title,
  203.                          mv_stat_lvl_0_ehr_diagnostic_reports.employee_id,
  204.                          mv_stat_lvl_0_ehr_diagnostic_reports.legal_entity_id,
  205.                          mv_stat_lvl_0_ehr_diagnostic_reports.patient_id
  206.                   FROM "mv_stat_lvl_0_ehr_diagnostic_reports"
  207.                   WHERE "mv_stat_lvl_0_ehr_diagnostic_reports"."code" IN
  208.                         ('T34001', 'T34006', 'T34024', 'T34011', 'T34013')
  209.                     AND "mv_stat_lvl_0_ehr_diagnostic_reports"."legal_entity_id" = 2528
  210.                     AND "mv_stat_lvl_0_ehr_diagnostic_reports"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-03-09 23:59:59.999999'),
  211.     "diagnoses_c1_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter
  212.                                FROM diagnoses
  213.                                WHERE "diagnoses"."code" IN ('K86', 'K87')
  214.                                GROUP BY "diagnoses"."patient_id"
  215.                                HAVING (COUNT(DISTINCT diagnoses.code) > 0)),
  216.     "diagnoses_c2_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter
  217.                                FROM diagnoses
  218.                                WHERE "diagnoses"."code" IN ('K76', 'K74')
  219.                                GROUP BY "diagnoses"."patient_id"
  220.                                HAVING (COUNT(DISTINCT diagnoses.code) > 0)),
  221.     "diagnoses_c3_counter" AS (SELECT diagnoses.patient_id, COUNT(DISTINCT diagnoses.code) AS counter
  222.                                FROM diagnoses
  223.                                WHERE "diagnoses"."code" IN ('T89', 'T90')
  224.                                GROUP BY "diagnoses"."patient_id"
  225.                                HAVING (COUNT(DISTINCT diagnoses.code) > 0)),
  226.     "observations_c1_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter
  227.                                   FROM observations
  228.                                   WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6')
  229.                                   GROUP BY "observations"."patient_id"
  230.                                   HAVING (COUNT(DISTINCT observations.code) >= 4)),
  231.     "observations_c2_counter" AS (SELECT observations.patient_id, COUNT(DISTINCT observations.code) AS counter
  232.                                   FROM observations
  233.                                   WHERE "observations"."code" IN ('39156-5', '14743-9', '8462-4', '8480-6', '56086-2')
  234.                                   GROUP BY "observations"."patient_id"
  235.                                   HAVING (COUNT(DISTINCT observations.code) >= 5))
  236. SELECT "mv_stat_lvl_1_declarations".*
  237. FROM "mv_stat_lvl_1_declarations"
  238.         JOIN selected_patients ON selected_patients.legal_entity_id = mv_stat_lvl_1_declarations.legal_entity_id AND
  239.                                   mv_stat_lvl_1_declarations.patient_id = selected_patients.id
  240.         INNER JOIN diagnoses ON mv_stat_lvl_1_declarations.patient_id = diagnoses.patient_id
  241.         LEFT JOIN diagnoses_c1_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c1_counter.patient_id
  242.         LEFT JOIN diagnoses_c2_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c2_counter.patient_id
  243.         LEFT JOIN diagnoses_c3_counter ON mv_stat_lvl_1_declarations.patient_id = diagnoses_c3_counter.patient_id
  244.         LEFT JOIN reasons ON reasons.patient_id = mv_stat_lvl_1_declarations.patient_id
  245.         JOIN actions ON actions.patient_id = mv_stat_lvl_1_declarations.patient_id
  246.         JOIN reports ON reports.patient_id = mv_stat_lvl_1_declarations.patient_id
  247.         LEFT JOIN observations_c1_counter ON observations_c1_counter.patient_id = mv_stat_lvl_1_declarations.patient_id
  248.         LEFT JOIN observations_c2_counter ON observations_c2_counter.patient_id = mv_stat_lvl_1_declarations.patient_id
  249. WHERE "mv_stat_lvl_1_declarations"."legal_entity_id" = 2528
  250.  AND ((diagnoses.code = 'A98' OR reasons.code = 'A98') AND
  251.       (((diagnoses_c1_counter.counter > 0 OR diagnoses_c2_counter.counter > 0) AND
  252.         observations_c1_counter.counter > 0) OR
  253.        (diagnoses_c3_counter.counter > 0 AND observations_c2_counter.counter > 0)))
  254. ;
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!