WikiSysop: Created page with "====Number of persons recorded in each assessment==== select count(*), f.community, p.assessmentMonth from person p, family f where f.familyId = p.familyId and f.assessmentMon..."

2021-01-30T11:09:02Z

Created page with "====Number of persons recorded in each assessment==== select count(*), f.community, p.assessmentMonth from person p, family f where f.familyId = p.familyId and f.assessmentMon..."

New page

====Number of persons recorded in each assessment====
select count(*), f.community, p.assessmentMonth from person p, family f
where f.familyId = p.familyId
and
f.assessmentMonth = p.assessmentMonth
group by p.assessmentMonth, f.community

====Total person records in database====
select count(*) from person p

====List of numeric answer by family for multiple assessments in a community====
select f.community, f.assessmentMonth, f.familyId, f.waterDistanceMeters from family f
where f.community = 'Mbatwe'
order by f.familyId, f.assessmentMonth

====List of text answers by family for multiple assessments in a community====
select f.community, f.assessmentMonth, f.familyId, a.enAnswer as whereGoPotty from family f, answerText a
where a.questionName='whereGoPotty'
and
a.answerCode=f.whereGoPotty
and
f.community = 'Mbatwe'
order by f.familyId, f.assessmentMonth

====List of number of families in a community reporting a given answer by assessment====
select count(*) as latrineOrToilet, f.community, f.assessmentMonth, f.familyId from family f, answerText a
where a.questionName='whereGoPotty'
and
a.answerCode=f.whereGoPotty
and
f.community = 'Mbatwe'
and
(
a.enAnswer = 'Latrine'
or
a.enAnswer = 'Toilet'
)
group by f.assessmentMonth

====FAMILIES MISSING FROM 2006-06 BUT IN 2005-10====

SELECT familyId,assessmentMonth,assessmentDate
FROM `family`
WHERE
assessmentMonth = '2005/10/00'
and
familyId not in
(
SELECT familyId
FROM `family`
WHERE
assessmentMonth = '2006/06/00'
)

====List of families increasing by more than X members from first to second assessment====
SELECT f.familyId,f.assessmentMonth,assessmentDate
FROM `family` f
WHERE
f.assessmentMonth = '2006/06/00'
and
familyId in
(
SELECT familyId
FROM `family`
WHERE
assessmentMonth = '2005/10/00'
)
and
(SELECT COUNT(*) FROM person p WHERE
p.familyId = f.familyId
and
p.assessmentMonth = '2006/06/00'
)
>
((SELECT COUNT(*) FROM person p WHERE
p.familyId = f.familyId
and
p.assessmentMonth = '2005/10/00'
)
+ 9
)

====List of all families by assessmentMonth with number of members====
select f.familyId,f.assessmentMonth,f.assessmentDate,count(p.name)
from family f, person p
where
p.familyId = f.familyId
and
p.assessmentMonth = f.assessmentMonth
group by f.familyId,f.assessmentMonth
order by f.familyId,f.assessmentMonth

====FAMILIES THAT WERE INTERVIEWED MORE THAN 3 MONTHS AFTER THE OFFICIAL ASSESSMENT MONTH====

SELECT familyId,assessmentMonth,assessmentDate
FROM `family`
WHERE
abs(DATE_SUB(assessmentDate, INTERVAL 3 MONTH)) > assessmentMonth

====Familes with no members====
SELECT f.familyId,f.assessmentMonth,assessmentDate
FROM `family` f
WHERE
(SELECT COUNT(*) FROM person p WHERE
p.familyId = f.familyId
and
p.assessmentMonth = '2006/06/00'
)
=
0

FPP queries - Revision history

WikiSysop: Created page with "====Number of persons recorded in each assessment==== select count(*), f.community, p.assessmentMonth from person p, family f where f.familyId = p.familyId and f.assessmentMon..."