核對(duì)銀行對(duì)賬單與單位銀行日記賬(以下簡(jiǎn)稱(chēng)單位日記賬)是對(duì)銀行存款審計(jì)中一項(xiàng)重要的步驟。通過(guò)核對(duì)銀行對(duì)賬單與單位日記賬，可以查找出未達(dá)賬項(xiàng)，從而為發(fā)現(xiàn)出租、出借帳戶、挪用公款，非法出借資金等違紀(jì)問(wèn)題提供線索。以往查找未達(dá)賬項(xiàng)采用的是手工逐行勾挑的方法。這種方法耗時(shí)長(zhǎng)，準(zhǔn)確性不高。尤其是對(duì)一些存取款業(yè)務(wù)頻繁的單位，手工核對(duì)更是顯得力不從心。而利用SQL游標(biāo)則可以快速查找未達(dá)賬項(xiàng)，從而取得事半功倍的效果。

　　一、采集銀行對(duì)賬單和單位日記賬數(shù)據(jù)，并進(jìn)行必要的整理轉(zhuǎn)換，使其對(duì)應(yīng)字段的長(zhǎng)度、數(shù)據(jù)類(lèi)型相同。如：通常銀行日記賬的支票號(hào)為銀行對(duì)賬單的憑證號(hào)的后四位，因此應(yīng)對(duì)銀行對(duì)賬單的憑證號(hào)作截?cái)嗵幚?。Update 銀行對(duì)賬單 set 憑證號(hào)=right(憑證號(hào),4)

　　二、對(duì)應(yīng)整理后的銀行對(duì)賬單和單位日記賬創(chuàng)建四個(gè)空表用于接收未達(dá)賬項(xiàng)記錄：?jiǎn)挝灰迅躲y行未付、單位已收銀行未收、銀行已付單位未付、銀行已收單位未收。如：

　　create table 單位已付銀行未付 (憑證日期 varchar(14),摘要 nvarchar(50),支票號(hào) nvarchar(10),借方金額 money,貸方金額 money)

　　create table 單位已收銀行未收 (憑證日期 varchar(14),摘要 nvarchar(50),支票號(hào) nvarchar(10),借方金額 money,貸方金額 money)

　　create table 銀行已付單位未付 (憑證日期 varchar(14),摘要 nvarchar(50),憑證號(hào) nvarchar(10),借方金額 money,貸方金額 money)

　　create table 銀行已收單位未收 (憑證日期 varchar(14),摘要 nvarchar(50),憑證號(hào) nvarchar(10),借方金額 money,貸方金額 money)

　　三、創(chuàng)建游標(biāo)，將所有金額以是否有重復(fù)金額為條件分為相同金額和不同金額記錄，再做對(duì)應(yīng)比較，分步篩選未達(dá)賬項(xiàng)：

　　1、篩選單位日記賬不同金額借方有銀行對(duì)賬單貸方無(wú)的記錄

　　declare cur1 cursor for select 借方金額 from 單位日記賬 where 借方金額 in (select 借方金額 from 單位日記賬 group by 借方金額 having count(借方金額)=1)

　　open cur1

　　declare @借方金額 money

　　fetch next from cur1 into @借方金額

　　while @@fetch_status=0

　　begin

　　if @借方金額 in (select 貸方金額 from 銀行對(duì)賬單 group by 貸方金額 having count(貸方金額)=1)

　　fetch next from cur1 into @借方金額

　　else

　　begin

　　insert into 單位已收銀行未收 select * from 單位日記賬 where 借方金額=@借方金額

　　fetch next from cur1 into @借方金額

　　end

　　end

　　close cur1

　　deallocate cur1

　　2、篩選單位日記賬不同金額貸方有銀行對(duì)賬單借方無(wú)的記錄

　　declare cur1 cursor for select 貸方金額 from 單位日記賬 group by 貸方金額 having count(貸方金額)=1

　　open cur1

　　declare @貸方金額 money

　　fetch next from cur1 into @貸方金額

　　while @@fetch_status=0

　　begin

　　if @貸方金額 in (select 借方金額 from 銀行對(duì)賬單

　　group by 借方金額 having count(借方金額)=1)

　　fetch next from cur1 into @貸方金額

　　else

　　begin

　　insert into 單位已付銀行未付 select * from 單位日記賬 where 貸方金額=@貸方金額

　　fetch next from cur1 into @貸方金額

　　end

　　end

　　close cur1

　　deallocate cur1

　　3、篩選單位日記賬相同金額借方有銀行對(duì)賬單貸方無(wú)的記錄

　　declare cur1 cursor for select 借方金額,count(*) 個(gè)數(shù) from 單位日記賬 where 借方金額0 group by 借方金額 having count(借方金額)>1

　　open cur1

　　declare @借方金額 money,@個(gè)數(shù) int

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　while @@fetch_status=0

　　begin

　　if @個(gè)數(shù) =(select count(*) from 銀行對(duì)賬單 where 貸方金額=@借方金額)

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　else

　　begin

　　insert into 單位已收銀行未收 select * from 單位日記賬 where 借方金額=@借方金額

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　end

　　end

　　close cur1

　　deallocate cur1

　　4、篩選單位日記賬相同金額貸方有銀行對(duì)賬單借方無(wú)的記錄

　　declare cur1 cursor for select 貸方金額,count(*) 個(gè)數(shù) from 單位日記賬 where 貸方金額0 group by 貸方金額 having count(借方金額)>1

