154 lines
9.9 KiB
SQL
154 lines
9.9 KiB
SQL
SELECT
|
|
first_answers.answer_name AS first_concept_name,
|
|
second_answers.answer_name AS second_concept_name,
|
|
gender.gender AS gender,
|
|
rag.name AS age_group,
|
|
rag.sort_order AS age_group_sort_order,p.person_id,
|
|
sum(CASE WHEN first_concept.answer IS NOT NULL AND second_concept.answer IS NOT NULL AND p.gender IS NOT NULL THEN 1
|
|
ELSE 0 END) AS patient_count
|
|
FROM
|
|
(SELECT
|
|
ca.answer_concept AS answer,
|
|
ifnull(answer_concept_short_name.name, answer_concept_fully_specified_name.name) AS answer_name
|
|
FROM concept c
|
|
INNER JOIN concept_datatype cd ON c.datatype_id = cd.concept_datatype_id
|
|
INNER JOIN concept_name question_concept_name ON c.concept_id = question_concept_name.concept_id
|
|
AND question_concept_name.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND
|
|
question_concept_name.voided IS FALSE
|
|
INNER JOIN concept_answer ca ON c.concept_id = ca.concept_id
|
|
INNER JOIN concept_name answer_concept_fully_specified_name
|
|
ON ca.answer_concept = answer_concept_fully_specified_name.concept_id
|
|
AND answer_concept_fully_specified_name.concept_name_type = 'FULLY_SPECIFIED' AND
|
|
answer_concept_fully_specified_name.voided IS FALSE
|
|
LEFT JOIN concept_name answer_concept_short_name ON ca.answer_concept = answer_concept_short_name.concept_id
|
|
AND answer_concept_short_name.concept_name_type = 'SHORT' AND
|
|
answer_concept_short_name.voided IS FALSE
|
|
WHERE question_concept_name.name = 'Non Communicable Disease Set, Non Communicable Disease' AND cd.name = 'Coded'
|
|
UNION
|
|
SELECT
|
|
answer_concept_fully_specified_name.concept_id AS answer,
|
|
answer_concept_fully_specified_name.name AS answer_name
|
|
FROM concept c
|
|
INNER JOIN concept_datatype cd ON c.datatype_id = cd.concept_datatype_id
|
|
INNER JOIN concept_name question_concept_name ON c.concept_id = question_concept_name.concept_id
|
|
AND question_concept_name.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND
|
|
question_concept_name.voided IS FALSE
|
|
INNER JOIN global_property gp ON gp.property IN ('concept.true', 'concept.false')
|
|
INNER JOIN concept_name answer_concept_fully_specified_name
|
|
ON answer_concept_fully_specified_name.concept_id = gp.property_value
|
|
AND answer_concept_fully_specified_name.concept_name_type = 'FULLY_SPECIFIED' AND
|
|
answer_concept_fully_specified_name.voided IS FALSE
|
|
WHERE question_concept_name.name = 'Non Communicable Disease Set, Non Communicable Disease' AND cd.name = 'Boolean'
|
|
ORDER BY answer_name DESC) first_answers
|
|
INNER JOIN
|
|
(SELECT
|
|
ca.answer_concept AS answer,
|
|
ifnull(answer_concept_short_name.name, answer_concept_fully_specified_name.name) AS answer_name
|
|
FROM concept c
|
|
INNER JOIN concept_datatype cd ON c.datatype_id = cd.concept_datatype_id
|
|
INNER JOIN concept_name question_concept_name ON c.concept_id = question_concept_name.concept_id
|
|
AND question_concept_name.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND
|
|
question_concept_name.voided IS FALSE
|
|
INNER JOIN concept_answer ca ON c.concept_id = ca.concept_id
|
|
INNER JOIN concept_name answer_concept_fully_specified_name
|
|
ON ca.answer_concept = answer_concept_fully_specified_name.concept_id
|
|
AND answer_concept_fully_specified_name.concept_name_type = 'FULLY_SPECIFIED' AND
|
|
answer_concept_fully_specified_name.voided IS FALSE
|
|
LEFT JOIN concept_name answer_concept_short_name ON ca.answer_concept = answer_concept_short_name.concept_id
|
|
AND answer_concept_short_name.concept_name_type = 'SHORT' AND
|
|
answer_concept_short_name.voided IS FALSE
|
|
WHERE question_concept_name.name = 'Non Communicable Disease Set, Risky Behaviour' AND cd.name = 'Coded'
|
|
UNION
|
|
SELECT
|
|
answer_concept_fully_specified_name.concept_id AS answer,
|
|
answer_concept_fully_specified_name.name AS answer_name
|
|
FROM concept c
|
|
INNER JOIN concept_datatype cd ON c.datatype_id = cd.concept_datatype_id
|
|
INNER JOIN concept_name question_concept_name ON c.concept_id = question_concept_name.concept_id
|
|
AND question_concept_name.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND
|
|
question_concept_name.voided IS FALSE
|
|
INNER JOIN global_property gp ON gp.property IN ('concept.true', 'concept.false')
|
|
INNER JOIN concept_name answer_concept_fully_specified_name
|
|
ON answer_concept_fully_specified_name.concept_id = gp.property_value
|
|
AND answer_concept_fully_specified_name.concept_name_type = 'FULLY_SPECIFIED' AND
|
|
answer_concept_fully_specified_name.voided IS FALSE
|
|
WHERE question_concept_name.name = 'Non Communicable Disease Set, Risky Behaviour' AND cd.name = 'Boolean'
|
|
ORDER BY answer_name DESC
|
|
) second_answers
|
|
INNER JOIN (SELECT 'M' AS gender
|
|
UNION SELECT 'F' AS gender
|
|
UNION SELECT 'O' AS gender) gender
|
|
INNER JOIN reporting_age_group rag ON rag.report_group_name = 'All Ages'
|
|
LEFT OUTER JOIN (
|
|
SELECT
|
|
o1.person_id,
|
|
cn2.concept_id AS answer,
|
|
cn1.concept_id AS question,
|
|
v1.visit_id AS visit_id,
|
|
v1.date_stopped AS datetime
|
|
FROM obs o1
|
|
INNER JOIN concept_name cn1
|
|
ON o1.concept_id = cn1.concept_id AND
|
|
cn1.concept_name_type = 'FULLY_SPECIFIED' AND cn1.name = 'Non Communicable Disease Set, Non Communicable Disease'
|
|
AND o1.voided = 0 AND cn1.voided = 0
|
|
INNER JOIN concept_name cn2
|
|
ON o1.value_coded = cn2.concept_id
|
|
AND cn2.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND cn2.voided = 0
|
|
INNER JOIN encounter e1
|
|
ON o1.encounter_id = e1.encounter_id
|
|
INNER JOIN visit v1
|
|
ON v1.visit_id = e1.visit_id
|
|
AND v1.date_stopped IS NOT NULL
|
|
WHERE cast(v1.date_stopped AS DATE) BETWEEN '2014-10-01' AND '2015-11-11'
|
|
AND o1.obs_id = (SELECT max(obs_id)
|
|
FROM obs obs2
|
|
INNER JOIN encounter e2 ON obs2.encounter_id = e2.encounter_id
|
|
WHERE obs2.concept_id = o1.concept_id
|
|
AND e2.visit_id = v1.visit_id
|
|
AND obs2.voided IS FALSE)
|
|
) first_concept
|
|
ON first_concept.answer = first_answers.answer
|
|
LEFT JOIN (
|
|
SELECT
|
|
o1.person_id,
|
|
cn2.concept_id AS answer,
|
|
cn1.concept_id AS question,
|
|
v1.visit_id AS visit_id,
|
|
v1.date_stopped AS datetime,
|
|
e1.encounter_id
|
|
FROM obs o1
|
|
INNER JOIN concept_name cn1
|
|
ON o1.concept_id = cn1.concept_id AND
|
|
cn1.concept_name_type = 'FULLY_SPECIFIED' AND cn1.name = 'Non Communicable Disease Set, Risky Behaviour'
|
|
AND o1.voided = 0 AND cn1.voided = 0
|
|
INNER JOIN concept_name cn2
|
|
ON o1.value_coded = cn2.concept_id
|
|
AND cn2.concept_name_type = 'FULLY_SPECIFIED'
|
|
AND cn2.voided = 0
|
|
INNER JOIN encounter e1
|
|
ON o1.encounter_id = e1.encounter_id
|
|
INNER JOIN visit v1
|
|
ON v1.visit_id = e1.visit_id
|
|
AND v1.date_stopped IS NOT NULL
|
|
WHERE cast(v1.date_stopped AS DATE) BETWEEN '2014-10-01' AND '2015-11-11'
|
|
AND o1.obs_id = (SELECT max(obs_id)
|
|
FROM obs obs2
|
|
INNER JOIN encounter e2 ON obs2.encounter_id = e2.encounter_id
|
|
WHERE obs2.concept_id = o1.concept_id
|
|
AND e2.visit_id = v1.visit_id
|
|
AND obs2.voided IS FALSE)
|
|
) second_concept
|
|
ON second_concept.answer = second_answers.answer
|
|
AND first_concept.person_id = second_concept.person_id
|
|
AND first_concept.visit_id = second_concept.visit_id
|
|
LEFT OUTER JOIN person p ON first_concept.person_id = p.person_id AND p.gender = gender.gender
|
|
AND cast(first_concept.datetime AS DATE) BETWEEN (DATE_ADD(
|
|
DATE_ADD(p.birthdate, INTERVAL rag.min_years YEAR), INTERVAL rag.min_days
|
|
DAY)) AND (DATE_ADD(DATE_ADD(p.birthdate, INTERVAL rag.max_years YEAR), INTERVAL
|
|
rag.max_days DAY))
|
|
GROUP BY first_answers.answer_name, second_answers.answer_name, gender.gender, rag.name, rag.sort_order,p.person_id |