/*
会计信息系统实验报告
3120202307103 金书豪
*/

use UFDATA_999_2010

--1.请利用凭证表查询以下内容：
--(1)检索出第11会计期间收字2号凭证。
--select distinct csign from GL_accvouch
select * from GL_accvouch
where iperiod=11 and csign='收' and ino_id=2

--注意：如果查询凭证表中所有凭证，只需按iperiod,csign,ino_id排序即可。
select * from GL_accvouch
order by iperiod,csign,ino_id,inid

--(2) 检索出第11会计期间收字5号凭证。
select * 
from GL_accvouch 
Where iperiod=11 and csign='收' and ino_id=5

--(3) 检索出会计期间、凭证类型、凭证号、科目代码、摘要、借贷方向、金额、对方 --科目供审计人员查看。
select iperiod 期间,csign 凭证类型, ino_id 凭证号,ccode 科目代码, cdigest 摘要,
借贷方向=case 
	when md<>0 then '借' 
	else '贷' 
	end,
金额=case
	when md<>0 then md 
	else mc 
	end,
ccode_equal 对方科目 
from GL_accvouch

--(4) 检索出借记银行存款的记录。
select * 
from GL_accvouch 
Where ccode='1002%' and md>0

--(5) 检索出现金收支大于元的记录。(注意凭证和记录的区别)
select *
from GL_accvouch
Where ccode='1001' and mc>1000 
--现金支出

select * 
from GL_accvouch
Where ccode='1010' and (mc>1000 or md>1000) 
--现金收支

--查询现金支出大于元的凭证。
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='1001' and b.mc>1000 
Order By a.i_id
-- Where a.ccode='1001' and a.mc>100000
--条件错误,因为查凭证所在的表不能设条件。

---下面分两步走，便于大家理解查询凭证算法：
--先查出现金支出大于元的记录的iperiod,csign,ino_id,
--drop View ttt
Create view ttt
as
select iperiod,csign,ino_id 
from GL_accvouch
Where ccode='1001' and mc>=1000

--再查出现金支出大于元的凭证
select a.* 
from GL_accvouch a join ttt b
	on a.iperiod=b.iperiod
		and a.csign=b.csign
		and a.ino_id=b.ino_id
Order By a.i_id

--思考：检索出所有办公支出在元以上的记录
select a.* 
from dbo.GL_accvouch a 
	join dbo.GL_accvouch b
on a.iperiod=b.iperiod 
	and a.csign=b.csign 
	and a.ino_id=b.ino_id
Where (a.ccode like '1001' or a.ccode like '1002%') 
	and a.mc>1000 
	and (
		b.ccode like '6602%' 
		and b.md>0
		)

--进一步：检索出所有办公支出在元以上的凭证
select a.iperiod,a.csign,a.ino_id,a.cdigest,a.ccode,
Kmmc=(
	select distinct ccode_name 
	from code
	Where ccode=a.ccode
	),
a.md,a.mc 
from dbo.GL_accvouch a join dbo.GL_accvouch b 
	on a.iperiod=b.iperiod 
		and a.csign=b.csign 
		and a.ino_id=b.ino_id
	join dbo.GL_accvouch c 
	on a.iperiod=c.iperiod 
		and a.csign=c.csign 
		and a.ino_id=c.ino_id
Where 
(
	b.ccode like '1001' 
	or b.ccode like '1002%'
	) 
	and b.mc>1000 
	and 
	(
		c.ccode like '6602%' 
		and c.md>0
		)

--(6) 检索出第会计期间产成品减少的金额。
select ccode,iperiod,sum(mc) 产成品减少的金额
from GL_accvouch
Where ccode='1405' and mc>0 
Group By ccode,iperiod

--(7) cname字段记录了业务经手人，请计算各位经手人的现金支出金额和业务笔数。
select cname 业务经手人,sum(mc) 现金支出金额,count(*) 业务笔数 
from GL_accvouch
Where ccode='1001' and mc>0 
Group By cname
-- count(*)改为count(cname)则统计不全

select cname 业务经手人,sum(mc) 现金支出金额,count(*) 业务笔数 
from GL_accvouch
Where cname<>'' and ccode='1001' and mc>0 
Group By cname
--不包括未签字的

select * from GL_accvouch
Where cname is null and ccode like '1001' and mc>0
--经手人未签字的记录

--(8) 检索出全年产品销售收入贷方发生额。
Select sum(mc) 全年产品销售收入贷方发生额
From GL_accvouch
Where ccode like '6001%'
	and iperiod 
		between 1 and 12 
	and mc>0

--(9) 检索出全年原材料借方发生额。
Select sum(md) 全年原材料借方发生额
From GL_accvouch
Where ccode like '1403%' 
	and iperiod 
		between 1 and 12 
	and md>0

--(10) 检索出第会计期间应收账款收回的记录。
Select ccode 科目代码,iperiod 会计期间,mc 应收账款收回额
From GL_accvouch 
Where ccode like '1122%'
	and iperiod=12 
	and mc>0

--(11) 检索出全年计提折旧的记录。
Select ccode 科目代码,iperiod 会计期间,md 计提折旧额
From GL_accvouch
Where ccode like '1602%' 
	and iperiod 
		between 1 and 12 
	and md>0

--(12) 检索出现金支出为整万元的记录。
select * 
from GL_accvouch 
Where ccode='1001'
	and mc>0
	and cast(mc/10000.0 as int)=mc/10000.0
-- mc>0可否省略？不可！?cast(mc as int)%10000=0,错！因为先取整再除取余， 
--如.26，计算后余数亦为。

--(13) 计算出各月的银行存款支出合计数。
select iperiod 会计期间,sum(mc) 各月的银行存款支出合计
From GL_accvouch 
Where ccode like '1002%'
	and mc>0 
Group By iperiod

--(14) 创建一个视图，视图中包含：期间、凭证类别、凭证号、科目代码、 --摘要、借方金额、贷方金额、对方科目等内容，供审计人员浏览查看。
--drop View v8_1_14
Create view v8_1_14 
as
Select iperiod 期间,csign 凭证类型, ino_id 凭证号,ccode 科目代码,
cdigest 摘要,md 借方金额,mc 贷方金额,ccode_equal 对方科目 
from GL_accvouch
--Order By iperiod,csign,ino_id
--视图不可轻易排序
    
create view v8_1_14
(期间,凭证类别,凭证号,科目代码,摘要,借方金额,贷方金额,对方科目) 
as
select iperiod, csign,ino_id,ccode,cdigest,md,mc,ccode_equal 
from GL_accvouch

--从凭证表中检索以下内容(期间、凭证类型、凭证号、科目代码、摘要、借贷方向、金额)
select iperiod 期间,csign 凭证类型,ino_id 凭证号, ccode 科目代码, cdigest 摘要,
借贷方向= 
case 
  when md<>0 then '借' else '贷' 
  end,
  金额=
  case 
    when md<>0 then md else mc 
    end 
from GL_accvouch

