REPORTFORM;FIX6-2.frf
TITLE;รายงานสินทรัพย์และค่าเสื่อมราคาที่หักได้ตามพระราชกฤษฎีกา
SQL;DROP TABLE IF EXISTS _fix&
SQL;CREATE TABLE _fix
SQL;select fixmst.FIXGL, glmst.GLDESC, 
SQL;fixmst.FIXID, fixmst.FIXDESC, fixmst.DATESTART,
SQL;fixmst.COST, fixmst.BEG_COST, fixmst.RATE, 
SQL;0000000000.00 as bal_COST, fixmst.DPTID,
SQL;0000000000.00 AS THISYEAR, 
SQL;0000000000.00 AS REPBAL
SQL;from fixmst 
SQL;left join fixgroup on fixmst.FIXGROUP = fixgroup.FIXGROUP
SQL;left join glmst on fixmst.FIXGL = glmst.GLID
SQL;where (fixmst.sale <> "Y" or isnull(fixmst.sale))
SQL;order by fixmst.FIXGROUP, fixmst.FIXID&
SQL;
SQL;DROP TABLE IF EXISTS _fix2&
SQL;CREATE TABLE _fix2
SQL;select fixtr.FIXID, sum(amount) as amount
SQL;from fixtr
SQL;where datedoc <= :M21
SQL;group by fixid&
SQL;
SQL;UPDATE _fix inner join _fix2 on _fix.fixid = _fix2.fixid
SQL;SET _fix.beg_cost = _fix.beg_cost + _fix2.amount&
SQL;
SQL;DROP TABLE IF EXISTS _fix2&
SQL;CREATE TABLE _fix2
SQL;select fixtr.FIXID , docno, datedoc, sum(amount) as amount
SQL;from fixtr
SQL;where datedoc >= :M21 and datedoc <= :M22
SQL;group by fixid&
SQL;
SQL;UPDATE _fix inner join _fix2 on _fix.fixid = _fix2.fixid
SQL;SET _fix.bal_cost = _fix2.amount&
SQL;
SQL;UPDATE _fix SET _fix.thisyear = _fix.beg_cost + _fix.bal_cost&
SQL;
SQL;UPDATE _fix SET _fix.repbal = _fix.cost - _fix.thisyear&
SQL;
SQL;select * from _fix order by fixgl,fixid
CONDITION;ตั้งแต่รหัสบัญชี~FIXMST.FIXGL~Chr~2~SELECT GLID FROM GLMST~GL
CONDITION;ตั้งแต่วันที่~FIXTR.DATEDOC~Date~2~~
EXCELSQL;DROP TABLE IF EXISTS _fix&
EXCELSQL;CREATE TABLE _fix
EXCELSQL;select fixmst.FIXGL, glmst.GLDESC, 
EXCELSQL;fixmst.FIXID, fixmst.FIXDESC, fixmst.DATESTART,
EXCELSQL;fixmst.COST, fixmst.BEG_COST, fixmst.RATE, 
EXCELSQL;0000000000.00 as bal_COST, fixmst.DPTID,
EXCELSQL;0000000000.00 AS THISYEAR, 
EXCELSQL;0000000000.00 AS REPBAL
EXCELSQL;from fixmst 
EXCELSQL;left join fixgroup on fixmst.FIXGROUP = fixgroup.FIXGROUP
EXCELSQL;left join glmst on fixmst.FIXGL = glmst.GLID
EXCELSQL;where (fixmst.sale <> "Y" or isnull(fixmst.sale))
EXCELSQL;order by fixmst.FIXGROUP, fixmst.FIXID&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _fix2&
EXCELSQL;CREATE TABLE _fix2
EXCELSQL;select fixtr.FIXID, sum(amount) as amount
EXCELSQL;from fixtr
EXCELSQL;where datedoc <= :M21
EXCELSQL;group by fixid&
EXCELSQL;
EXCELSQL;UPDATE _fix inner join _fix2 on _fix.fixid = _fix2.fixid
EXCELSQL;SET _fix.beg_cost = _fix.beg_cost + _fix2.amount&
EXCELSQL;
EXCELSQL;DROP TABLE IF EXISTS _fix2&
EXCELSQL;CREATE TABLE _fix2
EXCELSQL;select fixtr.FIXID , docno, datedoc, sum(amount) as amount
EXCELSQL;from fixtr
EXCELSQL;where datedoc >= :M21 and datedoc <= :M22
EXCELSQL;group by fixid&
EXCELSQL;
EXCELSQL;UPDATE _fix inner join _fix2 on _fix.fixid = _fix2.fixid
EXCELSQL;SET _fix.bal_cost = _fix2.amount&
EXCELSQL;
EXCELSQL;UPDATE _fix SET _fix.thisyear = _fix.beg_cost + _fix.bal_cost&
EXCELSQL;
EXCELSQL;UPDATE _fix SET _fix.repbal = _fix.cost - _fix.thisyear&
EXCELSQL;
EXCELSQL;select _fix.FIXID AS "รหัสทรัพย์สิน", _fix.FIXDESC AS "รายละเอียดทรัพย์สิน", _fix.DATESTART AS "วันที่ซื้อ",
EXCELSQL;_fix.COST AS "ราคาซื้อ", _fix.BEG_COST AS "ค่าเสื่อมยกมา", _fix.RATE AS "อัตราค่าเสื่อม %", _fix.bal_COST AS "ค่าเสื่อมในปีนี้",
EXCELSQL;_fix.thisyear AS "ค่าเสื่อมรวมในปีนี้", _fix.repbal AS "ค่าเสื่อมยกไป",
EXCELSQL;_fix.DPTID AS "รหัสแผนก"
EXCELSQL;from _fix
EXCELSQL;order by fixgl,fixid
