會計數(shù)據(jù)審計分析九大算法實例

1、 .wd. .wd. .wd./*第七章 7.2九大算法實例*/*第一算法 查記賬憑證的算法知識點：查的是記賬憑證而不是明細賬。一張憑證是多條記錄的集合，而記錄只是一條解題規(guī)那么：一個條件二張表，二個條件三張表，三個條件四張表。*/-分析：從題意看只有一個決定條件，即科目為主營業(yè)務收入，所以要用二張表相連,a表是查詢結(jié)果憑證，用b表設條件。 -例：檢索出所有現(xiàn)金支出為整千元的記賬記錄。Select * from gl_accvouch where ccode=101 and abs(mc%1000)=0 and mc0 -例：檢索出所有現(xiàn)金收支大于1000元的記賬憑證。Select b.* f

2、rom gl_accvouch a join gl_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=101 and (a.md1000 or a.mc1000) -例：檢索出所有憑證的現(xiàn)金科目的對應科目的科目代碼、科目名稱、借方發(fā)生額、貸方發(fā)生額。(?)select a.iperiod,a.csign ,a.ino_id,a.ccode,b.ccode_name,a.md,a.mcfrom GL_accvouch a join code b on a.ccode=

3、b.ccode where a.ccode_equal like %,101% or a.ccode_equal like 101% order by a.iperiod,a.csign ,a.ino_id,a.ccodeselect a.ccode 科目代碼,ccode_name 科目名稱,SUM(md) 借方發(fā)生額,SUM(mc) 貸方發(fā)生額from GL_accvouch a join code b on a.ccode=b.ccodewhere ccode_equal like %,101% or ccode_equal like 101% group by a.ccode,ccode

4、_name/*第二算法 賒銷算法，借方一個科目，貸方一個科目。如借 應收賬款/票據(jù) 貸：主營業(yè)務收入/產(chǎn)品銷售收入 查憑證比查記錄多張表。*/-分析：從題意看有二個條件，即憑證中要有應收科目和主營業(yè)務收入科目，所以要三張表，a 表是查詢結(jié)果憑證，-b表設應收條件，c表設主營業(yè)務收入條件。 -例：檢索出所有賒銷收入明細賬記錄。賒銷：已銷售，沒收到人民幣。 -第一種方式 可以利用產(chǎn)品銷售收入的對應科目code_equal來完成select * from GL_accvouch where ccode=501 and ccode_equal like %113% and mc0 order by i

5、period,csign,ino_id -第二種方式 內(nèi)連接方式，求兩個集合的交集運算，檢查兩個表中的共有內(nèi)容。顯示的是記錄而不是憑證。Select a.* from gl_accvouch a join gl_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=501 and b.ccode=113 and a.mc0 order by a.iperiod,a.csign,a.ino_id -例：檢索出所有賒銷收入明細賬憑證?；虿檎腋髟沦d銷憑證 -第一種方式 兩表連

6、接select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.ccode=501 and b.ccode_equal like %113% and b.mc0order by a.iperiod,a.csign,a.ino_id -第二種方式 三表連接select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csig

7、n=b.csign and a.ino_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where b.ccode like 501% and c.ccode like 113% and C.md0 and b.mc0order by a.iperiod,a.csign,a.ino_id -例：查找各月賒銷收入總額select a.iperiod 期間,SUM(a.mc) 收入總額 from GL_accvouch a join GL_accvouch

8、 b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode like 501% and b.ccode like 113% and b.md0 and a.mc0group by a.iperiodselect iperiod ,SUM(mc) 收入總額 from GL_accvouch where ccode =501 and ccode_equal like %113%group by iperiod -例：查找各月現(xiàn)銷記錄select a.* from GL_accvouch a jo

9、in GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode like 101% and b.ccode like 501% and a.md0select * from GL_accvouch awhere a.ccode =101 and a.ccode_equal like %501% and md0 -例：查找各月現(xiàn)銷憑證select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperi

