REPORTFORM;PF_JOB.frf
TITLE;รายงาน งบกำไรขาดทุนแยก Job
SQL;drop table if exists _gljob&
SQL;
SQL;create table _gljob
SQL;select  
SQL;gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,0 as xline,1 as xratio,
SQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.CREDIT - gltr.DEBIT,0)) as inPeirod,
SQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.CREDIT - gltr.DEBIT,0)) as AccPeriod
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
SQL;left join config on '01' = config.XINDEX
SQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
SQL;where LEFT(glmst.GLTYPE,1) = 'R' and datedoc >= config.SDATESTART and datedoc <= :M22
SQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
SQL;group by gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
SQL;
SQL;insert into _gljob
SQL;select  
SQL;gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,1 as xline,-1 as xratio,
SQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as inPeirod,
SQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as AccPeriod
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
SQL;left join config on '01' = config.XINDEX
SQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
SQL;where LEFT(glmst.GLTYPE,1) = 'C' and datedoc >= config.SDATESTART and datedoc <= :M22
SQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
SQL;group by gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
SQL;
SQL;insert into _gljob
SQL;select  
SQL;gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,2 as xline,-1 as xratio,
SQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as inPeirod,
SQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as AccPeriod
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
SQL;left join config on '01' = config.XINDEX
SQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
SQL;where LEFT(glmst.GLTYPE,1) = 'E' and datedoc >= config.SDATESTART and datedoc <= :M22
SQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
SQL;group by gltr.GLID,
SQL;glmst.GLDESC,
SQL;glmst.GLEDESC,
SQL;glmst.GLGROUP,glgroup.glgroupdesc,
SQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
SQL;
SQL;select * from _gljob order by jobno, xline, glgroup, glid
CONDITION;ตั้งแต่รหัส Job~GLTR.JOBNO~Chr~2~SELECT JOBNO FROM JOBMST~JOB
CONDITION;ตั้งแต่วันที่~GLTR.DATEDOC~Date~2~~
EXCELSQL;drop table if exists _gljob&
EXCELSQL;
EXCELSQL;create table _gljob
EXCELSQL;select  
EXCELSQL;gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,0 as xline,1 as xratio,
EXCELSQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.CREDIT - gltr.DEBIT,0)) as inPeirod,
EXCELSQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.CREDIT - gltr.DEBIT,0)) as AccPeriod
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
EXCELSQL;left join config on '01' = config.XINDEX
EXCELSQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
EXCELSQL;where LEFT(glmst.GLTYPE,1) = 'R' and datedoc >= config.SDATESTART and datedoc <= :M22
EXCELSQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
EXCELSQL;group by gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
EXCELSQL;
EXCELSQL;insert into _gljob
EXCELSQL;select  
EXCELSQL;gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,1 as xline,-1 as xratio,
EXCELSQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as inPeirod,
EXCELSQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as AccPeriod
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
EXCELSQL;left join config on '01' = config.XINDEX
EXCELSQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
EXCELSQL;where LEFT(glmst.GLTYPE,1) = 'C' and datedoc >= config.SDATESTART and datedoc <= :M22
EXCELSQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
EXCELSQL;group by gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
EXCELSQL;
EXCELSQL;insert into _gljob
EXCELSQL;select  
EXCELSQL;gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC,2 as xline,-1 as xratio,
EXCELSQL;sum(if(gltr.DATEDOC >= :M21 and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as inPeirod,
EXCELSQL;sum(if(gltr.DATEDOC >= config.SDATESTART and gltr.DATEDOC <= :M22 ,gltr.debit - gltr.credit,0)) as AccPeriod
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
EXCELSQL;left join config on '01' = config.XINDEX
EXCELSQL;left join jobmst on gltr.JOBNO = jobmst.JOBNO
EXCELSQL;where LEFT(glmst.GLTYPE,1) = 'E' and datedoc >= config.SDATESTART and datedoc <= :M22
EXCELSQL;AND gltr.jobno >= :m11 and gltr.jobno <= :m12
EXCELSQL;group by gltr.GLID,
EXCELSQL;glmst.GLDESC,
EXCELSQL;glmst.GLEDESC,
EXCELSQL;glmst.GLGROUP,glgroup.glgroupdesc,
EXCELSQL;glmst.GLTYPE,gltr.JOBNO, jobmst.JDESC&
EXCELSQL;
EXCELSQL;select * from _gljob order by jobno, xline, glgroup, glid
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
