เป้าหมายประชากร Type 1,3 ปีงบประมาณ 2559
SELECT v.village_moo,v.village_name ,
(SELECT COUNT(DISTINCT concat(p.house_id))from person p
where v.village_id = p.village_id and v.village_moo <> 0) AS c_house,
(SELECT COUNT(DISTINCT concat( p.family_id))from person p
where v.village_id = p.village_id and v.village_moo <> 0) AS c_family ,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N'
and p.nationality = 99) AS p_total_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N' and sex =1
and p.nationality = 99) AS man_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N' and sex =2
and p.nationality = 99) AS woman_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N'
and p.nationality = 99) AS p_total,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and sex =1
and p.nationality = 99) AS man,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and sex =2
and p.nationality = 99) AS woman,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
inner JOIN person_anc pa on pa.person_id = p.person_id
where v.village_id = p.village_id and v.village_moo <> 0 and pa.lmp BETWEEN '2014-10-01' and '2015-09-30' and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p_anc',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
inner JOIN person_deformed pd on pd.person_id = p.person_id
where v.village_id = p.village_id and v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p_dedormed',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN person_death pd on pd.person_id = p.person_id
where v.village_id = p.village_id and pd.death_date BETWEEN '2014-10-01' and '2015-09-30'
) AS p_death,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 0 and 12 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p0_1y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 0 and 72 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p0_5y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 73 and 216 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p6_18y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 35 and 300 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p35y<',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 60 and 300 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p60y<',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 30 and 60 and sex=2 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p30-60y',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
where v.village_id = p.village_id and v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p30-70y',DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS com_lastupdate
FROM person p
LEFT JOIN village v on p.village_id = v.village_id
LEFT OUTER JOIN person_death pd on pd.person_id = p.person_id
where v.village_moo <> 0
GROUP BY v.village_id union
SELECT ' ','total' ,
(SELECT COUNT(DISTINCT concat(p.house_id))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0) AS c_house,
(SELECT COUNT(DISTINCT concat( p.family_id))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0) AS c_family ,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N'
and p.nationality = 99) AS p_total_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N' and sex =1
and p.nationality = 99) AS man_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and p.house_regist_type_id IN (1,3) and
p.person_discharge_id = 9 AND p.death ='N' and sex =2
and p.nationality = 99) AS woman_t13,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N'
and p.nationality = 99) AS p_total,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and sex =1
and p.nationality = 99) AS man,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and
p.person_discharge_id = 9 AND p.death ='N' and sex =2
and p.nationality = 99) AS woman,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
inner JOIN person_anc pa on pa.person_id = p.person_id
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and pa.lmp BETWEEN '2014-10-01' and '2015-09-30' and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p_anc',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
INNER JOIN person_deformed pd on pd.person_id = p.person_id
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p_dedormed',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
LEFT JOIN person_death pd on pd.person_id = p.person_id
where pd.death_date BETWEEN '2014-10-01' and '2015-09-30' ) AS p_death,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 0 and 12 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p0_1y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 0 and 72 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p0_5y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(month,p.birthdate,now())between 73 and 216 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS p6_18y,
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 35 and 300 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p35y<',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 60 and 300 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p60y<',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 30 and 60 and sex=2 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p30-60y',
(SELECT COUNT(DISTINCT concat(p.cid))from person p
LEFT JOIN village v on p.village_id = v.village_id
where v.village_moo <> 0 and TIMESTAMPDIFF(year,p.birthdate,now())between 30 and 70 and sex=2 and
p.person_discharge_id = 9 AND p.death ='N' and p.nationality = 99) AS 'p30-70y',
DATE_FORMAT(NOW(),'%d-%m-%Y %H:%i:%s') AS com_lastupdate
FROM person p
LEFT JOIN village v on p.village_id = v.village_id
LEFT OUTER JOIN person_death pd on pd.person_id = p.person_id
where v.village_moo <> 0
GROUP BY v.village_id
ไม่มีความคิดเห็น:
แสดงความคิดเห็น