10、od and a.csign=b.csign and a.ino_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where b.ccode like 501% and (c.ccode like 101% or c.ccode like 102%) and C.md0 and b.mc0 -例：查找各月現(xiàn)銷收入,分析：統(tǒng)計各月通過現(xiàn)結(jié)方式的現(xiàn)金收入。select a.iperiod 期間,SUM(a.md) 收入from GL_accvouch a

11、join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (a.ccode like 101% or a.ccode like 102%) and b.ccode like 501% and a.md0 and b.mc0group by a.iperiod -例：計算各月收回的銷售欠款應收賬款累計發(fā)生額。分析：應收賬款是113，何謂收回，即113在貸方，借方應為101、102select a.iperiod 期間,a.ccode,sum(a.mc) mc,SUM(a.md

12、) mdfrom GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (a.ccode like 101% or a.ccode like 102%) and b.ccode like 113% and a.md0 group by a.iperiod,a.ccode -例：計算各月收回的銷售欠款應收賬款憑證。分解條件：此憑證借方應為現(xiàn)金或銀行存款，貸方為113，要查找憑證select a.* from GL_accvouch a join

13、gl_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where (b.ccode like 101% or b.ccode like 102%) and b.md0 and c.ccode like 113% and c.mc0order by a.iperiod,a.csign,a.ino_idselect a.* from

14、 GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (b.ccode like 101% or b.ccode like 102%) and b.md0 and (b.ccode_equal like 113% or b.ccode_equal like %,113%)/*第三算法 登記一個科目，末登記一個科目的算法。使用外連接left(right) join。 實現(xiàn)兩個集合的差集運算。找出一個集合中存在而另一個集合不存在的內(nèi)容*/ -例

15、：檢查所有確認收入時，未同時提取應交稅金的銷售收入明細賬記錄。 -分析：先查詢憑證中有主營業(yè)務收入，再左連接所有提取了應交稅金的記錄，而右表中為空的即為未提取應交稅金的記錄。select a.*from (select * from GL_accvouch where ccode like 501% and mc0) aleft join (select * from GL_accvouch where ccode like 221% and mc0) b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere

16、 b.iperiod is nullselect a.* from GL_accvouch a left join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id and b.ccode like 221% and b.mc0where a.ccode like 501% and b.iperiod is null and a.mc0-第一個視圖，獲取所有有501主營業(yè)務收入的記錄create view a_1 asselect * from GL_accvouch where cco

17、de like 501% and mc0-第二個視力，獲取所有有221%提取稅金的記錄create view a_2as select * from GL_accvouch where ccode like 221% and mc0-最后，一視圖左連接二視圖，檢查右邊記錄為空的所有記錄，即為確認收入時未同時提取應交稅金。create view a_3asselect a.* from a_1 aleft join a_2 b on a.iperiod =b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod is n

18、ull order by a.iperiod,a.csign,a.ino_iddrop view a_1,a_2 -例：檢查漏繳稅款的憑證 -分析用有主營業(yè)務收入的a表子查詢作查詢結(jié)果憑證，再與有提取稅金的B表子查詢進展左連接，右為空的即為所求。select a.* from GL_accvouch ajoin (select a.*from (select * from GL_accvouch where ccode like 501% and mc0) aleft join (select * from GL_accvouch where ccode like 221% and mc0)

19、b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod is null) bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idselect a.* from GL_accvouch ajoin (select a.* from GL_accvouch a left join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=

20、b.ino_id and b.ccode like 221% and b.mc0where a.ccode like 501% and a.mc0 and b.iperiod is null) bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id-在上題的根基上，將憑證表與視圖a_3用join連接，而視圖a_3中的記錄的所在憑證即為漏繳稅款的憑證select a.*from GL_accvouch ajoin a_3 b on a.iperiod=b.iperiod and a.csign=b.csign and a

21、.ino_id=b.ino_idorder by a.iperiod,a.csign,a.ino_iddrop view a_3 /*第四算法 數(shù)據(jù)分層算法*/-利用分組和求和、計數(shù)函數(shù)實現(xiàn)分層。三種情況：0到最大值分層；正的最小值到最大值分層；按金額范圍分層。-一從0到最大值分層。分層：(1)統(tǒng)計業(yè)務發(fā)生額的最大值、最小值，分別匯總金額和數(shù)量。分層的關(guān)鍵是找出層寬 -(2)使用ceiling取整函數(shù)進展分層分組匯總，正數(shù)“進一法取整，負數(shù)“去尾法取整。 -實際上ceiling函數(shù)是返回大于或等于所給數(shù)值的最小整數(shù)。且注：5/2=2，5/2.0=2.5 -例 將主營業(yè)務收入明細賬501科目記錄