--思考：创建一个视图，视图中包含以下内容供审计人员浏览查看 (期间,凭证类别,凭证号,科目名称,摘要,借贷方向,金额,对方科目)
--drop view v8_1_14
create view v8_1_14
as 
select 期间=iperiod,凭证类别=csign,凭证号=ino_id, 
科目名称=ccode_name, 
	摘要=cdigest,
	借贷方向=(
		case 
		when md<>0 then '借'
			else '贷' 
			end
		),
	金额=(
		case
			when md<>0 then md 
			else mc 
			end
		),
	对方科目=ccode_equal 
from GL_accvouch inner join code 
	on GL_accvouch.ccode=code.ccode

--(15) 检索出摘要中包含“劳务”、“费用”等内容的记录。
select * 
from GL_accvouch 
Where cdigest like '%劳务%' or cdigest like '%费用%' 
--不可用AND

--(16) 截止测试中关注期前期后事项，请检索出第会计期间的主营业务收入明细记录。
Select * 
from code 
Where ccode_name like '%收入%' 
--查看有关'收入'类的科目代码

select * 
from GL_accvouch 
Where iperiod=12 and ccode like '6001%' and mc<>0

--统计月份的主营业务收入明细账记录数
select count(*) 
from GL_accvouch
Where iperiod=12 
	and ccode like '6001%' 
	and mc<>0

--(17) 审计人员为检查凭证表的有效、完整性，对所有凭证文件的借方发生额、贷方发
--生额进行求和，以检查借贷方是否平衡。
select sum(md) 借方发生额合计,sum(mc) 贷方发生额合计 
from GL_accvouch
--总体平衡否(否)

select iperiod 会计期间,sum(md)借方合计,sum(mc) 贷方合计 
from GL_accvouch 
Group By iperiod
--发现不平，查看各会计期间平衡状况 

--(18) 检索出借贷方不平衡的凭证文件。
--查找不平衡的凭证,可以看到不平衡的凭证只有上年转入的凭证信息(因为当初录入时就没有完全录入)
select iperiod 会计期间,csign 凭证类型,ino_id 凭证号,sum(md)借方合计,sum(mc) 贷方合计 
from GL_accvouch
Group By iperiod,csign,ino_id 
having sum(md)<>sum(mc)

--(19) 汇总出各总账科目借贷方发生额合计。
--drop View w1,w2
Create view w1 
as
select left(ccode,4) 总账科目,sum(md) 借方总计,sum(mc) 贷方总计
from GL_accvouch Group By left(ccode,4) 
--包含了,20会计期间

--从余额表GL_accsum中汇总出各总账科目借贷方合计发生额。
Create view w2 
as 
select ccode 总账科目,sum(md) 借方总计,sum(mc) 贷方总计 
from GL_accsum 
Group By ccode 
having len(ccode)=4

select w1.*,w2.* 
from w1 join w2 
on w1.总账科目=w2.总账科目

--各会计期间各总账科目借贷方合计发生额
select iperiod 会计期间,left(ccode,4) 总账科目,sum(md) 累计借,sum(mc) 累计贷 
from GL_accvouch 
Group By iperiod,left(ccode,4)

select iperiod 会计期间,ccode 总账科目,md 累计借,mc 累计贷 
from GL_accsum 
Where len(ccode)=4 
Order By ccode

--(20) 检索出各二级明细科目借贷方发生额合计。
Select ccode 二级明细科目,sum(md) 累计借,sum(mc) 累计贷 
from GL_accvouch 
Where len(ccode)=6 
Group By ccode 

select ccode 总账科目,md 累计借,mc 累计贷
from GL_accsum 
Where len(ccode)=6 
Order By ccode

--(21)计算出各会计期间的现金支出合计金额
select iperiod 会计期间,sum(mc) 现金支出合计金额
from GL_accvouch
Where ccode='1001' and mc>0 and iperiod not in (0,20) 
Group By iperiod
--=============分割线==================
select iperiod 会计期间,sum(mc) 现金支出合计金额
from GL_accvouch
	Where ccode=(
	select distinct ccode 
	from code 
	Where ccode_name='库存现金'
	) and mc>0 
Group By iperiod
--=============分割线==================
select iperiod 会计期间,
科目名称=(
	select distinct ccode_name 
	from code 
	Where ccode=GL_accvouch.ccode
	),
sum(mc) 现金支出合计金额 
from GL_accvouch
Where ccode='1001' and mc>0 
Group By iperiod,ccode
--=============分割线==================
select iperiod 会计期间,sum(mc) 现金支出合计
from GL_accvouch
Group By iperiod,ccode
having ccode='1001'
--=============分割线==================
select iperiod 会计期间,mc 现金支出合计
from GL_accsum Where ccode like '1001' and mc>0
--思考：上述语句结果一样么？试分析之。(不完全一样，答案一样含义也不一样)

--(22)检索该单位全年登记收入科目的记账凭证数目；(若包含期间损益则是)
select ccode
from code
Where ccode_name like '%收入%'

select count(distinct a.ino_id)
from dbo.GL_accvouch a 
	join dbo.GL_accvouch b
	on a.iperiod=b.iperiod
		and a.csign=b.csign
		and a.ino_id=b.ino_id 
Where b.ccode like '6001%'
	and a.csign = '记'

select count(distinct a.ino_id)
from dbo.GL_accvouch a 
	join dbo.GL_accvouch b
	on a.iperiod=b.iperiod
		and a.csign=b.csign
		and a.ino_id=b.ino_id
Where b.ccode like '6001%' 
	and a.csign = '记'

select count(*) 
from dbo.GL_accvouch
Where (
	ccode like '6001%' 
		or ccode like '6051%' 
		or ccode like '6301%'
		) 
	and mc<>0

--检索该单位全年登记收入科目的记账凭证？
select a.iperiod,a.csign,a.ino_id,a.cdigest,a.ccode,
a.md,a.mc, a.ccode_equal,a.coutbillsign,a.coutid
from dbo.GL_accvouch a 
	join dbo.GL_accvouch b
	on a.iperiod=b.iperiod
		and a.csign=b.csign 
		and a.ino_id=b.ino_id
Where (
	b.ccode like '6001%' 
	or b.ccode like '6051%'
	or b.ccode like '6301%'
	) 
	and b.mc<>0 
Order By a.iperiod,a.csign,a.ino_id 

--(23)在这些凭证中，借方出现了哪些科目，出现次数；
select a.ccode,count(a.ccode)
from dbo.GL_accvouch a join dbo.GL_accvouch b
	on a.iperiod=b.iperiod
		and a.csign=b.csign 
		and a.ino_id=b.ino_id 
Where (
	b.ccode like '6001%' 
		or b.ccode like '6051%'
		or b.ccode like '6301%'
	) 
	and b.mc<>0
	and a.md<>0 
Group By a.ccode

--(24)在这些凭证中，贷方出现了哪些科目，出现次数；
select a.ccode,count(a.ccode)
from dbo.GL_accvouch a join dbo.GL_accvouch b
on a.iperiod=b.iperiod
	and a.csign=b.csign
	and a.ino_id=b.ino_id
Where b.ccode like '6001%'
	and b.mc<>0
	and a.mc<>0 
