REPORTFORM;TBAL2_1.frf
TITLE;งบทดลองแยก แผนก
SQL;drop table if exists _gltr&
SQL;create table _gltr
SQL;select
SQL;gltr.glid, glmst.GLDESC ,gltype,dptid,
SQL;sum(debit) as debitbeg,
SQL;sum(credit) as creditbeg,
SQL;0000000000.00 as debit,
SQL;0000000000.00 as credit,
SQL;0000000000.00 as debitbal,
SQL;0000000000.00 as creditbal,
SQL;0 as line
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;where gltr.datedoc < :m11 
SQL;and gltr.dptid >= :m21 and gltr.dptid <= :m22
SQL;group by 1,2,3,4&
SQL;
SQL;alter table _gltr add index dpti (dptid)& 
SQL;alter table _gltr add index glid (glid)&
SQL;
SQL;
SQL;insert into _gltr
SQL;select
SQL;gltr.glid, glmst.GLDESC ,gltype,dptid,
SQL;0.00 as debitbeg,
SQL;0.00 as creditbeg,
SQL;sum(debit) as debit,
SQL;sum(credit) as credit,
SQL;0000000000.00 as debitbal,
SQL;0000000000.00 as creditbal,
SQL;0 as line
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;where gltr.datedoc >= :m11 and gltr.datedoc <= :m12
SQL;and gltr.dptid >= :m21 and gltr.dptid <= :m22
SQL;group by 1,2,3,4&
SQL;
SQL;drop table if exists _gltr0&
SQL;create table _gltr0
SQL;select  
SQL;glid, GLDESC ,gltype,dptid,
SQL;sum(debitbeg) as debitbeg,
SQL;sum(creditbeg) as creditbeg,
SQL;sum(debit) as debit,
SQL;sum(credit) as credit,
SQL;sum(debitbal) as debitbal,
SQL;sum(creditbal) as creditbal,
SQL;0000000000.00 as begbal,
SQL;0000000000.00 as balbal
SQL;from _gltr 
SQL;group by 1,2,3,4
SQL;order by glid&
SQL;
SQL;update _gltr0 set begbal = debitbeg - creditbeg where instr('ACE',left(gltype,1))&
SQL;update _gltr0 set begbal = creditbeg - debitbeg where not instr('ACE',left(gltype,1))&
SQL;update _gltr0 set balbal = begbal + debit - credit where instr('ACE',left(gltype,1))&
SQL;update _gltr0 set balbal = begbal + credit - debit where not instr('ACE',left(gltype,1))&
SQL;
SQL;update _gltr0 set debitbeg = 0, creditbeg = 0&
SQL;update _gltr0 set debitbeg = begbal  where instr('ACE',left(gltype,1)) and begbal > 0&
SQL;update _gltr0 set creditbeg = abs(begbal) where instr('ACE',left(gltype,1)) and begbal < 0&
SQL;update _gltr0 set debitbeg = abs(begbal) where not instr('ACE',left(gltype,1)) and begbal < 0&
SQL;update _gltr0 set creditbeg = begbal where not instr('ACE',left(gltype,1)) and begbal > 0&
SQL;
SQL;update _gltr0 set debitbal = balbal where instr('ACE',left(gltype,1)) and balbal > 0&
SQL;update _gltr0 set creditbal = abs(balbal) where instr('ACE',left(gltype,1)) and balbal < 0&
SQL;update _gltr0 set debitbal = abs(balbal) where not instr('ACE',left(gltype,1)) and balbal < 0&
SQL;update _gltr0 set creditbal = balbal where not instr('ACE',left(gltype,1)) and balbal > 0&
SQL;
SQL;
SQL;select glid, gldesc,
SQL;gltype,dptid,
SQL;sum(debitbal) as BDB, sum(creditbal) as BCR,
SQL;sum(debit) as DB, sum(credit) as CR,
SQL;SUM(DEBITBEG) AS XDB,SUM(CREDITBEG) AS XCR,depart.DDESC
SQL;from _gltr0 left join depart on _gltr0.DPTID = depart.DID
SQL;GROUP BY GLID,gldesc,GLTYPE
SQL;order by DPTID,GLID
CONDITION;ตั้งแต่วันที่~GLTR.DATEDOC~Date~2~~
CONDITION;ตั้งแต่รหัสแผนก~GLTR.DPTID~Chr~2~SELECT DID FROM DEPART~DEP
EXCELSQL;drop table if exists _gltr&
EXCELSQL;create table _gltr
EXCELSQL;select
EXCELSQL;gltr.glid, glmst.GLDESC ,gltype,dptid,
EXCELSQL;sum(debit) as debitbeg,
EXCELSQL;sum(credit) as creditbeg,
EXCELSQL;0000000000.00 as debit,
EXCELSQL;0000000000.00 as credit,
EXCELSQL;0000000000.00 as debitbal,
EXCELSQL;0000000000.00 as creditbal,
EXCELSQL;0 as line
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where gltr.datedoc < :m11 
EXCELSQL;and gltr.dptid >= :m21 and gltr.dptid <= :m22
EXCELSQL;group by 1,2,3,4&
EXCELSQL;
EXCELSQL;alter table _gltr add index dpti (dptid)& 
EXCELSQL;alter table _gltr add index glid (glid)&
EXCELSQL;
EXCELSQL;
EXCELSQL;insert into _gltr
EXCELSQL;select
EXCELSQL;gltr.glid, glmst.GLDESC ,gltype,dptid,
EXCELSQL;0.00 as debitbeg,
EXCELSQL;0.00 as creditbeg,
EXCELSQL;sum(debit) as debit,
EXCELSQL;sum(credit) as credit,
EXCELSQL;0000000000.00 as debitbal,
EXCELSQL;0000000000.00 as creditbal,
EXCELSQL;0 as line
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where gltr.datedoc >= :m11 and gltr.datedoc <= :m12
EXCELSQL;and gltr.dptid >= :m21 and gltr.dptid <= :m22
EXCELSQL;group by 1,2,3,4&
EXCELSQL;
EXCELSQL;drop table if exists _gltr0&
EXCELSQL;create table _gltr0
EXCELSQL;select  
EXCELSQL;glid, GLDESC ,gltype,dptid,
EXCELSQL;sum(debitbeg) as debitbeg,
EXCELSQL;sum(creditbeg) as creditbeg,
EXCELSQL;sum(debit) as debit,
EXCELSQL;sum(credit) as credit,
EXCELSQL;sum(debitbal) as debitbal,
EXCELSQL;sum(creditbal) as creditbal,
EXCELSQL;0000000000.00 as begbal,
EXCELSQL;0000000000.00 as balbal
EXCELSQL;from _gltr 
EXCELSQL;group by 1,2,3,4
EXCELSQL;order by glid&
EXCELSQL;
EXCELSQL;update _gltr0 set begbal = debitbeg - creditbeg where instr('ACE',left(gltype,1))&
EXCELSQL;update _gltr0 set begbal = creditbeg - debitbeg where not instr('ACE',left(gltype,1))&
EXCELSQL;update _gltr0 set balbal = begbal + debit - credit where instr('ACE',left(gltype,1))&
EXCELSQL;update _gltr0 set balbal = begbal + credit - debit where not instr('ACE',left(gltype,1))&
EXCELSQL;
EXCELSQL;update _gltr0 set debitbeg = 0, creditbeg = 0&
EXCELSQL;update _gltr0 set debitbeg = begbal  where instr('ACE',left(gltype,1)) and begbal > 0&
EXCELSQL;update _gltr0 set creditbeg = abs(begbal) where instr('ACE',left(gltype,1)) and begbal < 0&
EXCELSQL;update _gltr0 set debitbeg = abs(begbal) where not instr('ACE',left(gltype,1)) and begbal < 0&
EXCELSQL;update _gltr0 set creditbeg = begbal where not instr('ACE',left(gltype,1)) and begbal > 0&
EXCELSQL;
EXCELSQL;update _gltr0 set debitbal = balbal where instr('ACE',left(gltype,1)) and balbal > 0&
EXCELSQL;update _gltr0 set creditbal = abs(balbal) where instr('ACE',left(gltype,1)) and balbal < 0&
EXCELSQL;update _gltr0 set debitbal = abs(balbal) where not instr('ACE',left(gltype,1)) and balbal < 0&
EXCELSQL;update _gltr0 set creditbal = balbal where not instr('ACE',left(gltype,1)) and balbal > 0&
EXCELSQL;
EXCELSQL;
EXCELSQL;select glid, gldesc,
EXCELSQL;gltype,dptid,
EXCELSQL;sum(debitbal) as BDB, sum(creditbal) as BCR,
EXCELSQL;sum(debit) as DB, sum(credit) as CR,
EXCELSQL;SUM(DEBITBEG) AS XDB,SUM(CREDITBEG) AS XCR,depart.DDESC
EXCELSQL;from _gltr0 left join depart on _gltr0.DPTID = depart.DID
EXCELSQL;GROUP BY GLID,gldesc,GLTYPE
EXCELSQL;order by DPTID,GLID
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
