| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This report shows the total number of surveys with a certain name, done before a certain age, for all active patients between a certain age range.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
In this example, active patients between ages 3 and 4 are checked to see how many ASQs they’ve had by the time they are 1095 days old (1095/365 = third birthday).
- You can edit the age range by changing the line:
- age >=3 and age <4
- You can change the title(s) of the surveys by changing the line
- where (title like '%ASQ%' or title like '%Ages and Stages%')
- You can change the maximum age in days for the survey by changing the line
- where (admin_date-birthdat) <= 1095
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select * from
(
select register.patno, birthdat, datediff(month,
dateadd(day,-extract(day from birthdat)+1,birthdat),
dateadd(day,-extract (day from birthdat)+1,cast('today' as date))
)/12 as age, status_pat, total_asq from register
left outer join
(
select patno, count(title) as total_asq from
(
select register.patno, TITLE, admin_date, status_pat from register
left outer join (select register.patno, admin_date, questionhead_id from quest_result_header inner join register on register.patno = quest_result_header.patno where (admin_date-birthdat) <= 1095 ) qrh on qrh.patno = register.patno
left outer join (select questionhead_id, title from quest_Questionnaire_header ) qqh on qqh.questionhead_id = qrh.questionhead_id
)
where (title like '%ASQ%' or title like '%Ages and Stages%')
group by patno
) a on a.patno = register.patno
)
where status_pat = 'ACTIVE' and
age >=3 and age <4
order by patnoSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select * from ( select register.patno, birthdat, floor(timestampdiff(year,birthdat,curdate())) as age, status_pat, total_asq from register left outer join ( select patno, count(title) as total_asq from ( select register.patno, TITLE, admin_date, status_pat from register left outer join (select register.patno, admin_date, questionhead_id from quest_result_header inner join register on register.patno = quest_result_header.patno where (admin_date-birthdat) <= 1095 ) qrh on qrh.patno = register.patno left outer join (select questionhead_id, title from quest_Questionnaire_header ) qqh on qqh.questionhead_id = qrh.questionhead_id ) t1 where (title like '%ASQ%' or title like '%Ages and Stages%') group by patno ) a on a.patno = register.patno ) t2 where status_pat = 'ACTIVE' and age >=3 and age <4 order by patno
