~ Tiez Notes ~

-- Data Position Anak dgn Produk
-- select aa.umur_anak, aa.product_id, count(aa.umur_anak) as jumlah
-- from
-- (
select ch.child_id, cu.customer_id, ch.child_name, ch.child_birthday,
         -- PERIOD_DIFF(DATE_FORMAT('2010-11-30','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak,
         ((datediff(curdate(), ch.child_birthday)) / 30)  as umur_anak,
         -- (case when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 0 and 6 then 'IF1'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 7 and 12 then 'IF2'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 13 and 36 then 'ESS3'
         -- end) as product_id,
         -- (case when ((datediff(curdate(), ch.child_birthday)) / 30)  >= 0 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 6  then 'IF1'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 6   and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 12 then 'IF2'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 12 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 36 then 'ESS3'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 36 then 'ESS4'
         -- end) as product_id,
         pl.product_id,
         cl.calling_result_id,
         cu.customer_address, cu.customer_phone, cu.customer_phone_2
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
inner join product_log pl on pl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 37 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_result_id = '1'
and pl.consumed_by = ch.child_id
and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('6', '12', '16')
and pl.product_id in ('if1', 'if2', 'ess3', 'ess4')
group by ch.child_id
order by ch.child_id, ch.child_birthday
-- limit 60000,60000
-- ) aa
-- group by aa.umur_anak, aa.product_id
-- order by aa.umur_anak