22、從0開場到最大值分10層，統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。select MAX(mc) 最大值,MAX(mc)/10 層寬,COUNT(*) 數(shù)量合計,SUM(mc) 金額合計from GL_accvouch where ccode like 501% and mc0select cast(CEILING(mc/40800.00) as int) 層級,COUNT(*) 業(yè)務筆數(shù),cast(COUNT(*)/27.00 as numeric(4,2) 數(shù)量比率,sum(mc) 業(yè)務金額合計,cast(sum(mc)/4733700.00 as numeric(4,4) 金

23、額比率from GL_accvouch where ccode like 501% and mc0 group by CEILING(mc/40800.00)-二從正的最小值到最大值分層 -例 將主營業(yè)務收入明細賬501科目記錄從正的最小值開場到最大值分10層，層數(shù)=ceiling(發(fā)生額-最小值)/層寬 -統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。必須要做最小值的判斷,使層數(shù)的開場為1。 select MAX(mc) 最大值,Min(mc) 最小值,(MAX(mc)-MIN(mc)/10 層寬,COUNT(*) 數(shù)量合計,SUM(mc) 金額合計from GL_accvouch

24、where ccode like 501% and mc0select CEILING(case when mc=6000 then 1 else (mc-6000)/40200.00 end) 層級,COUNT(*) 業(yè)務筆數(shù),count(*)/27.00 數(shù)量占比,SUM(mc) 業(yè)務金額合計,SUM(mc)/4733700.00 金額占比from GL_accvouch where ccode like 501% and mc0group by CEILING(case when mc=6000 then 1 else (mc-6000)/40200.00 end)-三按金額范圍分層。

25、-例 將主營業(yè)務收入明細賬501科目記錄分為4層，包括2萬元以下，2萬-3萬元，3萬-4萬元，4萬元以上。 -統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。 -分析：首先統(tǒng)計每筆業(yè)務所屬的區(qū)間，按區(qū)間確定層級增加“層級列。然后再按要求進展統(tǒng)計。select COUNT(*) zsl,SUM(mc) zje from GL_accvouch where ccode like 501% and mc0select 層級,COUNT(*) 業(yè)務筆數(shù),count(*)/27.00 數(shù)量占比,SUM(mc) 業(yè)務金額合計,SUM(mc)/4733700.00 金額占比from (select 層

26、級=case when mc40000 then 4 end,*from GL_accvouch where ccode like 501% and mc0) agroup by 層級 /*第五算法 整理科目的算法*/ -例：從憑證表查詢獲得以下內(nèi)容期間、憑證類型、憑證號、摘要、科目代碼、借貸方向、金額,利用CASE語句。select iperiod 期間,csign 憑證類型,ino_id 憑證號,a.cdigest 摘要,a.ccode 科目代碼,b.ccode_name 科目名稱,case when md0 then 借 when mc0 then 貸 end 借貸方向,case whe

27、n md0 then md when mc0 then mc end 金額from GL_accvouch a join code b on a.ccode=b.ccode -例：某單位科目代碼級次為322。以下程序可以生成了個新的科目代碼表。表中包含兩個字段科目代碼、科目全稱-分析：利用case語句進展判斷。第一個表用來展示，第2表為二級科目表，第3表為三級科目表，第4表為四級科目表. -按級次來確定需要連接幾個表，如題，3個級次那么要連接4張表。分別自連接，利用left構(gòu)造各級次的代碼表select a.ccode 科目代碼,科目全稱=b.ccode_name+case when LEN(

28、a.ccode)3 then +c.ccode_name else EnD +case when LEN(a.ccode)5 then +d.ccode_name else end from code ajoin code b on left(a.ccode,3)=b.ccodejoin code c on left(a.ccode,5)=c.ccodejoin code d on left(a.ccode,7)=d.ccode/*第六算法 真實性、完整性、一致性檢查的算法分析：真實性、完整性算法類型比較多，首先看什么是真實性；什么是完整性，從教師已經(jīng)講過的內(nèi)容來看，注意，查真實性完整性，在憑

29、證表中都指的是收入憑證，也就是在憑證表中要設條件ccode like 501%查找真實性就是從:關(guān)注業(yè)務的真實性,進展逆查.憑證表發(fā)票表發(fā)貨單即為 gl_accvouch - salebillvouch- dispatchlist查找完整性就是從：發(fā)貨單發(fā)票表憑證表即為 dispatchlist-salebillvouch - gl_accvouch -例：審計人員檢查銷售發(fā)票所列商品的品名、數(shù)量、金額與發(fā)貨單中所列商品的品名、數(shù)量、金額是否一致。 分析：全連接full join。在不確定兩個集合的關(guān)系時，可以使用全連接。包含了兩個集合的所有元素。 通常會將同一張發(fā)票、發(fā)貨單的主子表連接起來。

30、按品名等分組。在左連接、右連接、全連接時，如 where 條件需要作比較判斷時，需用isnull函數(shù)確定數(shù)據(jù)的準確性、完整性。*/select * from (select a.cSBVCode,cInvCode,SUM(b.iQuantity) sl,SUM (iNatSum ) jefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode,cInvCode) a full join (select a.cSBVCode,a.cdlcode,cInvCode,SUM(b.iQuantit

31、y) sl,SUM(iNatSum) jefrom DispatchList a join DispatchLists b on a.DLID=b.DLID group by a.cSBVCode,a.cdlcode,cInvCode) bon a.cSBVCode=b.cSBVCode and a.cInvCode=b.cInvCode -連接條件where isnull(a.je,0) isnull(b.je,0) or isnull(a.sl,0) isnull(b.sl,0)-一致性比較 or a.cInvCode is null or b.cInvCode is null-發(fā)票主、子

32、表連接create view a_fp asselect a.SBVID,b.cInvCode,SUM(b.iQuantity) sl,SUM(iNatSum) jefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVIDgroup by a.SBVID,b.cInvCodeselect * from a_fpdrop view a_fp-發(fā)貨單主、子表連接create view a_fhd as select a.SBVID,a.cDLCode,b.cInvCode,SUM(b.iQuantity) sl,SUM(iNatSu

33、m) jefrom DispatchList a join DispatchLists b on a.DLID=b.DLIDgroup by a.SBVID,a.cDLCode,b.cInvCodeselect * from a_fhddrop view a_fhd-一致性比較select * from a_fp afull join a_fhd b on a.sbvid=b.sbvid and a.cinvcode=b.cinvcodewhere ISNULL(a.sl,0)ISNULL(b.sl,0) or ISNULL(a.je,0)ISNULL(b.je,0) or a.cinvcod

34、e is null or b.cinvcode is null-檢索出銷售收入明細賬與發(fā)票不一致的地方./*分析:收入為501, 主要是比照銷售收入的貸方金額sum(mc)與發(fā)票的金額不含稅sum(iNatMoney)是否一致 根據(jù)憑證表中的外部單據(jù)號coutid、外部單據(jù)類型coutbillsign 與發(fā)票表的發(fā)票號csbvcode、發(fā)票類型cvouchtype相等作為條件來進展判斷。*/select a.coutid,a.coutbillsign,sum(mc) sumje from GL_accvouch a where ccode like 501% and mc0group by a

35、.coutid,a.coutbillsignselect a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumjefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchTypeselect * from (select a.coutid,a.coutbillsign,sum(mc) sumje from GL_accvouch a where ccode like 501% and mc0group by a.coutid,a.c

36、outbillsign) afull join (select a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumjefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchType) bon a.coutid=b.cSBVCode and a.coutbillsign=b.cVouchTypewhere isnull(a.sumje,0)isnull(b.sumje,0)or a.coutbillsign is null or

37、b.cVouchType is null/*(一)真實性檢查之符合性測試 (一)真實性檢查之符合性測試：業(yè)務流程逆查，憑證表-?發(fā)票表-?發(fā)貨單-?訂單*/-例題六：檢查銷售發(fā)票副聯(lián)是否附有發(fā)運憑證(或提貨單)及顧客訂貨單 -檢查發(fā)票是否附有發(fā)貨單select a.* from SaleBillVouch a left join DispatchList b on a.SBVID=b.SBVID where b.SBVID is null -檢查發(fā)票是否附有訂單select a.* from SaleBillVouch a left join SO_SOMain b on a.cSOCode=

38、b.cSOCode where b.cSOCode is null -例題七：查真實性，檢查是否每張發(fā)票都有對應的發(fā)貨單。select a.* from SaleBillVouch a left join DispatchList b on a.SBVID=b.SBVID where b.DLID is null -例題八：查真實性，檢查是否每一張發(fā)票都有對應的訂單select a.* from SaleBillVouch a left join SO_SOMain b on a.cSOCode=b.cSOCode where b.cSOCode is null/*(一)真實性檢查之實質(zhì)性測試

39、*/-1、追查主營業(yè)務收入的明細賬中的分錄至銷售單、銷售發(fā)票副聯(lián)及發(fā)運憑證。 -檢查主檢察員業(yè)務收入501的明細賬分錄是否都銷售開票select * from GL_accvouch a left join SaleBillVouch b on a.coutid=b.cSBVCode and a.coutbillsign=b.cVouchType where b.SBVID is null and a.ccode like 501% and mc0/*(二)完整性檢查：業(yè)務流程順查，訂單-?發(fā)貨-?發(fā)票-?記賬*/-例題九：查完整性，檢查是否每一張發(fā)票都有對應的收入明細select *from

40、 SaleBillVouch a left join GL_accvouch b on a.cSBVCode=b.coutid and a.cVouchType=b.coutbillsign where b.coutid is null-例題十：查完整性，檢查是否每一張發(fā)貨單都有對應的發(fā)票select * from DispatchList a left join SaleBillVouch b on a.SBVID=b.SBVID where b.SBVID is null-例題十一:將發(fā)票與收入明細賬進展核對,確定所有的發(fā)票均記賬.select a.* from SaleBillVouch

41、 a left join GL_accvouch b on a.cSBVCode=b.coutid and a.cVouchType=b.coutbillsign and b.ccode like 501% and b.mc0where b.iperiod is null /*第七算法 金額比照檢查的算法,即估價準確性算法分析：發(fā)票和記賬憑證相比較，看金額是否對算法(注意，此題發(fā)票中的金額指的是本幣也即是發(fā)票子表中的inatmoney)金額在發(fā)票子表中，一張發(fā)票主表對應多條發(fā)票子表記錄，所以要對子表的sbvid分組求每組的合計值，算出每張發(fā)票總金額*/-例題十一：發(fā)票和收入明細賬相比較，找出發(fā)

42、票金額和收入金額不相等的記錄。-1、子表按sbvid分組求出每張發(fā)票總金額create view v_103 as select sbvid ,sum(inatmoney) sum_inatmoney from salebillvouchs group by sbvid-2、視圖和發(fā)票主表關(guān)聯(lián)alter view v_104 asselect a.sbvid,a.cvouchtype, a.csbvcode, b.sum_inatmoney from salebillvouch a inner join v_103 bon a.sbvid = b.sbvid-3、發(fā)票金額視圖和收入明細表關(guān)聯(lián)，

43、找出金額不相等的記錄(v_102是前面已生成的收入明細視圖)select a.iperiod, a.csign, a.ino_id, a.mc , b.sbvid, b.sum_inatmoney from v_102 a inner join v_104 bon a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcodewhere a.mc b.sum_inatmoney -例題十二: 追查主營業(yè)務收入明細賬中的分錄至銷售,檢查主營業(yè)務收入明細賬中登記金額與銷售發(fā)票中填寫金額是否一致.select iperiod,csign,ino_id,ccode,mc 憑證金額,inatmoney 發(fā)票金額 from GL_accvouch ajoin (select a.cSBVCode,a.cVouchType,SUM(iNatMoney) inatmoney from SaleBillVouch a join S