it's easy but need a few concentration..
one a bit freak query to calculate my client sales commission..
SELECT huhu.* , head.tanggal , head.sales, head.customer
FROM
(
SELECT x.faktur_penjualan_header_no, COALESCE(x.customer_name,z.customer_name) AS customer_name, COALESCE(weight_total2mToUp,'') AS weight_total2mToUp, COALESCE(weight_total2mToDown,'') AS weight_total2mToDown
FROM
(
SELECT zz.faktur_penjualan_header_no, xx.tanggal, xx.tanggal_BayarKomisi, xx.customer_name, xx.sales_code, xx.sales_name, xx.weight_total2mToUp, xx.status_transaksi FROM (
SELECT a.faktur_penjualan_header_no,
CONCAT(SUBSTRING(tanggal,9,2),'/',SUBSTRING(tanggal,6,2),'/',SUBSTRING(tanggal,1,4)) AS tanggal,
tanggal_BayarKomisi,
customer_name,
sales_code,
sales_name,
COALESCE(SUM(e.qty*d.commision_sales),'') AS weight_total2mToUp,
status_transaksi
FROM faktur_penjualan_header a, customer b, sales c , product d , faktur_penjualan_detail e
WHERE a.customer=b.customer_code
AND a.sales=c.sales_code
AND e.product_id=d.product_id
AND e.faktur_penjualan_header_no=a.faktur_penjualan_header_no
AND thickness >= 2
AND status_transaksi = 'LUNAS'
AND tanggal_BayarKomisi IS NULL
GROUP BY e.faktur_penjualan_header_no
) xx
RIGHT JOIN (
SELECT faktur_penjualan_header_no FROM faktur_penjualan_header
WHERE status_transaksi = 'LUNAS' AND tanggal_BayarKomisi IS NULL ) zz
ON xx.faktur_penjualan_header_no=zz.faktur_penjualan_header_no
) X
JOIN
(
SELECT uu.faktur_penjualan_header_no, yy.tanggal, yy.tanggal_BayarKomisi, yy.customer_name, yy.sales_code, yy.sales_name, yy.weight_total2mToDown, yy.status_transaksi
FROM (
SELECT a.faktur_penjualan_header_no,
CONCAT(SUBSTRING(tanggal,9,2),'/',SUBSTRING(tanggal,6,2),'/',SUBSTRING(tanggal,1,4)) AS tanggal,
tanggal_BayarKomisi,
customer_name,
sales_code,
sales_name,
COALESCE(SUM(e.qty*d.commision_sales),'') AS weight_total2mToDown,
status_transaksi
FROM faktur_penjualan_header a, customer b, sales c , product d , faktur_penjualan_detail e
WHERE a.customer=b.customer_code
AND a.sales=c.sales_code
AND e.product_id=d.product_id
AND e.faktur_penjualan_header_no=a.faktur_penjualan_header_no
AND thickness < 2
AND status_transaksi = 'LUNAS'
AND tanggal_BayarKomisi IS NULL
GROUP BY e.faktur_penjualan_header_no
) yy
RIGHT JOIN (
SELECT faktur_penjualan_header_no FROM faktur_penjualan_header
WHERE status_transaksi = 'LUNAS' AND tanggal_BayarKomisi IS NULL ) uu
ON yy.faktur_penjualan_header_no=uu.faktur_penjualan_header_no
) z ON x.faktur_penjualan_header_no=z.faktur_penjualan_header_no
ORDER BY faktur_penjualan_header_no
) huhu , faktur_penjualan_header head
WHERE huhu.faktur_penjualan_header_no=head.faktur_penjualan_header_no
| 150 views
UNITED STATES 
RUSSIAN FEDERATION 
SINGAPORE 
REPUBLIC OF KOREA 
SWEDEN 
CHINA 
