REPORTFORM;exp2.frf
TITLE;รายงานค่าใช้จ่าย Job/งบประมาณ 
SQL;drop table if exists _exp&
SQL;
SQL;create table _exp
SQL;select exptail.JOBNO, exptail.GLID, jobmst.JDESC, glmst.GLDESC, sum(exptail.INVAMT) as AMT,0000000000.00 as bud
SQL;from exptail left join jobmst on exptail.JOBNO = jobmst.JOBNO
SQL;left join glmst on exptail.GLID = glmst.GLID
SQL;where exptail.jobno >= :m11 and exptail.jobno <= :m12
SQL;and year(datedoc) = :m21
SQL;and month(datedoc) = :m31
SQL;group by exptail.jobno,exptail.GLID,jobmst.Jdesc, glmst.GLDESC
SQL;&
SQL;
SQL;insert into _exp
SQL;select  
SQL;aptrtail.JOB,aptrtail.glid, jobmst.JDESC, glmst.GLDESC, sum(aptrtail.INVAMT) as AMT,0000000000.00 as bud
SQL;from aptrtail inner join apcard on aptrtail.docno = apcard.DOCNO
SQL;left join jobmst on aptrtail.JOB = jobmst.JOBNO
SQL;left join glmst on aptrtail.glid = glmst.GLID
SQL;where aptrtail.job >= :m11 and aptrtail.job <= :m12
SQL;and year(apcard.datedoc) = :m21
SQL;and month(apcard.datedoc) = :m31
SQL;
SQL;group by aptrtail.JOB,aptrtail.glid, jobmst.JDESC, glmst.GLDESC&
SQL;
SQL;drop table if exists _exp2&
SQL;create table _exp2
SQL;select jobno,glid,jdesc,gldesc,sum(amt) as amt,sum(bud) as bud
SQL;from _exp
SQL;group by jobno,glid,jdesc,gldesc&
SQL;
SQL;update _exp2 inner join jobtail on (_exp2.jobno = jobtail.jobno and _exp2.glid = jobtail.glid)
SQL;set bud = jobtail.budget&
SQL;
SQL;select * from _exp2 order by jobno,glid
CONDITION;รหัส Job~EXPTAIL.JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
CONDITION;ปี ( ค.ศ.)~EXPTAIL.DOCNO~Chr~1~select year(sdatestart) as xyear from config~
CONDITION;เดือน~EXPTAIL.DOCNO~Chr~1~select month(now()) as xmth from config~
EXCELSQL;drop table if exists _exp&
EXCELSQL;
EXCELSQL;create table _exp
EXCELSQL;select exptail.JOBNO, exptail.GLID, jobmst.JDESC, glmst.GLDESC, sum(exptail.INVAMT) as AMT,0000000000.00 as bud
EXCELSQL;from exptail left join jobmst on exptail.JOBNO = jobmst.JOBNO
EXCELSQL;left join glmst on exptail.GLID = glmst.GLID
EXCELSQL;where exptail.jobno >= :m11 and exptail.jobno <= :m12
EXCELSQL;and year(datedoc) = :m21
EXCELSQL;and month(datedoc) = :m31
EXCELSQL;group by exptail.jobno,exptail.GLID,jobmst.Jdesc, glmst.GLDESC
EXCELSQL;&
EXCELSQL;
EXCELSQL;insert into _exp
EXCELSQL;select  
EXCELSQL;aptrtail.JOB,aptrtail.glid, jobmst.JDESC, glmst.GLDESC, sum(aptrtail.INVAMT) as AMT,0000000000.00 as bud
EXCELSQL;from aptrtail inner join apcard on aptrtail.docno = apcard.DOCNO
EXCELSQL;left join jobmst on aptrtail.JOB = jobmst.JOBNO
EXCELSQL;left join glmst on aptrtail.glid = glmst.GLID
EXCELSQL;where aptrtail.job >= :m11 and aptrtail.job <= :m12
EXCELSQL;and year(apcard.datedoc) = :m21
EXCELSQL;and month(apcard.datedoc) = :m31
EXCELSQL;
EXCELSQL;group by aptrtail.JOB,aptrtail.glid, jobmst.JDESC, glmst.GLDESC&
EXCELSQL;
EXCELSQL;drop table if exists _exp2&
EXCELSQL;create table _exp2
EXCELSQL;select jobno,glid,jdesc,gldesc,sum(amt) as amt,sum(bud) as bud
EXCELSQL;from _exp
EXCELSQL;group by jobno,glid,jdesc,gldesc&
EXCELSQL;
EXCELSQL;update _exp2 inner join jobtail on (_exp2.jobno = jobtail.jobno and _exp2.glid = jobtail.glid)
EXCELSQL;set bud = jobtail.budget&
EXCELSQL;
EXCELSQL;select * from _exp2
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