Group By a.ccode


--2.请利用余额表查询以下内容：
--(1) 检索出各总账科目的年初余额。
select ccode 科目代码,cbegind_c 年初余额方向,mb 年初余额
from GL_accsum
Where iperiod=1 and len(ccode)=4
Order By ccode

--(2) 检索出各二级明细科目的各月借贷方发生额。
select ccode,iperiod,md,mc
from GL_accsum
Where len(ccode)=6

--(3) 检索出销售收入与销售成本科目各月发生额，供审计人员对比分析。
select ccode,ccode_name
from code
Where ccode_name like '%收入%'
	or ccode_name like '%成本%' 

select ccode,iperiod,md,mc 
from GL_accsum 
Where ccode like '6001%' 
	or ccode like '6401%'

select a.ccode,a.iperiod,a.md,a.mc,b.ccode,b.iperiod,b.md,b.mc
from GL_accsum a join GL_accsum b 
on a.iperiod=b.iperiod 
Where a.ccode like '6001%' and b.ccode like '6401%'
--自连接

--检索出销售收入贷方与销售成本科目借方各会计期间发生额，供审计人员对比分析
Select a.ccode,a.iperiod,a.mc,b.ccode,b.md 
from GL_accsum a join GL_accsum b 
on a.iperiod=b.iperiod 
Where a.ccode like '6001%' and b.ccode like '6401%'

--(4) 检索出各总账科目的年末余额。
select ccode,cendd_c,me 
--into qmye
from GL_accsum
Where iperiod=12 and len(ccode)=4
Order By ccode
--思考：如何利用凭证表实现之？(需凭证表、余额表连接)

--4-1)利用凭证表求出各总账科目全年累计借贷方发生额
--drop table pzhj
select left(ccode,4) 总账科目代码,sum(md) 累计借,sum(mc) 累计贷 
into pzhj 
from GL_accvouch 
Where iperiod between 1 and 10 
Group By left(ccode,4)

--4-2)利用余额表得到各总账科目年初数(正数表示借，负数表示贷) 
--drop table qcye
select ccode 总账科目代码,年初余额=(
	case 
	when cbegind_c in('借','平') then mb
	else -mb
	end 
	) 
into qcye
from GL_accsum
Where len(ccode)=4 and iperiod=1

--4-3)利用上述两中间表得到各总账科目年末数(正数表示借，负数表示贷)
--drop table qmye1
select a.总账科目代码,年末余额=a.年初余额+isnull(b.累计借,0)-isnull(b.累计贷,0)
into qmye1
from qcye a left join pzhj b
	on a.总账科目代码=b.总账科目代码 
Order By a.总账科目代码

--4-4)利用上表得到各总账科目年末数及方向 
--drop table qmye2
select 总账科目代码,年末余额方向=(
	case
		when 年末余额>0 then '借' 
		when 年末余额=0 then '平'
		else '贷' 
		end
	), 
年末余额=abs(年末余额) 
--into qmye2 
from qmye1

--对照比较
select a.*,b.*
from qmye a join qmye2 b
	on a.ccode=b.总账科目代码

--(5) 检索出收入科目(总账和明细账科目) 各月贷方发生额。
select ccode,ccode_name 
from code 
Where ccode_name like '%收入%'

--查找收入类科目的科目编码
select ccode,iperiod,mc 
from GL_accsum 
Where (
	ccode like '6001%' 
	or ccode like '6051%' 
	or ccode like '6301%'
	) 
and mc>0 

select ccode,iperiod,mc 
from GL_accsum
Where ccode in ('6001','6051','6301') and mc>0
--思考：如何利用凭证表实现之？

--(6) 检索出应收账款(总账和明细账) 科目的年末余额。
select ccode,ccode_name 
from code 
Where ccode_name like '应收%'

select ccode,cendd_c,me 
from GL_accsum 
Where ccode like '1122%' and iperiod=12

--检索出应收账款(总账)科目的本年各期余额
select iperiod,cbegind_c,mb,md,mc,md-mc 发生额,me,cendd_c 
from GL_accsum 
Where ccode like '1122' 
Order By iperiod

--查询总账科目年初余额
--drop view v_accsum
create view v_accsum
as 
select ccode,年初余额=
case 
	when cbegind_c like '借' then mb 
	when cbegind_c like '贷' then -mb 
	end
from dbo.GL_accsum 
Where iperiod=1 and len(ccode)=4

select * from v_accsum

select ccode 科目代码, cbegind_c 借贷,年初余额=mb
from dbo.GL_accsum 
Where iperiod=1 and len(ccode)=4

--查询总账科目借贷方发生额
--drop view v_accvouch 
create view v_accvouch 
as
select left(ccode,4) 科目代码,sum(md) 借方发生额,sum(mc) 贷方发生额
from dbo.GL_accvouch 
Group By left(ccode,4)

select ccode 科目代码,isnull(年初余额,0) 年初余额, 
isnull(年初余额,0)+isnull(借方发生额,0)-isnull(贷方发生额,0) 年末余额 
from v_accsum full join v_accvouch 
	on v_accsum.ccode= v_accvouch.科目代码
Order By ccode

--查询各客户的赊销状况
select a.ccus_id 客户代码,(
	select cCusName 
	from dbo.Customer
	Where cCusCode=a.ccus_id
	) 客户名称,
sum(a.md)赊销金额,count(*)赊销笔数
from dbo.GL_accvouch a join dbo.GL_accvouch b
on a.iperiod=b.iperiod 
	and a.csign=b.csign 
	and a.ino_id=b.ino_id
Where a.ccode like '1122%'
	and a.md>0
	and b.ccode like '6001%'
	and b.mc>0 
Group By a.ccus_id

--(7) 检索出损益类科目(总账及明细账) 各月发生额。
select ccode,iperiod,md,mc 
from GL_accsum 
Where left(ccode,1)='6'

--(8) 检索出资产类科目的总账，显示结果包括科目代码、期初余额、当期借贷方 --发生额和期末余额。
select ccode 科目代码,iperiod 会计期间,mb 期初余额,
md 借方发生额,mc 贷方发生额,me 期末余额
from GL_accsum 
Where len(ccode)=4 and left(ccode,1)='1' 

--(9)检索出销售收入与销售成本(总账)科目各会计期间发生额。
select a.ccode,a.iperiod,a.md,a.mc,b.ccode,b.iperiod,b.md,b.mc 
from GL_accsum a join GL_accsum b
	on a.iperiod=b.iperiod 
Where a.ccode like '6001' and b.ccode like '6401'
--Where b.ccode like '6001' and a.ccode like '6401'

--思考：如何利用凭证表实现之？(见下面语句)
select left(ccode,4) 科目代码,iperiod 会计期间,sum(md) 借方合计,sum(mc) 贷方合计
from GL_accvouch 
Where ccode like '6001%'or ccode like '6401%' 
Group By iperiod,left(ccode,4)

