~ Tiez Notes ~

select customer_id, count(*)
from calling
group by customer_id
having count(*) > 1
order by customer_id

select cu.customer_id as ID,cu.customer_name as NAMA,cu.customer_phone as PHONE1,cu.customer_phone_2 as PHONE2,cu.customer_address as ALAMAT, pl.product_id as PRODUK,cs.calling_strike_name,(select max(calling.phone_date)
from calling where calling.customer_id=cu.customer_id and calling.calling_target_name=pl.consumed_by) as telepon  from customer cu
inner join product_log pl on pl.customer_id=cu.customer_id
inner join calling ca on ca.customer_id=cu.customer_id
inner join calling_strike cs on cs.calling_strike_id=ca.calling_strike_id
where ca.calling_strike_id='10'
and pl.product_id='lacta'
and cu.submit_date<='2010-12-09'
and pl.consumed_by=cu.customer_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 ('12', '17')
group by cu.customer_id
order by cu.customer_name

select aa.umur_anak, 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, 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
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 (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 ('12', '17')
group by ch.child_id
order by ch.child_id, ch.child_birthday
-- limit 60000,60000
) aa
group by aa.umur_anak
order by aa.umur_anak

-- Data Position utk materna
select cu.customer_id, cu.customer_name, cu.customer_phone, pl.product_id, ch.child_expected_birthday, ca.calling_strike_id
from customer cu, calling ca, product_log pl, children ch
where cu.customer_id = ca.customer_id
          and ca.validation_id = '1'
          and cu.customer_id = pl.customer_id
          and cu.customer_id = ch.customer_id
          and pl.product_id = 'materna'
          and pl.consumed_by = cu.customer_id
          and cu.followup_status = '1'
          and cu.loyalty1_by <> " "
          -- and ca.calling_strike_id <> '12'      
          and ca.calling_result_id = '1'
          and ca.calling_for_id = '3'
          -- and ca.calling_strike_id in ('4','5','10','11','13','14','15')
          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 ('12', '17')
          and ch.child_expected_birthday <> '0000-00-00'
          and cu.submit_date <= curdate()
          -- and cu.customer_id = '01080800030023'
group by cu.customer_id
order by cu.customer_id