SELECT a.itemcode,a.iname,a.ibrand,a.igroup,
SUM(CASE WHEN b.zone ='B' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_B1,
SUM(CASE WHEN b.zone ='B' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_B2,
SUM(CASE WHEN b.zone ='B' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_B3,
SUM(CASE WHEN b.zone ='B' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_B4,
SUM(CASE WHEN b.zone ='B' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_B5,
SUM(CASE WHEN b.zone ='C' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_C1,
SUM(CASE WHEN b.zone ='C' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_C2,
SUM(CASE WHEN b.zone ='C' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_C3,
SUM(CASE WHEN b.zone ='C' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_C4,
SUM(CASE WHEN b.zone ='C' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_C5,
SUM(CASE WHEN b.zone ='E' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_E1,
SUM(CASE WHEN b.zone ='E' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_E2,
SUM(CASE WHEN b.zone ='E' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_E3,
SUM(CASE WHEN b.zone ='E' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_E4,
SUM(CASE WHEN b.zone ='E' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_E5,
SUM(CASE WHEN b.zone ='N' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_N1,
SUM(CASE WHEN b.zone ='N' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_N2,
SUM(CASE WHEN b.zone ='N' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_N3,
SUM(CASE WHEN b.zone ='N' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_N4,
SUM(CASE WHEN b.zone ='N' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_N5,
SUM(CASE WHEN b.zone ='N/E' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_NE1,
SUM(CASE WHEN b.zone ='N/E' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_NE2,
SUM(CASE WHEN b.zone ='N/E' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_NE3,
SUM(CASE WHEN b.zone ='N/E' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_NE4,
SUM(CASE WHEN b.zone ='N/E' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_NE5,
SUM(CASE WHEN b.zone ='S' and b.orderdate BETWEEN '20170701' and '20170710' THEN b.qty_ctn END) as sum_S1,
SUM(CASE WHEN b.zone ='S' and b.orderdate BETWEEN '20170701' and '20170715' THEN b.qty_ctn END) as sum_S2,
SUM(CASE WHEN b.zone ='S' and b.orderdate BETWEEN '20170701' and '20170720' THEN b.qty_ctn END) as sum_S3,
SUM(CASE WHEN b.zone ='S' and b.orderdate BETWEEN '20170701' and '20170725' THEN b.qty_ctn END) as sum_S4,
SUM(CASE WHEN b.zone ='S' and b.orderdate BETWEEN '20170701' and '20170731' THEN b.qty_ctn END) as sum_S5
FROM itemforecast a
INNER JOIN transale b on a.itemcode = b.itemno
WHERE b.orderdate LIKE '201707%' and b.channel in ('CR') and a.ibrand <> 'Z'
GROUP BY a.itemcode,a.iname,a.ibrand
ORDER BY a.ibrand,a.igroup,a.iweight,a.order_byรายงานยอดขาย SALE