--(10)检索出各总账科目的各会计期间借贷方发生额。
select iperiod 会计期间,ccode 科目代码,md 累计借,mc 累计贷 
from GL_accsum
Where len(ccode)=4
Order By ccode
--Where ccode in (
--	select ccode 
--	from code 
--	Where Igrade=1
--	)


--3.请利用总账数据表查询以下内容：

--(1) 创建一个视图，视图中包含以下内容供审计人员浏览查：期间、凭证类型、
--凭证号、科目代码、科目名称、摘要、借方金额、贷方金额、对方科目
--drop view v6_3_1
Create view v6_3_1
as
select iperiod 期间,csign 凭证类型, ino_id 凭证号,a.ccode 科目代码, 
ccode_name 科目名称,cdigest 摘要,md 借方金额,
mc 贷方金额,ccode_equal 对方科目 
from GL_accvouch a join code 
	on a.ccode=code.ccode

create view v6_3_1
(期间,凭证类型,凭证号,科目代码,摘要,借方金额,贷方金额,对方科目) 
as
select iperiod, csign,ino_id,ccode,cdigest,md,mc,ccode_equal 
from GL_accvouch

select * from V6_3_1 
Order By 期间,凭证类型,凭证号,科目代码

--(2) 创建一个视图，视图中包含以下内容供审计人员浏览查看：总账科目代码、总账科目 --名称、年初余额方向、年初余额。
--drop view v6_3_2 
create view v6_3_2
as
select a.ccode 科目代码,ccode_name 总账科目名称,
	cbegind_c 年初余额方向,mb 年初余额
from GL_accsum a join code b
	on b.ccode=a.ccode
Where len(a.ccode)=4 and iperiod =1

select a.ccode 科目代码,总账科目名称=(
	select distinct ccode_name 
	from code 
	Where ccode=a.ccode
	),
cbegind_c 年初余额方向,mb 年初余额
from GL_accsum a 
Where len(ccode)=4 and iperiod =1

select ccode 科目代码,cbegind_c 年初余额方向,mb 年初余额 
from GL_accsum
Where len(ccode)=4 and iperiod =1


--（二）会计数据分析方法

--1.基于Transact-SQL的筛选查询

--(1)会计人员关注特殊的负值，如盘存类库存商品的现存量出现负值是明显的异常情况，说明该单位存货管理存在问题。会计人员检查现存量表，筛选现存量为负值的物料进行追踪。
Select cinvcode,iquantity
From CurrentStock
Where iquantity<0

--2.基于Transact-SQL的排序
--(1)会计人员在对存货出入库记录进行分析时，通过按仓库代码进行排序，发现仓库代码的编码方式为从开始的顺序编码，将仓库按编码排序后，发现有仓库编码为空的销售记录。通过对特殊仓库编码的进一步取证，发现异常的仓库线索。
Select cinvcode, cWhCode, sbvid, iUnitPrice,
	iQuotedPrice,iTax, iTaxUnitPrice, imoney,isum
From salebillvouchs
Order by cWhCode, cinvcode

--(2)会计人员关注某单位银行存款支付的明细记录，按银行存款支出降序排列，检索异常事项。
Select * 
From gl_accvouch
Where ccode like '1002%' and mc<>0
Order by mc desc

--(3)会计人员关注商品销售价格是否超出商品价目表的单价，检查销售发票中的销售单价并降序排列。
Select *
From salebillvouchs
Order by cinvcode,inatunitprice desc

--3.基于Transact-SQL的统计

--(1)会计人员关注采购价格的变化是否在合理的范围之内，对某年度各种物料的采购单价进行统计分析，
--观察每种物料的最高单价、最低单价、价差及采购业务笔数、总金额的变化区间。
Select cinvcode,max(icost) max,min(icost) min,
	max(icost)-min(icost) cha, count(*) sl,sum(imoney) je
From PurBillVouchs
Group by cinvcode

/*(2)审计组对某企业进行财务收支审计。会计人员分析业务单位业务流及会计信息系统数据后发现，
销售发票子表中，字段iNatSum 记录发票表中某项存货的应收价税金额，iMoneySum 记录发票中某项存货的
实际收回金额。销售款项收回后，会计人员依据收款单逐项核销每张销售发票明细，核销金额记入iMoneySum 字段。
会计人员重点关注款项未完全收回的销售业务。统计尚未完全收回款项的发票，结果显示发票号、应收金额、
收回金额、尚未收回的金额。*/
Select a.csbvcode, sum(b.inatsum) ys, sum(b.imoneysum) ss,
	cha=sum(b.inatsum)-sum(b.imoneysum)
From salebillvouch a join salebillvouchs b 
	on a.sbvid=b.sbvid
Group by a.csbvcode
having sum(b.inatsum)<>sum(b.imoneysum)

--4.基于Transact-SQL的连续性检查

--(1)会计人员检查销售发票的重复编号情况。
Select cvouchtype, csbvcode, count(*)
From salebillvouch
Group by cvouchtype, csbvcode having count(*)>1 

--(2)会计人员检查销售发票连续编号的情况，检索不连续的发票，为进一步追查取证获取线索。
Select cvouchtype,max(csbvcode),min(csbvcode),
	count(distinct csbvcode)
From salebillvouch
Group by cvouchtype

--由于本实例中发票分类编号的特殊性，会计人员可继续追查断号的发票号。
--drop view fp2
Create View fp2
as
Select cvouchtype,csbvcode,
id=row_number()over(
	Order by [cvouchtype],[csbvcode]
	)
From salebillvouch

Select a.cvouchtype, a.csbvcode,b.csbvcode,
	a.id,b.id,cast(
		right(b.csbvcode,5) as int
		)-cast(
		right(a.csbvcode,5) as int
		)
	From fp2 a join fp2 b
on b.id=a.id+1 and a.cVouchType=b.cVouchType
Where cast(
	right(b.csbvcode,5
	) as int)-cast(
	right(a.csbvcode,5) as int
	)<>1

--5.基于Transact-SQL的账龄分析

--(1)会计人员关注跨期交易或事项，将发货日期与记账日期进行比对，查询显示每笔收入确认时的记账日期、发货日期及二者的天数差，结果按天数降序排列。
Select c.iperiod, c.csign, c.ino_id, c.ccode, a.ddate, c.dbill_date,
	datediff(day,a.ddate,c.dbill_date) as ts 
From dispatchlist a
	join salebillvouch b 
		on a.sbvid=b.sbvid 
			or (a.cdlcode=b.cdlcode and b.sbvid=0) 
	join gl_accvouch c 
		on b.cvouchtype =c.coutbillsign 
			and b.csbvcode=c.coutid
Where ccode='6001'
Order by datediff(day,a.ddate,c.dbill_date) desc


--6.基于Transact-SQL的表达式与计算
/*(1)会计人员关注发票金额及发货金额计算的准确性，请依据发票所列单价计算获得发票的无税金额、
含税金额、税额，并与发票中所列的无税金额、含税金额、税额进行比较并计算出与原表中无税金额、
含税金额、折扣额的差额。注：无税金额＝无税单价*数量；含税金额l= 含税单价*数量；含税金额= 无税金额+税额。*/
Select cinvCode, inatunitprice, iquantity,
	wsje=iNatUnitPrice*iQuantity, hsjel=iTaxUnitPrice*iQuantity,
	hsje2=iNatUnitPrice*iQuantity+inattax, wscel=iNatUnitPrice*iQuantity-iNatMoney, 
	hscel=iTaxUnitPrice*iQuantity-inatsum, hsce2=iNatUnitPrice*iQuantity+inattax-inatsum
