~ Tiez Notes ~

CREATE VIEW vUsiaAnakAll
as
SELECT ch.child_id, ch.child_name,ch.child_birthday,  ch.customer_id,PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur
FROM children ch inner join customer cu using (customer_id)
WHERE cu.followup_status='1' and ((ch.child_birthday >= (date_sub(curdate(), interval 36 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
order by ch.child_birthday

select aa.umur_anak, count(aa.umur_anak) as jumlah
from
(
select distinct ch.child_id, cu.customer_id, ch.child_birthday,
            PERIOD_DIFF(DATE_FORMAT('2010-11-04','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak
from children ch
inner join  customer cu  on ch.customer_id = cu.customer_id
inner join  calling cl on cl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 36 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
and (ch.child_birthday<> '0000-00-00' or ch.child_birthday like '0001-00-00')
and cl.validation_id='1'
and cu.followup_status = '1'
and cl.calling_strike_id in ('4','5','10','11','13','14','15')
order by ch.child_birthday
) aa
group by aa.umur_anak
order by aa.umur_anak