SHARE
TWEET

Untitled

a guest Oct 22nd, 2019 91 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT month_target AS bulan,SUM(b.`target_sales`) AS target_month,SUM(c.`sales_2012`) AS last_month,
  2.             COALESCE(total_sales,
  3. 0)AS actual, COALESCE(total_point,
  4. 0)AS regular, COALESCE(bonus_point,
  5. 0)AS bonus,
  6.             COALESCE((total_point+COALESCE(bonus_point,
  7. 0)),
  8. 0)AS total,COALESCE(g.rebate,
  9. 0)AS rebate,COALESCE(g.rebate_pf,
  10. 0)AS rebate_pf
  11.            
  12.             FROM mstr_outlet a
  13.             LEFT JOIN mstr_sales_target b ON a.`outlet_id` = b.`outlet_id`
  14.             LEFT JOIN mstr_sales_2012 c ON c.`outlet_id` = a.`outlet_id` AND b.`month_target` = c.`month_2012`
  15.             LEFT JOIN (SELECT e.pulau_id_alias,e.nama_pulau_alias,MONTHNAME(tgl_transaksi)AS bulan, SUM(sales)AS total_sales,
  16.              SUM(point_satuan)AS total_point
  17.             FROM trx_transaksi a
  18.             INNER JOIN mstr_outlet b ON a.no_id = b.outlet_id
  19.             INNER JOIN ms_city_alias c ON b.city_id_alias = c.city_id_alias
  20.             INNER JOIN mstr_distributor d ON b.distributor_id = d.distributor_id
  21.             INNER JOIN ms_pulau_alias e ON e.pulau_id_alias = b.region_id
  22.             INNER JOIN trx_transaksi_barang g ON g.kd_transaksi = a.kd_transaksi WHERE a.status='A'AND d.distributor_id  = '200053'
  23.             GROUP BY bulan )d ON d.bulan = b.month_target
  24.             LEFT JOIN (SELECT b.outlet_id,MONTHNAME(tgl_transaksi)AS bulan,SUM(point_satuan)AS bonus_point
  25.             FROM trx_transaksi a
  26.             INNER JOIN trx_transaksi_barang i ON a.kd_transaksi = i.kd_transaksi
  27.             LEFT JOIN mstr_outlet b ON b.outlet_id = a.no_id
  28.             LEFT JOIN ms_city_alias c ON b.city_id_alias = c.city_id_alias
  29.             LEFT JOIN mstr_distributor d ON b.distributor_id = d.distributor_id
  30.             LEFT JOIN ms_pulau_alias e ON e.pulau_id_alias = b.region_id WHERE STATUS='B' AND d.distributor_id  = '200053'
  31.             GROUP BY bulan)e ON e.bulan = b.month_target
  32.             LEFT JOIN ( SELECT MONTHNAME(tgl_transaksi)AS bulan, e.pulau_id_alias FROM trx_transaksi a
  33.             INNER JOIN mstr_outlet b ON a.no_id = b.outlet_id
  34.             INNER JOIN ms_city_alias c ON b.city_id_alias = c.city_id_alias
  35.             INNER JOIN mstr_distributor d ON b.distributor_id = d.distributor_id
  36.             INNER JOIN ms_pulau_alias e ON e.pulau_id_alias = b.region_id
  37.             INNER JOIN trx_transaksi_barang f ON f.kd_transaksi = a.kd_transaksi
  38.             INNER JOIN mstr_item g ON g.item_code = f.kd_produk
  39.             INNER JOIN mstr_signature h ON h.kode_signature = g.kode_signature
  40.             INNER JOIN mstr_category i ON i.kode_category = h.kode_category WHERE d.distributor_id = '200053' GROUP BY bulan )f ON
  41.             f.bulan = b.month_target
  42.             LEFT JOIN (
  43.             SELECT bulan,rebate,rebate_pf FROM `trx_rebate_list_bulan1` a
  44.             INNER JOIN mstr_outlet b ON a.outlet_id = b.outlet_id
  45.             WHERE  a.distributor_id = '200053'
  46.             UNION
  47.             SELECT bulan,rebate,rebate_pf FROM `trx_rebate_list_bulan2` a
  48.             INNER JOIN mstr_outlet b ON a.outlet_id = b.outlet_id
  49.             WHERE  a.distributor_id = '200053'
  50.             UNION
  51.             SELECT bulan,rebate,rebate_pf FROM `trx_rebate_list_bulan3` a
  52.             INNER JOIN mstr_outlet b ON a.outlet_id = b.outlet_id
  53.             WHERE  a.distributor_id = '200053'
  54.             UNION          
  55.             SELECT bulan,rebate,rebate_pf FROM `trx_rebate_list_bulan4` a
  56.             INNER JOIN mstr_outlet b ON a.outlet_id = b.outlet_id
  57.             WHERE  a.distributor_id = '200053')g ON g.bulan = b.month_target
  58.                
  59.             WHERE b.distributor_id ='200053' GROUP BY b.month_target ORDER BY b.month_target DESC
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top