From SaleBillVouchs

--(2)审计过程中，会计人员根据需要，将凭证表的借方金额、贷方金额字段进行整理，构造为借贷标志、
--金额两个新字段，查询结果包括会计期间、凭证类型、凭证号、摘要、科目代码、借贷标志和金额。
Select iperiod, csign, ino_id, cdigest, ccode,
	bz=case 
		when md<>0 then 'j'
		else 'd'
		end,
	je=md+mc
From GL_accvouch
Order by iperiod, csign, ino_id

--7.基于Transact-SQL的分组查询

--(1)会计人员关注银行存款的业务是否有异常，将对应科目为银行存款的全部记录进行分组，
--统计每个科目的累计借、贷方发生额及业务笔数。
Select ccode, sum(md), sum(mc), count(*)
From gl_accvouch
Where ccode_equal like '1002%'
Group by ccode

--(2)会计人员关注凭证表中的总账科目已记账数据，请按会计期间对凭证表中的总账科目进行分组计算。
--统计每个会计期间、每个总账科目的累计借、贷方发生额。
Select iperiod,left(ccode,4),sum(md),sum(mc)
From gl_accvouch
Where ibook=1
Group by iperiod,left(ccode,4)

--8.基于Transact-SQL的分层

--(1)会计人员关注企业销售收入的真实性，对主营业务收入明细记录进行审计抽样。请将主营业务收入分五层，
--统计每层金额、业务笔数、每层金额占总金额的比率、每层业务笔数占总业务的比率，以确定审计抽样的重点。

--(a) 统计基本信息
Select max(mc),max(mc)/5,count(*) ,sum(mc)
From gl_accvouch
Where ccode like '6001%' and mc<>0

--(b) 分层汇总
Select ceiling(mc/116820.51),count(*),count(*)/25.0,
	sum(mc),sum(mc)/1827886.97
From gl_accvouch
Where ccode like '6001%' and mc>0
Group by ceiling(mc/116820.51)
Order by ceiling(mc/116820.51)

--(2)会计人员关注企业销售收入的真实性，对主营业务收入明细记录进行审计抽样。请将主营业务收入每万元分一层，
--统计每层金额、业务笔数、每层金额占总金额的比率、每层业务笔数占总业务的比率，以确定审计抽样的重点。
Select ceiling(mc/80000.00),count(*),count(*)/25.O,
sum(mc),sum(mc)/1827886.97
From gl_accvouch
Where ccode like '6001%' and mc>0
Group by ceiling(mc/80000.00)
Order by ceiling(mc/80000.00)

--(3)会计人员关注企业销售收入的真实性，对主营业务收入明细记录进行审计抽样。将主营业务收入明细账记录从正的
--最小值开始到最大值分层，统计每层业务笔数、金额以及占总业务笔数、金额的比率。

--(a) 统计基本信息
Select min(mc) minmc, max(mc) maxmc, (max(mc)-min(mc))/10 cha, count(*) shu,sum(mc) je
From gl_accvouch
Where ccode like '6001%' and mc>0

--(b) 分层汇总
Select ceiling(
	case
		when mc=1153.84 then 1/58294.87
		else (mc-1153.84)/ 58294.87
		end
	) 层,
	count(*) 记录数,count(*)/25.0 记录比率,sum(mc) 本层总额,
	sum(mc)/1827886.97 本层金额比率
From gl_accvouch
Where ccode like '6001%'and mc>0
Group by ceiling(
	case
		when mc=1153.84 then 1/58294.87
		else (mc-1153.84)/58294.87
		end
	)

--(4)会计人员关注企业销售收入的真实性，对主营业务收明细记录进行审计抽样并将主营业务收入明细账记录分为层，
--包括万元以下、万(含万)-10万元、10万(含万)-20万元、万(含万) 元以上，统计每层业务笔数、金额以及占总业务笔数、
--金额的比率。

--(a) 创建中间表
--drop view aal
Create View aal
as
Select ceng=case
	when mc<50000 then 1
	when mc>=50000 and mc<100000 then 2
	when mc>=100000and mc<200000then 3
	else 4
	end,*
From gl_accvouch
Where ccode like '6001%' and mc>0

--(b) 统计基本数据
Select sum(mc),count(*)
From aal

--(c) 分层汇总
Select ceng, sum(mc) summc, sum(mc)/1827886.97 金额比率,
	count(*) 业务笔数,count(*)/25.0 业务笔数占比
From aal
Group by ceng

--9.基于Transact-SQL的连接查询

--(1)会计人员关注高额的现金支出，期望检索出高额现金支出的来龙去脉，利用凭证表，检索出现金收支大于000元的记账凭证。
Select a.*
From gl_accvouch a join gl_accvouch b
on a.iperiod=b.iperiod 
	and a.ino_id=b.ino_id
	and a.csign=b.csign
Where b.ccode='lO0l'and(b.mc>5000 or b.md>5000)

--(2)会计人员关注赊销的执行情况，检索出所有赊销收入的明细账记录供会计人员进行抽样。
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 like '6001%' and b.ccode like '1122%'

--(3) 会计人员关注赊销控制的执行情况，检索出所有赊销收入的记账凭证供审计入员进行抽样。
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 join gl_accvouch c
on a.iperiod=c.iperiod and a.csign=c.csign
	and a.ino_id=c.ino_id
Where (
	c.ccode like '6001%' and c.mc<>0
	) and (
	b.ccode like '1122%' and b.md<>0
	)
Order by a.iperiod, a.csign, a.ino_id, a.inid

--(4)会计人员对科目代码级次为-2-2结构的科目代码表进行整理，生成一张包含科目代码、科目全称、
--末级标志的新的科目代码表。
Select a.ccode,a.bend, kmqc=b.ccode_name+case
	when len(a.ccode)>4 then '\'+c.ccode_name
	else ''
	end+ case
	when len(a.ccode)>6 then '\'+d.ccode_name
	else ''
	end
From code a join code b 
	on left(a.ccode,4)=b.ccode join code c
	on left(a.ccode,6)=c.ccode join code d
	on left(a.ccode, 8)=d.ccode

--(5)会计人员关注销售过程中是否按税法规定确认了增值税的销项税，请利用凭证表检索出确认销售收入
--却未确认应交增值税的主营业务收入明细账记录。

--(a) 构建收入视图
Create View a_6001
as
Select * From gl_accvouch
Where ccode like '6001%' and mc<>0

--(b) 构建税金视图
Create View a_2221
as
Select * From gl_accvouch
Where ccode like '2221%' and mc<>0

--(c) 外连接检索记录
Select a.*
From a_6001 a left join a_2221 b
	on a.csign=b.csign and a.iperiod=b.iperiod
		and a.ino_id=b.ino_id
