API tools faq
paste
Advertisement
temaon_lieto

SQL chronic_diseases/employees

temaon_lieto
Feb 2nd, 2024 (edited)
94
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 9.48 KB | None | 0 0
raw download clone embed print report
  1. -- http://localhost:4000/api/v1/chronic_diseases/employees?employee_ids%5B%5D=181286
  2.  
  3. SELECT mv_stat_employees_parties."Id",
  4. mv_stat_employees_parties."FullName",
  5. COUNT(DISTINCT mv_stat_declarations.patient_id) AS total_counter,
  6. COUNT(DISTINCT mv_stat_patient_personalities.id) filter (WHERE mv_stat_patient_personalities.is_smoking = true AND mv_stat_patient_personalities.age BETWEEN 18 AND 69) AS smoking_counter,
  7. COUNT(DISTINCT mv_stat_imt_diseases.patient_id) filter (WHERE mv_stat_patient_personalities.age BETWEEN 18 AND 69 AND mv_stat_imt_diseases.imt_index > 25 AND "mv_stat_imt_diseases"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999' ) AS overweight_counter,
  8. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('K85', 'K86', 'K87') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999' ) AS hypertension_counter,
  9. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('T90') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS diabetes_counter,
  10. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('R96') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS asthma_counter,
  11. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code LIKE 'P%' AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS derangement_counter,
  12. COUNT(DISTINCT receptions.patient_id) filter (WHERE receptions.ehealth_status = 1 AND receptions.asserted_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS reception_counter FROM "mv_stat_employees_parties" LEFT OUTER JOIN "receptions" ON "receptions"."employee_id" = "mv_stat_employees_parties"."Id" LEFT OUTER JOIN "mv_stat_declarations" ON "mv_stat_declarations"."declaration_status_code" = $1 AND "mv_stat_declarations"."employee_id" = "mv_stat_employees_parties"."Id" LEFT OUTER JOIN "mv_stat_imt_diseases" ON "mv_stat_imt_diseases"."patient_id" = "mv_stat_declarations"."patient_id" LEFT OUTER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" LEFT OUTER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_employees_parties"."EmployeeStatusRefValue" = 'APPROVED') AND ("mv_stat_employees_parties"."EmployeeTypeRefValue" = 'DOCTOR') AND ("mv_stat_employees_parties"."LegalEntityId" = 3228) AND "mv_stat_employees_parties"."Id" = $2 GROUP BY "Id", "FullName" LIMIT $3 OFFSET $4  [["declaration_status_code", "ACTIVE"], ["Id", 181286], ["LIMIT", 15], ["OFFSET", 0]]
  13.  
  14. SELECT mv_stat_employees_parties."Id",
  15. mv_stat_employees_parties."FullName",
  16. COUNT(DISTINCT mv_stat_declarations.patient_id) AS total_counter,
  17. COUNT(DISTINCT mv_stat_patient_personalities.id) filter (WHERE mv_stat_patient_personalities.is_smoking = true AND mv_stat_patient_personalities.age BETWEEN 18 AND 69) AS smoking_counter,
  18. COUNT(DISTINCT mv_stat_imt_diseases.patient_id) filter (WHERE mv_stat_patient_personalities.age BETWEEN 18 AND 69 AND mv_stat_imt_diseases.imt_index > 25 AND "mv_stat_imt_diseases"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999' ) AS overweight_counter,
  19. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('K85', 'K86', 'K87') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999' ) AS hypertension_counter,
  20. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('T90') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS diabetes_counter,
  21. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code IN ('R96') AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS asthma_counter,
  22. COUNT(DISTINCT mv_stat_conditions.patient_id) filter (WHERE mv_stat_conditions.code LIKE 'P%' AND "mv_stat_conditions"."asserted_date" BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS derangement_counter,
  23. COUNT(DISTINCT receptions.patient_id) filter (WHERE receptions.ehealth_status = 1 AND receptions.asserted_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') AS reception_counter FROM "mv_stat_employees_parties" LEFT OUTER JOIN "receptions" ON "receptions"."employee_id" = "mv_stat_employees_parties"."Id" LEFT OUTER JOIN "mv_stat_declarations" ON "mv_stat_declarations"."declaration_status_code" = $1 AND "mv_stat_declarations"."employee_id" = "mv_stat_employees_parties"."Id" LEFT OUTER JOIN "mv_stat_imt_diseases" ON "mv_stat_imt_diseases"."patient_id" = "mv_stat_declarations"."patient_id" LEFT OUTER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" LEFT OUTER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_employees_parties"."EmployeeStatusRefValue" = 'APPROVED') AND ("mv_stat_employees_parties"."EmployeeTypeRefValue" = 'DOCTOR') AND ("mv_stat_employees_parties"."LegalEntityId" = 3228) AND "mv_stat_employees_parties"."Id" = $2 GROUP BY "Id", "FullName"  [["declaration_status_code", "ACTIVE"], ["Id", 181286]]
  24.  
  25.  
  26. SELECT COUNT("mv_stat_declarations"."id") FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228)
  27.  
  28. SELECT COUNT(DISTINCT mv_stat_conditions.patient_id) FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" INNER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND (mv_stat_conditions.code LIKE 'P%') AND "mv_stat_conditions"."asserted_date" BETWEEN $1 AND $2  [["asserted_date", "2024-01-01 00:00:00"], ["asserted_date", "2024-02-02 23:59:59.999999"]]
  29.  
  30. SELECT COUNT(DISTINCT mv_stat_conditions.patient_id) FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" INNER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND "mv_stat_conditions"."code" = $1 AND "mv_stat_conditions"."asserted_date" BETWEEN $2 AND $3  [["code", "R96"], ["asserted_date", "2024-01-01 00:00:00"], ["asserted_date", "2024-02-02 23:59:59.999999"]]
  31.  
  32. SELECT COUNT(DISTINCT mv_stat_conditions.patient_id) FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" INNER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND "mv_stat_conditions"."code" = $1 AND "mv_stat_conditions"."asserted_date" BETWEEN $2 AND $3  [["code", "T90"], ["asserted_date", "2024-01-01 00:00:00"], ["asserted_date", "2024-02-02 23:59:59.999999"]]
  33.  
  34.  SELECT COUNT(DISTINCT mv_stat_conditions.patient_id) FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" INNER JOIN "mv_stat_conditions" ON "mv_stat_conditions"."patient_id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND "mv_stat_conditions"."code" IN ($1, $2, $3) AND "mv_stat_conditions"."asserted_date" BETWEEN $4 AND $5  [["code", "K85"], ["code", "K86"], ["code", "K87"], ["asserted_date", "2024-01-01 00:00:00"], ["asserted_date", "2024-02-02 23:59:59.999999"]]
  35.  
  36. SELECT COUNT("mv_stat_patient_personalities"."id") FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND "mv_stat_patient_personalities"."age" BETWEEN $1 AND $2 AND "mv_stat_patient_personalities"."is_smoking" = $3  [["age", "18.0"], ["age", "69.0"], ["is_smoking", true]]
  37.  
  38. SELECT COUNT("mv_stat_employees_parties"."Id") FROM "mv_stat_employees_parties" WHERE ("mv_stat_employees_parties"."LegalEntityId" = 3228) AND ("mv_stat_employees_parties"."EmployeeTypeRefValue" = 'DOCTOR') AND ("mv_stat_employees_parties"."EmployeeStatusRefValue" = 'APPROVED')
  39.  
  40. SELECT COUNT("mv_stat_declarations"."id") FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228)
  41.  
  42. SELECT COUNT(DISTINCT mv_stat_imt_diseases.patient_id) FROM "mv_stat_declarations" INNER JOIN "mv_stat_patient_personalities" ON "mv_stat_patient_personalities"."id" = "mv_stat_declarations"."patient_id" INNER JOIN "mv_stat_imt_diseases" ON "mv_stat_imt_diseases"."patient_id" = "mv_stat_declarations"."patient_id" WHERE ("mv_stat_declarations"."legal_entity_id" = 3228) AND "mv_stat_patient_personalities"."age" BETWEEN $1 AND $2 AND "mv_stat_imt_diseases"."asserted_date" BETWEEN $3 AND $4  [["age", "18.0"], ["age", "69.0"], ["asserted_date", "2024-01-01 00:00:00"], ["asserted_date", "2024-02-02 23:59:59.999999"]]
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!