FPP queries

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