　　open cur1

　　declare @貸方金額 money,@個(gè)數(shù) int

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　while @@fetch_status=0

　　begin

　　if @個(gè)數(shù) =(select count(*) from 銀行對(duì)賬單 where 借方金額=@貸方金額)

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　else

　　begin

　　insert into 單位已付銀行未付 select * from 單位日記賬 where 支票號(hào) is null and 貸方金額=@貸方金額

　　declare cur2 cursor for select 支票號(hào) from 單位日記賬 where 貸方金額=@貸方金額 and 支票號(hào) is not null

　　open cur2

　　declare @支票號(hào) varchar(10)

　　fetch next from cur2 into @支票號(hào)

　　while @@fetch_status=0

　　begin

　　if @支票號(hào) in (select 憑證號(hào) from 銀行對(duì)賬單 where 借方金額=@貸方金額)

　　fetch next from cur2 into @支票號(hào)

　　else

　　begin

　　insert into 單位已付銀行未付 select * from 單位日記賬 where 支票號(hào)=@支票號(hào)

　　fetch next from cur2 into @支票號(hào)

　　end

　　end

　　close cur2

　　deallocate cur2

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　end

　　end

　　close cur1

　　deallocate cur1

　　5、篩選銀行對(duì)賬單不同金額借方有單位日記賬貸方無(wú)的記錄

　　declare cur1 cursor for select 借方金額 from 銀行對(duì)賬單 group by 借方金額 having count(借方金額)=1

　　open cur1

　　declare @借方金額 money

　　fetch next from cur1 into @借方金額

　　while @@fetch_status=0

　　begin

　　if @借方金額 in (select 貸方金額 from 單位日記賬 group by 貸方金額 having count(貸方金額)=1)

　　fetch next from cur1 into @借方金額

　　else

　　begin

　　insert into 銀行已付單位未付 select * from 銀行對(duì)賬單 where 借方金額=@借方金額

　　fetch next from cur1 into @借方金額

　　end

　　end

　　close cur1

　　deallocate cur1

　　6、篩選銀行對(duì)賬單不同金額貸方有單位日記賬借方無(wú)的記錄

　　declare cur1 cursor for select 貸方金額 from 銀行對(duì)賬單 group by 貸方金額 having count(貸方金額)=1

　　open cur1

　　declare @貸方金額 money

　　fetch next from cur1 into @貸方金額

　　while @@fetch_status=0

　　begin

　　if @貸方金額 in (select 借方金額 from 單位日記賬

　　group by 借方金額 having count(借方金額)=1)

　　fetch next from cur1 into @貸方金額

　　else

　　begin

　　insert into 銀行已收單位未收 select * from 銀行對(duì)賬單 where 貸方金額=@貸方金額

　　fetch next from cur1 into @貸方金額

　　end

　　end

　　close cur1

　　deallocate cur1

　　7、篩選銀行對(duì)賬單相同金額借方有單位日記賬貸方無(wú)的記錄

　　declare cur1 cursor for select 借方金額,count(*) 個(gè)數(shù) from 銀行對(duì)賬單 where 借方金額0 group by 借方金額 having count(借方金額)>1

　　open cur1

　　declare @借方金額 money,@個(gè)數(shù) int

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　while @@fetch_status=0

　　begin

　　if @個(gè)數(shù) =(select count(*) from 單位日記賬 where 貸方金額=@借方金額)

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　else

　　begin

　　insert into 銀行已付單位未付 select * from 銀行對(duì)賬單 where 憑證號(hào) is null and 借方金額=@借方金額

　　declare cur2 cursor for select 憑證號(hào) from 銀行對(duì)賬單 where 借方金額=@借方金額 and 憑證號(hào) is not null

　　open cur2

　　declare @憑證號(hào) varchar(10)

　　fetch next from cur2 into @憑證號(hào)

　　while @@fetch_status=0

　　begin

　　if @憑證號(hào) in (select 支票號(hào) from 單位日記賬 where 貸方金額=@借方金額)

　　fetch next from cur2 into @憑證號(hào)

　　else

　　begin

　　insert into 銀行已付單位未付 select * from 銀行對(duì)賬單 where 憑證號(hào)=@憑證號(hào)

　　fetch next from cur2 into @憑證號(hào)

　　end

　　end

　　close cur2

　　deallocate cur2

　　fetch next from cur1 into @借方金額,@個(gè)數(shù)

　　end

　　end

　　close cur1

　　deallocate cur1

　　8、篩選銀行對(duì)賬單相同金額貸方有單位日記賬借方無(wú)的記錄

　　declare cur1 cursor for select 貸方金額,count(*) 個(gè)數(shù) from 銀行對(duì)賬單 where 貸方金額0 group by 貸方金額 having count(借方金額)>1

　　open cur1

　　declare @貸方金額 money,@個(gè)數(shù) int

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　while @@fetch_status=0

　　begin

　　if @個(gè)數(shù) =(select count(*) from 單位日記賬 where 借方金額=@貸方金額)

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　else

　　begin

　　insert into 銀行已收單位未收 select * from 銀行對(duì)賬單 where 貸方金額=@貸方金額

　　fetch next from cur1 into @貸方金額,@個(gè)數(shù)

　　end

　　end

　　close cur1

　　deallocate cur1