核對銀行對帳單與單位銀行日記帳(以下簡稱單位日記帳)是對銀行存款審計中一項重要的步驟。通過核對銀行對帳單與單位日記帳，可以查找出未達帳項，從而為發現出租、出借帳戶、挪用公款，非法出藉資金等違紀問題提供線索。以往查找未達帳項採用的是手工逐行勾挑的方法。這種方法耗時長，準確性不高。尤其是對一些存取款業務頻繁的單位，手工核對更是顯得力不從心。而利用SQL遊標則可以快速查找未達帳項，從而取得事半功倍的效果。
一、採集銀行對帳單和單位日記帳數據，並進行必要的整理轉換，使其對應欄位的長度、數據類型相同。如：通常銀行日記帳的支票號為銀行對帳單的憑證號的後四位，因此應對銀行對帳單的憑證號作截斷處理。Update 銀行對帳單 set 憑證號=right(憑證號,4)
二、對應整理後的銀行對帳單和單位日記帳創建四個空表用於接收未達帳項記錄：單位已付銀行未付、單位已收銀行未收、銀行已付單位未付、銀行已收單位未收。如：
create table 單位已付銀行未付 (憑證日期 varchar(14),摘要 nvarchar(50),支票號 nvarchar(10),借方金額 money,貸方金額 money)
create table 單位已收銀行未收 (憑證日期 varchar(14),摘要 nvarchar(50),支票號 nvarchar(10),借方金額 money,貸方金額 money)
create table 銀行已付單位未付 (憑證日期 varchar(14),摘要 nvarchar(50),憑證號 nvarchar(10),借方金額 money,貸方金額 money)
create table 銀行已收單位未收 (憑證日期 varchar(14),摘要 nvarchar(50),憑證號 nvarchar(10),借方金額 money,貸方金額 money)
三、創建遊標，將所有金額以是否有重複金額為條件分為相同金額和不同金額記錄，再做對應比較，分步篩選未達帳項：
1、篩選單位日記帳不同金額借方有銀行對帳單貸方無的記錄
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 銀行對帳單 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、篩選單位日記帳不同金額貸方有銀行對帳單借方無的記錄
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 銀行對帳單
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、篩選單位日記帳相同金額借方有銀行對帳單貸方無的記錄
declare cur1 cursor for select 借方金額,count(*) 個數 from 單位日記帳 where 借方金額0 group by 借方金額 having count(借方金額)>1
open cur1
declare @借方金額 money,@個數 int
fetch next from cur1 into @借方金額,@個數
while @@fetch_status=0
begin
if @個數 =(select count(*) from 銀行對帳單 where 貸方金額=@借方金額)
fetch next from cur1 into @借方金額,@個數
else
begin
insert into 單位已收銀行未收 select * from 單位日記帳 where 借方金額=@借方金額
fetch next from cur1 into @借方金額,@個數
end
end
close cur1
deallocate cur1
4、篩選單位日記帳相同金額貸方有銀行對帳單借方無的記錄
declare cur1 cursor for select 貸方金額,count(*) 個數 from 單位日記帳 where 貸方金額0 group by 貸方金額 having count(借方金額)>1
open cur1
declare @貸方金額 money,@個數 int
fetch next from cur1 into @貸方金額,@個數
while @@fetch_status=0
begin
if @個數 =(select count(*) from 銀行對帳單 where 借方金額=@貸方金額)
fetch next from cur1 into @貸方金額,@個數
else
begin
insert into 單位已付銀行未付 select * from 單位日記帳 where 支票號 is null and 貸方金額=@貸方金額
declare cur2 cursor for select 支票號 from 單位日記帳 where 貸方金額=@貸方金額 and 支票號 is not null
open cur2
declare @支票號 varchar(10)
fetch next from cur2 into @支票號
while @@fetch_status=0
begin
if @支票號 in (select 憑證號 from 銀行對帳單 where 借方金額=@貸方金額)
fetch next from cur2 into @支票號
else
begin
insert into 單位已付銀行未付 select * from 單位日記帳 where 支票號=@支票號
fetch next from cur2 into @支票號
end
end
close cur2
deallocate cur2
fetch next from cur1 into @貸方金額,@個數
end
end
close cur1
deallocate cur1
5、篩選銀行對帳單不同金額借方有單位日記帳貸方無的記錄
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 單位日記帳 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
6、篩選銀行對帳單不同金額貸方有單位日記帳借方無的記錄
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 單位日記帳
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
7、篩選銀行對帳單相同金額借方有單位日記帳貸方無的記錄
declare cur1 cursor for select 借方金額,count(*) 個數 from 銀行對帳單 where 借方金額0 group by 借方金額 having count(借方金額)>1
open cur1
declare @借方金額 money,@個數 int
fetch next from cur1 into @借方金額,@個數
while @@fetch_status=0
begin
if @個數 =(select count(*) from 單位日記帳 where 貸方金額=@借方金額)
fetch next from cur1 into @借方金額,@個數
else
begin
insert into 銀行已付單位未付 select * from 銀行對帳單 where 憑證號 is null and 借方金額=@借方金額
declare cur2 cursor for select 憑證號 from 銀行對帳單 where 借方金額=@借方金額 and 憑證號 is not null
open cur2
declare @憑證號 varchar(10)
fetch next from cur2 into @憑證號
while @@fetch_status=0
begin
if @憑證號 in (select 支票號 from 單位日記帳 where 貸方金額=@借方金額)
fetch next from cur2 into @憑證號
else
begin
insert into 銀行已付單位未付 select * from 銀行對帳單 where 憑證號=@憑證號
fetch next from cur2 into @憑證號
end
end
close cur2
deallocate cur2
fetch next from cur1 into @借方金額,@個數
end
end
close cur1
deallocate cur1
8、篩選銀行對帳單相同金額貸方有單位日記帳借方無的記錄
declare cur1 cursor for select 貸方金額,count(*) 個數 from 銀行對帳單 where 貸方金額0 group by 貸方金額 having count(借方金額)>1
open cur1
declare @貸方金額 money,@個數 int
fetch next from cur1 into @貸方金額,@個數
while @@fetch_status=0
begin
if @個數 =(select count(*) from 單位日記帳 where 借方金額=@貸方金額)
fetch next from cur1 into @貸方金額,@個數
else
begin
insert into 銀行已收單位未收 select * from 銀行對帳單 where 貸方金額=@貸方金額
fetch next from cur1 into @貸方金額,@個數
end
end
close cur1
deallocate cur1
責任編輯：zoe