Where b.ccode is null
--在上述程序使用中，注意外连接时使用单条查询语句时，有时候不注意条件，容易造成筛选条件间互斥而执行无结果，
--此时要慎重使用单条语句进行查询，注意连接条件与筛选条件的写法。

Select a.*
From gl_accvouch a left join gl_accvouch b
	on a.csign=b.csign and a.iperiod=b.iperiod and a.ino_id=b.ino_id
		and b.ccode like '2221%' and b.mc<>0
Where a.ccode like '6001%'
	and a.mc<>0 and b.ccode is null

--(6) 会计人员关注销售发票与发货单上同种商品的数量、金额是否一致。
--drop view fp,fh
Create View fp
as
Select cinvcode, SUM(iquantity) fpsl,SUM(inatmoney) fpje
From salebillvouchs
Group by cinvcode

Create View fh
as
Select cinvcode, SUM(iquantity) fhsl, SUM(inatmoney) fhje
From dispatchlists
Group by cinvcode

Select *
From fp full join fh
	on fp.cinvcode=fh.cinvcode
Where isnull(fpsl,0)<>isnull(fhsl,0)
	or isnull(fpje,0)<>isnull(fhje,0)

--10.基于Transact-SQL的比率分析

--(1)会计人员关注商品销售的价格，计算每种商品销售的最高售价、最低售价、最高售价与最低售价之比，找出异常事项。
Select cinvcode,max(inatunitprice) zg,min(inatunitprice) zd,
	max(inatunitprice)/min(inatunitprice) bl
From salebillvouchs
Group by cinvcode
Order by max(inatunitprice)/min(inatunitprice) desc

--11.基于Transact-SQL的数字分析

--(1)会计人员关注某企业可能存在的高买低卖或关联交易情况，对该公司销售客户与采购供应商进行分组查询，对比其中既是供应商又是客户的交易总额。

--(a) 构建客户视图
Create View kh
as
Select ccuscode,ccusname,sum(inatmoney) je,count(*) bs, sum(idiscount) zk
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid
Group by ccuscode, ccusname

--(b) 构建供应商视图
Create view gys
as
Select a.cVenCode, cvenname, sum(imoney) je,
	count(*) sl, sum(iSum) js
From PurBillVouch a
	join PurBillVouchs b
		on a.pbvid=b.pbvid
	join vendor c
		on a.cvencode=c.cvencode
group by a.cVenCode, cvenname

--(c) 对比查询
Select *
From kh join gys
	on kh.ccusname=gys.cVenName

--12.班福法则

--（1）会计人员关注主营业务收入记录是否有异常数据，利用班福法则，计算主营业务收入首位数字出现的频率
--(实际频率＝实际出现的次数/总交易次数) ，并与标准频率进行比对，确定重点关注的交易事项。
Select left(mc,1),
	count(*)/(Select cast(count(*) as decimal) 
	From gl_accvouch
Where ccode='6001' and mc<>0) 实际频率,
	log(1+1/cast(left(mc,1) as decimal))/log(10) 标准频率
From gl_accvouch
Where ccode='6001' and mc<>0
Group by left(mc,1)

--思考题:

--1.2010年月日审计组对某企业进行财务收支审计。审计人员关注销售收入的真实性，检查未完全收回的销售业务。审计人员分析被审计单位业务流及会计信息系统数据后发现，销售发票子表中字段iNatSum记录发票某项存货的应收价税金额，iMoneySum记录发票某项存货的实际收回金额。销售款项收回后，会计员依据收款单逐项核销销售发票明细，核销金额记入iMoneySum字段。审计人员重点关注款项未完全收回的销售业务。请统计至审计日尚未完全收回款项的发票，查询发票开票日距离审计日的天数、每张发票中未完全收回款项的数量、尚未收回的金额。

--2.审计人员关注往来对冲的清况，请利用凭证表检索出往来对冲的明细账记录。

--3.查找各月赊销收入总额。

--4.计算各月收回的销售欠款(应收账款) 累计发生额。

--5.计算各月收回的销售欠款(应收账款) 的记账凭证。

--6.检索出赊销收入的记账凭证供审计人员查阅。

--7.检索出开出发票未确认收入的记录供审计人员分析。

--8.审计人员采用平行计算方法进行账表核对。审计人员确认了根据账户主文件(余额表) 记录的年初余额是正确的，在此基础上，审计人员汇总凭证表中已记账的交易记录，并行计算出各总账科目的年末余额；将计算的年末余额与原账户主文件中的年末余额进行对比，显示出有差额的记录。显示结果包括总账科目代码、年初余额、审计计算的年末余额、原年末余额、差额。其中余额的方向用“+”、“-”号表示，借方为“+”金额，贷方为“-”金额。

--9.计算各月的资产负债率，保留两位小数并按比率降序排列，显示结果包括会计期间、资产负债率。

--10.计算销售收入第二位数字出现的频率。

--11.请将旧会计准则下-2-2级次结构的科目代码表整理为新准则下的-3-3结构的科目代码，不足部分一级科目在第一位和第二位中间补, 二、三级科目在前面补。

--12.请将第题中的新科目代码表整理成包含科目代码、科目全称和末级标志的审计中间表。
select ccode 科目代码,ccode_name 科目名称,bend 科目末级标志
from code 
--不符合题意

--下面语句显示结果变成了“三级科目名\一级科目名\二级科目”，错误。
select km.ccode,km.ccode_name+
	case
		when len(km.ccode)>4 then '\'+k1.ccode_name
		else ''
		end
	+case
		when len(km.ccode)>6 then '\'+k2.ccode_name
		else ''
		end 科目全称,
	km.bend
from code km join code k1
	on k1.ccode=left(km.ccode,4) join code k2
	on k2.ccode=left(km.ccode,6)

------------------------下面的语句结果只有一级科目名--------------------
select km.ccode,k1.ccode_name+
	case
		when len(k1.ccode)>4 then '\'+k2.ccode_name
		else ''
		end
	+case 
		when len(k2.ccode)>6 then '\'+k3.ccode_name
		else ''
		end 科目全称,
	km.bend 
from code km join code k1
	on k1.ccode=left(km.ccode,4) join code k2
	on k2.ccode=left(km.ccode,6) join code k3
	on k3.ccode=left(km.ccode,8)

------------------------------下面是正确表达方法-----------------------
select km.ccode 科目代码, 科目全称=k1.ccode_name+
	case
		when len(km.ccode)>4 then '\'+k2.ccode_name
		else ''
	end + case 
		when len(km.ccode)>6 then '\'+k3.ccode_name
		else ''
	end,km.bend
from code km join code k1
	on k1.ccode=left(km.ccode,4) join code k2
	on k2.ccode=left(km.ccode,6) join code k3
	on k3.ccode=left(km.ccode,8)
Order By km.ccode

