首页 > 学术百科

51CTO下载-10[1].0重算存货总账脚本

select * into ia_summarybak  from ia_summary
delete Ia_summary
--按月创建总账
declare @iastyle as varchar(60)
declare @iyear as int
select  @iyear =RIGHT(db_name(),4)
select @iastyle=cvalue from accinformation where csysid='ia' and cname='cValueStyle'
if @iastyle='按仓库核算'
begin
Insert Into Ia_summary(
cWhCode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
cWhCode,
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6))) As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
dif
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode
where cvoutype <> '33'
group by
cWhCode,
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End
end
if @iastyle='按部门核算'
begin
Insert Into Ia_summa
ry(
cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
cdepcode,
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6))) As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode行政处罚法第27条
where cvoutype <> '33'
group by
cdepcode,
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End
end
if @iastyle='按存货核算'
begin
Insert Into Ia_summary(
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
inventory.cInvCode,imonth,
sum(cast(iainquantity as decimal(38,6))) As innum,sum(cast(iaoutquantity as decimal(38,6)))
As outnum,IsNull(sum(cast(iainquantity as decimal(38,6))),0) -IsNull(sum(cast(iaoutquantity as decimal(38,6))),0) As inum,
sum(iainPrice) As InPrice,sum(iaoutprice) As OutPrice,IsNull(sum(iainPrice),0) -IsNull(sum(iaoutprice),0) As iPrice,
sum(Case When brdflag = 1 Then Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) Else 0 End) As InDif,
sum(Case When brdflag = 0 Then Isnull(icreditdifcost,0) - IsNull(idebitdifcost,0) Else 0 End) As OutDif,
sum(Isnull(idebitdifcost,0) - IsNull(icreditdifcost,0) ) As iDif,Case When cBustype like '直运%' Then 1 Else 0 End As idir,0,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End As cFree1, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End As cFree2,
Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End As cFree3, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End As cFree4,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End As cFree5, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End As cFree6,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End As cFree7, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End As cFree8,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End As cFree9, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End As cFree10,
max(iTrans)
From ia_subsidiary
Left Join inventory on ia_subsidiary.cinvcode= inventory.cinvcode
where cvoutype <> '33'
group by
inventory.cInvCode,imonth,Case When cBustype like '直运%' Then 1 Else 0 End ,
Case When Inventory.bCheckFree1 = 1 Then cFree1 Else Null End, Case When Inventory.bCheckFree2 = 1 Then cFree2 Else Null End,
大地欢歌2009Case When Inventory.bCheckFree3 = 1 Then cFree3 Else Null End, Case When Inventory.bCheckFree4 = 1 Then cFree4 Else Null End,
Case When Inventory.bCheckFree5 = 1 Then cFree5 Else Null End, Case When Inventory.bCheckFree6 = 1 Then cFree6 Else Null End,
Case When Inventory.bCheckFree7 = 1 Then cFree7 Else Null End, Case When Inventory.bCheckFree8 = 1 Then cFree8 Else Null End,
Case When Inventory.bCheckFree9 = 1 Then cFree9 Else Null End, Case When Inventory.bCheckFree10 = 1 Then cFree10 Else Null End
end
declare @i int
declare @MaxMonth int
declare @MinMonth int
declare @StartMonth int
set @StartMonth = (select IsNull(Min(imonth),0) from Ia_Subsidiary Where imonth <>0)
2012新课标理综
set @MinMonth = (select Min(imonth) from Ia_summary Where imonth <>0)
set @MaxMonth = (select Max(iperiod) from gl_mend where bflag_ia=1 )
if not @MinMonth is null
begin
--处理期初数据
--更新已存在数据
Update Ia_summary Set iNum = IsNull(cast(Ia_summary.iNum as decimal(38,6)),0) + IsNull(cast(Pre.iNum as decimal(38,6)),0), iMoney = IsNull(Ia_summary.iMoney, 0) + IsNull(Pre.iMoney,0),
iDif = IsNull(Ia_summary.iDif,0) + IsNull(Pre.iDif,0)
From Ia_summary
Left Join Ia_summary Pre On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'')
And
isnull(ia_summary.cdepcode,'')=isnull(pre.cdepcode,'') and
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '') 
and Pre.imonth = 0
where Ia_summary.imonth = @MinMonth
--插入下月不存在的数据
Insert Into Ia_summary(
cWhCode,cdepcode,
浙江工业大学成人教育学院cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,pre.cdepcode,
Pre.cInvCode,@MinMonth,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3, Pre.cFree4,
Pre.cFree5, Pre.cFree6,
Pre.cFree7, Pre.cFree8,
Pre.cFree9, Pre.cFree10,
Pre.iTrans
From Ia_summary Pre
Left Join Ia_summary On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'')
and Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '') 
and Ia_summary.imonth = @MinMonth
where Pre.imonth = 0 and Ia_summary.autoid is null
While @MinMonth <= @MaxMonth
begin
Update Ia_summary Set iNum = IsNull(cast(Ia_summary.iNum as decimal(38,6)), 0) + IsNull(cast(Pre.iNum as decimal(38,6)), 0),iMoney = IsNull(Ia_summary.iMoney, 0) + IsNull(Pre.iMoney, 0),
iDif = IsNull(Ia_summary.iDif, 0) + IsNull(Pre.iDif, 0)
From Ia_summary
Left Join Ia_summary Pre On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'') and
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '') 
and Pre.imonth = @MinMonth
where Ia_summary.imonth = @MinMonth + 1
----插入下月不存在的数据
Insert Into Ia_summary(
cWhCode,cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,pre.cdepcode,
Pre.cInvCode,@MinMonth+1,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
中餐宴会摆台主题设计
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3, Pre.cFree4,
Pre.cFree5, Pre.cFree6,
Pre.cFree7, Pre.cFree8,
Pre.cFree9, Pre.cFree10,
Pre.iTrans
From Ia_summary Pre
Left Join Ia_summary On
isnull(Ia_summary.cWhCode,'') = isnull(Pre.cWhCode,'') And isnull(Ia_summary.cdepcode,'') = isnull(Pre.cdepcode,'') And
Ia_summary.cInvCode = Pre.cInvCode And Ia_summary.iDirect = Pre.iDirect
And IsNull(Ia_summary.cFree1, '') = IsNull(Pre.cFree1, '') And IsNull(Ia_summary.cFree2, '') = IsNull(Pre.cFree2, '')
And IsNull(Ia_summary.cFree3, '') = IsNull(Pre.cFree3, '') And IsNull(Ia_summary.cFree4, '') = IsNull(Pre.cFree4, '')
And IsNull(Ia_summary.cFree5, '') = IsNull(Pre.cFree5, '') And IsNull(Ia_summary.cFree6, '') = IsNull(Pre.cFree6, '')
And IsNull(Ia_summary.cFree7, '') = IsNull(Pre.cFree7, '') And IsNull(Ia_summary.cFree8, '') = IsNull(Pre.cFree8, '')
And IsNull(Ia_summary.cFree9, '') = IsNull(Pre.cFree9, '') And IsNull(Ia_summary.cFree10, '') = IsNull(Pre.cFree10, '') 
and Ia_summary.imonth = @MinMonth + 1
where Pre.imonth = @MinMonth and Ia_summary.autoid is null
set @MinMonth  = @MinMonth + 1
end
end
else
begin
Insert Into Ia_summary(
cWhCode,cdepcode,
cInvCode,iMonth,iInum,iOnum,iNum,iImoney,iOmoney,iMoney,iIDif,iODif,iDif,iDirect,bManual,
cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, iTrans)
Select
Pre.cWhCode,
pre.cdepcode,
Pre.cInvCode,@StartMonth,
null As innum,null As outnum,cast(Pre.inum as decimal(38,6)) As inum,
null As InPrice,null As OutPrice,Pre.iMoney As iMoney,
null As InDif,null As OutDif,Pre.iDif As iDif,
Pre.iDirect As iDirect,0,
Pre.cFree1, Pre.cFree2,
Pre.cFree3 , Pre.cFr

本文发布于:2024-09-20 16:48:13，感谢您对本站的认可！

本文链接：https://www.17tex.com/xueshu/721011.html

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。

上一篇：MACD买卖技巧有哪些
下一篇：聚宽macd底背离_MACD指标图解!如何解决滞后性,规避盲目买进卖出!_百 ...
标签：核算   存在   数据   设计   总账
留言与评论（共有 0 条评论）
   
验证码：
推荐文章
排行榜
热门标签
Copyright ©2019-2024 Comsenz Inc.Powered by © 易纺专利技术学习网 豫ICP备2022007602号 豫公网安备41160202000603 站长QQ:729038198 关于我们 投诉建议