REPORTFORM;SC5.frf
TITLE;วิเคราะห์ต้นทุนขาย ทั้งปีแยกตามลูกค้า (แสดง 12 เดือน)
SQL;select invcard.DATEDOC, invcard.ACCID, invcard.DOCNO,
SQL;armst.XCOMP, invcard.SALEID, salemst.SNAME, arcard.REFNO, 
SQL;invcard.ID, invmst.XDESC,invcard.COST, invmst.UNIT, invcard.PRICE, invcard.QUAN,
SQL;invcard.SALEPURAMT, invcard.COSTAMT,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 1,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH1,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 2,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH2,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 3,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH3,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 4,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH4,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 5,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH5,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 6,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH6,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 7,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH7,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 8,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH8,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 9,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH9,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 10,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH10,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 11,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH11,
SQL;SUM(IF(MONTH(invcard.DATEDOC) = 12,invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)   ,0)) as MTH12,
SQL;SUM(invcard.SALEPURAMT - IF(LEFT(invcard.DOCNO,2)="RI",invcard.costamt * -1,invcard.COSTAMT)) as XTOTAL
SQL;from invcard 
SQL;left join armst on invcard.ACCID = armst.ACCID
SQL;left join salemst on invcard.SALEID = salemst.SID
SQL;left join arcard on invcard.DOCNO = arcard.DOCNO
SQL;left join invmst on invcard.ID = invmst.ID
SQL;where trtype2 = 'SA'
SQL;group by 2,4
SQL;order by invcard.ACCID
CONDITION;ตั้งแต่รหัสลูกค้า~INVCARD.ACCID~Chr~2~SELECT ACCID FROM ARMST~AR
CONDITION;ตั้งแต่วันที่~INVCARD.DATEDOC~Date~2~~