---------------------------3344-------------------------------------
select km.ccode 科目代码, 科目全称=k1.ccode_name+
	case
		when len(km.ccode)>3 then '\'+k2.ccode_name
		else ''
	end + case 
		when len(km.ccode)>6 then '\'+k3.ccode_name
		else ''
	end + case
		when len(km.ccode)>10 then '\'+k4.ccode_name
		else ''
	end , case
		when km.bend=1 then '是'
		else '否'
		end 是否末级
from code km join code k1
	on k1.ccode=left(km.ccode,3)
		join code k2 
	on k2.ccode=left(km.ccode,6)
		join code k3 on k3.ccode=left(km.ccode,10)
		join code k4 on k4.ccode=left(km.ccode,14)
Order By km.ccode

----整理科目表
--drop table km1
select km.ccode,k1.ccode_name+
	case
		when len(km.ccode)>3 then '\'+k2.ccode_name
		else ''
	end+ case
		when len(km.ccode)>5 then '\'+k3.ccode_name
		else ''
	end+ case
		when len(km.ccode)>7 then '\'+k4.ccode_name
		else ''
	end+ case
		when len(km.ccode)>9 then '\'+k5.ccode_name
		else ''
	end kmmc,km.bend 
into km1
from code km join code k1
	on left(km.ccode,3)=k1.ccode
		join code k2
	on left(km.ccode,5)=k2.ccode
		join code k3
	on left(km.ccode,7)=k3.ccode
		join code k4
	on left(km.ccode,9)=k4.ccode
		join code k5
	on left(km.ccode,11)=k5.ccode

--13.审计人员关注折旧计提的科目确认是否正确，请检索出计提折旧的记账凭证。

--14.审计人员关注销售过程中是否按税法规定确认了增值税的销项税，请利用凭证表检索出确认销售收入却未确认应交增值税的记账凭证。

--15.审计人员为了检查所转换数据的有效性和完整性，将凭证文件中已记账的末级科目各月发生额与余额文件的末级相关科目发生额进行一致性检查。

--16.毛利率是反映销售变化的一个重要比率，毛利率=(销售收入-销售成本-销售税金及附加)/销售收入。审计人员关注销售收入的异常波动情况，请利用凭证表检索出各月所有的销售收入、销售成本、销售税金及附加，并计算各月的销售毛利率(以%表示)并降序排列。


--（三）会计数据测试

--1.存在或发生的控制测试

--(1)会计人员从主营业务收入明细账追查每笔确认销售的业务有无发票。
Select *
From gl_accvouch a left join salebillvouch b
	on a.coutbillsign=b.cvouchtype and a.coutid=b.csbvcode
Where a.ccode='6001' and a.mc<>0 and b.sbvid is null

--(2)会计人员从销售发票追查每张发票是否附有发货凭证。
Select *
From salebillvouch a left join dispatchlist b
	on a.sbvid=b.sbvid 
		or (a.cdlcode=b.cdlcode and b.sbvid=0)
Where b.cdlcode is null

--(3)会计人员从销售发票追查每张发票是否附有经批准的销售单。
Select *
From salebillvouch a
	left join so_somain b
	on a.csocode=b.csocode
Where b.csocode is null

--(4)会计人员从销售发票追查每张发票是否都有订单。
Select *
From salebillvouch a
	left join SO_SOMain b
	on a.csocode=b.csocode
Where b.csocode is null

--2.完整性控制测试

--(1)会计人员根据所有经批准的销售单追查是否都开具了发运凭证。
Select * 
From so_somain a
	left join dispatchlist b
	on a.csocode=b.csocode
Where b.dlid is null

--(2)会计人员根据所有开具的发运凭证确认是否都开了销售发票。
Select *
From dispatchlist a
	left join salebillvouch b
	on a.sbvid=b.sbvid
		or (a.cdlcode=b.cdlcode and a.sbvid=0)
Where b.sbvid is null

--(3)会计人员根据所有开具的销售发票追查是否都巳入账。

--(a) 创建入账的收入视图
--drop view sr
Create View sr
as
Select * 
From gl_accvouch
Where ccode='6001' and mc<>0

--(b) 检索有发票未入账的记录
Select a.*
From salebillvouch a
	left join sr b 
	on a.cvouchtype=b.coutbillsign
		and a.csbvcode=b.coutid
Where b.ccode is null

--3. 计价、分摊、准确性控制测试

--(1)会计人员关注银行存款日记账是否都定期与银行进行对账，请将对账不一致的记录检索出来。

--(a) 创建银行存款日记账记录
--drop view pz
Create View pz
as
Select dbill_date,ccode,fx=
	case
		when md<>0 then 0
		else 1
		end,
	je=md+mc
From gl_accvouch
Where ccode like '1002%'

--(b) 将银行存款日记账与银行对账单核对
Select *
From pz a full join RP_bankrecp b
on a.fx=b.bd_c and a.je=b.mmoney
Where a.je is null
	or b.mmoney is null

--4.分类测试

--(1)会计人员关注客户往来的分类辅助核算准确性，请检索辅助账中二级明细科目的汇总金额与余额表中的二级明细科目余额是否相符。

--(a) 创建辅助账视图
--drop view mx
Create View mx
As
Select left(ccode,6) ccode, iperiod, sum(mb) qc,
	sum(md) md, sum(mc) mc, sum(me) me
From gl_accass
Group by left(ccode,6), iperiod

--(b) 与余额表进行比对
Select a.iperiod,a.ccode,a.mb,
	a.md,a.mc,a.me,b.*
From gl_accsum a join mx b
	on a.iperiod=b.iperiod and a.ccode=b.ccode
Where a.mb<>b.qc or a.me<>b.me 
	or a.mc<>b.mc or a.md<>b.md

--5.截止测试

--(1)检查截止审计日(2010年月日) 尚未开具发票的发货明细，显示单据类别、发货单号、距离审计日的月份差，按月份差降序排列。
Select a.cvouchtype, a.cdlcode, b.sbvid, datediff(month, a.ddate,'2010-12-31') y
From dispatchlist a left join salebillvouch b
	on a.sbvid=b.sbvid or (a.cdlcode=b.cdlcode and a.sbvid=0)
Where b.sbvid is null
Order by datediff(month, a.ddate,'2010-12-31') desc

--6.列报控制测试

--(1)会计人员利用总账余额表，平行模拟资产负债表中的资产总额、负债总额、所有者权益总额，分析各月资产负债是否平衡；获取业务单位资产负债表(实例中名为bb) 后，与由会计人员平行模拟出的报表系统出具的报表进行比对，以确认内部控制得到有效执行。
--drop view zc,fz
Create View zc
as
Select iperiod,sum(
	case
		when cbegind_c like '借' then mb
		else -1*mb
		end
	) mb,
	sum(
	case when cendd_c like '借' then me
	else -1*me
	end
	) me
From gl_accsum
Where (left(ccode,1)=1 or left(ccode,1)=5)
	and len(ccode)=4
Group by iperiod

Create View fz
as
Select iperiod,sum(
	case
		when cbegind_c like '借'then mb
		else -1*mb
		end
	) mb,
	sum(
	case
	when cendd_c like '借'then me
	else -1*me
	end
	) me
