~ Tiez Notes ~

select aa.periode, aa.product_id, count(*) as JumlahLacta, sum(aa.0sd3bln) as 0sd3bln, sum(aa.3sd6bln) as 3sd6bln, sum(aa.6sd9bln) as 6sd9bln,
         sum(aa.9sd12bln) as 9sd12bln, sum(aa.TotOthers) as TotOthers, sum(aa.Success) as Success, sum(aa.NotSuccess) as NotSuccess,
         sum(aa.NotCalling) as NotCalling, sum(aa.NewData) as NewData,
         sum(aa.OldData) as OldData, sum(aa.Users) as Users, sum(aa.Prospects) as Prospects,
         sum(aa.DO) as TotalDO, sum(aa.DO0sd3bln) as DO0sd3bln, sum(aa.DO3sd6bln) as DO3sd6bln, sum(aa.DO6sd9bln) as DO6sd9bln, sum(aa.DO9sd12bln) as DO9sd12bln,
         sum(aa.DOlbh12) as OhtersDOLbh12,
         sum(aa.TotalFO) as TotalFO,
         sum(aa.FO0sd3bln) as FO0sd3bln, sum(aa.FO3sd6bln) as FO3sd6bln, sum(aa.FO6sd9bln) as FO6sd9bln, sum(aa.FO9sd12bln) as FO9sd12bln,
         sum(aa.FOlbh12) as OhtersFOLbh12,
         (sum(aa.Users) +  sum(aa.Prospects) + sum(aa.DO) + sum(aa.TotalFO)) as Total
from
(
select date_format(cu.submit_date,'%Y%m') as Periode, pl.product_id, pl.customer_id, cu.customer_name, cu.customer_address, cu.customer_phone, cu.customer_city,
         ca.phone_date, ca.calling_for_id, ca.calling_strike_id, ca.calling_target_id, (case when ca.calling_strike_id = '12' then 1 else 0 end) as DO,
         ca.calling_result_id, (case when ca.calling_result_id = '1' then 1 else 0 end) as Success, (case when ca.calling_result_id = '2' then 1 else 0 end) as NotSuccess,
         (case when ca.calling_result_id is null or '' then 1 else 0 end) as NotCalling,
         (case when date_format(cu.recruit_date,'%Y%m') = '201001' then 1 else 0 end) as NewData,
         (case when date_format(cu.recruit_date,'%Y%m') < '201001' then 1 else 0 end) as OldData,
         (case when ca.calling_strike_id = '10' then 1 else 0 end) as Users,
         (case when ca.calling_strike_id = '13' then 1 else 0 end) as Prospects,
         ch.child_name, ch.child_birthday, period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) as selisih_bulan,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as 0sd3bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as 3sd6bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as 6sd9bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as 9sd12bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 end) as TotOthers,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as DO0sd3bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as DO3sd6bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as DO6sd9bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as DO9sd12bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as DOlbh12,
         (case when ca.calling_strike_id = '14' then 1 else 0 end) as TotalFO,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as FO0sd3bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as FO3sd6bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as FO6sd9bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as FO9sd12bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as FOlbh12
from product_log pl, customer cu, calling ca, children ch
where pl.product_id = 'lacta' and date_format(cu.submit_date,'%Y%m') = '201001' and cu.followup_status = '1' and
          pl.customer_id = cu.customer_id and pl.customer_id = ca.customer_id and
          ca.calling_target_id = '1' and pl.customer_id = ch.customer_id and ch.child_birthday <> ' ' and
          ch.child_birthday > '0000-00-00' and ca.calling_strike_id > 0
order by pl.customer_id
) as aa
group by aa.periode, aa.product_id
order by aa.periode