REPORTFORM;AR5_3.frf
TITLE;ยอดหนี้คงเหลือ เรียงตามรหัสลูกหนี้ (แบบที่3)
SQL;DROP TABLE IF EXISTS _XARBAL&
SQL;
SQL;CREATE TABLE _XARBAL
SQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE, ARCARD.INVAMT, 
SQL;ARCARD.VATAMT, ARCARD.ADVAMT, ARCARD.DISCD,  
SQL;REPBAL as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL, ARCARD.ORDPONO, 
SQL;ARCARD.BILLDOC
SQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;where ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;and ARCARD.DATEDOC <= :M21 
SQL;and left(trtype,2) = "AR"
SQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
SQL;
SQL;INSERT INTO _XARBAL
SQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE, ARCARD.INVAMT, 
SQL;ARCARD.VATAMT, ARCARD.ADVAMT, ARCARD.DISCD,  
SQL;0000000000.00 as xdeby,0000000000.00 as xrec,repbal * -1 as xdisc, 0000000000.00 as xBAL, ARCARD.ORDPONO, 
SQL;ARCARD.BILLDOC
SQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
SQL;where  left(ARCARD.trtype,2) = "DR"
SQL;and ( left(ARCARD.DISCTYPE,2) = "03")
SQL;and ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;and ARCARD.DATEDOC <= :M21 
SQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL2&
SQL;
SQL;CREATE TABLE _XARBAL2
SQL;SELECT REFNO ,
SQL;SUM(REPBAL * -1) AS XREC
SQL;FROM ARCARD
SQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;AND ARCARD.DATEDOC  <=  :M21 
SQL;AND LEFT(TRTYPE,2) = 'BR'
SQL;GROUP BY REFNO&
SQL;
SQL;INSERT INTO _XARBAL2
SQL;SELECT REFNO ,
SQL;SUM(REPBAL * -1) AS XREC
SQL;FROM ARCARD
SQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
SQL;AND ARCARD.DATEDOC  <=  :M21 
SQL;AND LEFT(TRTYPE,2) = 'DR' AND LEFT(DISCTYPE,2) = '01'
SQL;GROUP BY REFNO&
SQL;
SQL;
SQL;DROP TABLE IF EXISTS _XARBAL3&
SQL;CREATE TABLE _XARBAL3
SQL;SELECT REFNO,SUM(XREC) AS XREC FROM _XARBAL2
SQL;GROUP BY REFNO&
SQL;
SQL;
SQL;UPDATE _XARBAL INNER JOIN _XARBAL3 ON _XARBAL.DOCNO = _XARBAL3.REFNO
SQL;SET _XARBAL.XREC = _XARBAL3.XREC&
SQL;
SQL;DROP TABLE _XARBAL2&
SQL;DROP TABLE _XARBAL3&
SQL;
SQL;UPDATE _XARBAL SET XBAL = XDEBY-XREC-XDISC&
SQL;
SQL;SELECT * FROM _XARBAL ORDER BY ACCID,DATEDOC,DOCNO
CONDITION;ตั้งแต่รหัสลูกค้า~ARCARD.ACCID~Chr~2~SELECT ACCID FROM ARCARD~AR
CONDITION;สิ้นสุด ณ วันที่~ARCARD.DATEDOC~Date~1~~
EXCELSQL;DROP TABLE IF EXISTS _XARBAL&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL
EXCELSQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE, ARCARD.INVAMT, 
EXCELSQL;ARCARD.VATAMT, ARCARD.ADVAMT, ARCARD.DISCD,  
EXCELSQL;REPBAL as xdeby,0000000000.00 as xrec,0000000000.00 as xdisc, 0000000000.00 as xBAL, ARCARD.ORDPONO, 
EXCELSQL;ARCARD.BILLDOC
EXCELSQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;where ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;and ARCARD.DATEDOC <= :M21 
EXCELSQL;and left(trtype,2) = "AR"
EXCELSQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
EXCELSQL;
EXCELSQL;INSERT INTO _XARBAL
EXCELSQL;SELECT ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO, ARCARD.DATEDOC, ARCARD.DUEDATE, ARCARD.INVAMT, 
EXCELSQL;ARCARD.VATAMT, ARCARD.ADVAMT, ARCARD.DISCD,  
EXCELSQL;0000000000.00 as xdeby,0000000000.00 as xrec,repbal * -1 as xdisc, 0000000000.00 as xBAL, ARCARD.ORDPONO, 
EXCELSQL;ARCARD.BILLDOC
EXCELSQL;FROM ARCARD INNER JOIN ARMST ON ARCARD.ACCID = ARMST.ACCID
EXCELSQL;where  left(ARCARD.trtype,2) = "DR"
EXCELSQL;and ( left(ARCARD.DISCTYPE,2) = "03")
EXCELSQL;and ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;and ARCARD.DATEDOC <= :M21 
EXCELSQL;GROUP BY ARCARD.ACCID, ARMST.XCOMP, ARCARD.DOCNO&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL2&
EXCELSQL;
EXCELSQL;CREATE TABLE _XARBAL2
EXCELSQL;SELECT REFNO ,
EXCELSQL;SUM(REPBAL * -1) AS XREC
EXCELSQL;FROM ARCARD
EXCELSQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;AND ARCARD.DATEDOC  <=  :M21 
EXCELSQL;AND LEFT(TRTYPE,2) = 'BR'
EXCELSQL;GROUP BY REFNO&
EXCELSQL;
EXCELSQL;INSERT INTO _XARBAL2
EXCELSQL;SELECT REFNO ,
EXCELSQL;SUM(REPBAL * -1) AS XREC
EXCELSQL;FROM ARCARD
EXCELSQL;WHERE  ARCARD.ACCID >= :M11 AND ARCARD.ACCID <= :M12
EXCELSQL;AND ARCARD.DATEDOC  <=  :M21 
EXCELSQL;AND LEFT(TRTYPE,2) = 'DR' AND LEFT(DISCTYPE,2) = '01'
EXCELSQL;GROUP BY REFNO&
EXCELSQL;
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _XARBAL3&
EXCELSQL;CREATE TABLE _XARBAL3
EXCELSQL;SELECT REFNO,SUM(XREC) AS XREC FROM _XARBAL2
EXCELSQL;GROUP BY REFNO&
EXCELSQL;
EXCELSQL;
EXCELSQL;UPDATE _XARBAL INNER JOIN _XARBAL3 ON _XARBAL.DOCNO = _XARBAL3.REFNO
EXCELSQL;SET _XARBAL.XREC = _XARBAL3.XREC&
EXCELSQL;
EXCELSQL;DROP TABLE _XARBAL2&
EXCELSQL;DROP TABLE _XARBAL3&
EXCELSQL;
EXCELSQL;UPDATE _XARBAL SET XBAL = XDEBY-XREC-XDISC&
EXCELSQL;
EXCELSQL;SELECT * FROM _XARBAL ORDER BY ACCID,DATEDOC,DOCNO
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