From gl_accsum
where(
	left(ccode,1)=2
		or left(ccode,1)=4
	)
	and len(ccode)=4
Group by iperiod

Select zc.iperiod,zc.mb zc,
	fz.mb fc,zc.me zm,fz.me fm,
	zc.mb+fz.mb cph,zc.me+fz.me mph
From zc join fz
	on zc.iperiod=fz.iperiod


--7.存在或发生的实质性测试

--(1)会计人员关注所有入账金额是否都开了发票，比对入账金额与发票金额，检索出未开发票的入账记录，显示结果包括会计期间、凭证类别、凭证号、凭证金额和发票金额。

--①创建发票信息视图
--drop view fp
Create View fp
As
Select a.cvouchtype,a.csbvcode,sum(inatmoney) je
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid
Group by a.cvouchtype,a.csbvcode

--②核对发票与记账信息
Select a.iperiod,a.csign,a.ino_id,a.mc,b.je
From GL_accvouch a left join fp b
	on a.coutbillsign=b.cvouchtype
		and a.coutid=b.csbvcode
Where a.ccode='6001'
	and a.mc<>0
	and a.mc<>isnull(b.je,0)

--(2)会计人员核查企业已入账的主营业务收入明细账中记录的销售发票号码与单据类别以确定是否存在重复入账。
Select coutbillsign, coutid, count(*)
From gl_accvouch
Where ccode ='6001' and mc<>0
Group by coutbillsign, coutid
having count(*)>1

--(3)会计人员检查主营业务收入明细账中与销售分录相应的发货单，检查授权审批人的签字，并与客户明细表进行比对，确认客户确系客户明细表中的客户与收货地址。
Select a.iperiod,a.csign,a.ino_id, a.mc,b.cvouchtype,
	b.csbvcode,b.ccuscode,c.ccusname,b.cVerifier
From gl_accvouch a left join salebillvouch b
	on a.coutbillsign=b.cvouchtype and a.coutid=b.csbvcode
		left join Customer c
	on b.ccuscode=c.ccuscode
		left join dispatchlist d
	on b.sbvid=d.sbvid
		or(
			b.cdlcode=d.cdlcode 
				and d.sbvid=0
		)
Where a.ccode='6001' 
	and a.mc<>0
	and(
		b.sbvid is null 
			or c.ccuscode is null
		)

--8.完整性的实质性测试

--(1)会计人员关注所有开具了的发票是否都如实记账，且入账金额与发票金额相同，检索出未入账的发票金额，显示结果包括发票类型、发票号、发票金额和入账金额。
--drop view fp
Create View fp
as
Select a.cvouchtype,a.csbvcode,
	sum(inatmoney) je, sum(iquantity) sl, sum(idiscount) zk
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid
Group by a.cvouchtype,a.csbvcode

Select b.cVouchType, b.cSBVCode, b.je, a.mc
From gl_accvouch a right join fp b
	on a.coutbillsign=b.cvouchtype
		and a.coutid=b.csbvcode
		and a.ccode='6001' and a.mc<>0
Where isnull(a.mc,0)<>b.je

--9.准确性、计价与分摊的实质性测试
--(1)会计人员将销售发票与入账金额进行比对，分析是否所有发票金额都准确地记入了主营业务收入明细账。
--drop view fp
Create View fp
as
Select a.cvouchtype, a.csbvcode, b.inatmoney
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid

Select a.iperiod, a.csign, a.ino_id, a.ccode,
	a.mc, b.cvouchtype, b.csbvcode,b.inatmoney
From gl_accvouch a
	join fp b
	on a.coutbillsign=b.cvouchtype
		and a.coutid=b.csbvcode
Where a.ccode='6001' and a.mc<>0

--根据结果分析，本例数据中由于未设置主营业务收入明细账，因此一笔主营业务收入记录对应一张发票中多行销售记录的金额之和。故需按每张发票金额进行汇总后再与主营业务收入明细账进行对比分析。
--drop view fp
Create View fp
as
Select a.cvouchtype, a.csbvcode, sum(b.inatmoney) fpje
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid
Group by a.cvouchtype,a.csbvcode

Select a.iperiod, a.csign,a.ino_id,
	b.cvouchtype,b.csbvcode,a.mc,b.fpje
From gl_accvouch a join fp b
	on a.coutbillsign=b.cvouchtype
		and a.coutid=b.csbvcode
Where a.ccode='6001' and a.mc<>b.fpje

--由于此例重点在于理解计价准确性，所以对销售发票金额与主营业务收入金额的对比分析未考虑完整性与真实性关注的不一致情况。如果全部考虑发票与入账的一致性问题，用全连接可以实现对上述三个认定的分析，仅需通过设置显示条件筛选不同的分析目标进行分析。
--drop view fp
Create View fp
as
Select a.cvouchtype,a.csbvcode, sum(b.inatmoney) fpje
From salebillvouch a join salebillvouchs b
	on a.sbvid=b.sbvid
Group by a.cvouchtype,a.csbvcode

Select a.iperiod, a.csign, a.ino_id,b.cvouchtype
	,b.csbvcode, a.mc,b.fpje
From gl_accvouch a full join fp b
	on a.coutbillsign=b.cvouchtype
		and a.coutid=b.csbvcode
Where a.ccode='6001' and isnull(a.mc,0)<>isnull(b.fpje,0)

--10.截止测试

--(1)会计人员比对记账日期、发票日期和发货日期应该归属的会计期间，将不属于同一期间的记录显示出来，结果包括发票类别、发票号成记账日期、发票日期、发货日期；记账发票月份差、发票发货月份差、记账发货月份差。
Select distinct a.coutbillsign, a.coutid, a.dbill_date,b.ddate,c.dDate,
	datediff(month,a.dbill_date,b.ddate) 记账发票跨月,
	datediff(month,b.ddate,c.ddate) 发票发货跨月,
	datediff(month, a.dbill_date, c.ddate) 记账发货跨月
From gl_accvouch a join salebillvouch b
	on a.coutbillsign=b.cvouchtype 
		and a.coutid=b.csbvcode join dispatchlist c
	on b.sbvid=c.sbvid 
		or (
			b.cdlcode=c.cdlcode
				and c.sbvid=0
		)
Where a.ccode='6001' and a.mc<>0
Group by a.coutbillsign,a.coutid,a.dbill_date,b.ddate,c.dDate

--思考题

--1.检查截止审计日尚未登记入账的销售交易，显示单据类别、发票号、距离审计日的月份差，按月份差降序排列。

--2.检查是否所有的销售单都开具了发票。

--3.检查发运凭证连续编号的完整性。

--4.比较核对销售交易登记入账的日期与发运凭证的日期，显示跨期记录。

--5.将应收账款明细账的记录按万元标准进行分层，统计每层业务记录笔数及金额、占总金额及记录数的比率，确定重点关注的记录。

--6.将主营业务收入明细账加总，追查其至总账的过账，确定金额的一致性。

--7.审计人员关注不同商品的最高售价与最低售价、最高售价与次高售价的比率，请按两种比率降序排列。显示结果包括品名、最高售价/最低售价和最高售价/次高售价。