CREATEPROCEDUREsp_ArrearageRate@DayCountint,@EndDatevarchar(10),@Typeint,@OutputTypeint,@Em
CREATE PROCEDURE sp_ArrearageRate @DayCount int ,@EndDate varchar(10),@Type int,@OutputType int ,@EmployeeID varchar(10),@GuestNo varchar(10),@cb varchar(4),@SelectDate varchar(10),@SubPrice numeric(10,4) AS
set @DayCount = @DayCount - 1
if @Type = 1
begin
if exists(select * from dbo.sysobjects where id = object_id(N'[SaveMoney]') and objectproperty(id,N'IsUserTable') = 1)
drop table SaveMoney
declare @i int
set @i = 0
while @i <= @DayCount
begin
select
out_no,
b.outcb as cb,
Round(sum(s_amt),4) as inamt
into #t1
from accrecv as a,accrecvde as b
where
a.sn=b.sn and
a.falg =1 and
a.r_date <= convert(datetime,@EndDate)
group by out_no,b.outcb
select
a.out_no,
a.cb,
a.out_date,
a.out_emp,
a.out_dept,
round(sum(b.out_qty*b.out_price),4) as amt
into #t2
from mtlout as a ,mtloutde as b
where
a.outid=b.outid and
a.out_type='F08' and
a.out_flag =1 and
a.out_date <= convert(datetime,@EndDate)
group by a.out_no,a.cb,a.out_date,a.out_emp,a.out_dept
select
out_no,
b.outcb as cb,
edate ,
Round(sum(s_amt),4) as inamt
into #t41
from accrecv as a,accrecvde as b
where
a.sn=b.sn and
a.falg =1 and
a.r_date <= convert(datetime,@EndDate) and
a.edate > convert(datetime,@EndDate)
group by out_no,b.outcb,edate
select
out_no,
cb,
sum(inamt) as inamt ,
(select top 1 edate from #t41 as a where a.out_no=b.out_no order by edate desc ) as edate
into #t4
from #t41 as b
group by out_no,cb
select
a.*,
isnull(b.inamt,0) as inamt ,
round(isnull(a.amt,0)- isnull(b.inamt,0) ,2) as notamt
into #t3
from #t2 as a,#t1 as b
where a.out_no *=b.out_no and a.cb*=b.cb
select
a.*,
isnull(b.inamt,0) as qpinamt ,
round(notamt+isnull(b.inamt,0) ,2) as qpnotamt,
isnull(b.edate,null ) as edate
into #t5
from #t3 as a, #t4 as b
where a.out_no*=b.out_no and a.cb*=b.cb
delete from #t5
where notamt =0 and qpnotamt=0
if @i = 0
begin
select
@EndDate as ArrearageDate,
#t5.*
into Arrearage
from #t5
end
else
begin
insert Arrearage
select
@EndDate,
#t5.*
from #t5
end drop table #t1
drop table #t2
drop table #t3
drop table #t41
drop table #t4
drop table #t5
select
* ,
(case when charindex('現金',out_type)<>0 then amt else 0 end) as xjamt,
(case when charindex('現金',out_type)=0 then amt else 0 end) as pjamt
into #t81
from accrecv
where
charindex('預收',out_lb)<>0 and
edate <= convert(datetime,@EndDate)
select
* ,
(case when charindex('退票',o_remark)=0 and charindex('現金',out_type)<>0 then -(amt) else 0 end) as xjamt ,
(case when charindex('退票',o_remark)=0 and charindex('現金',out_type)=0 then -(amt) else 0 end) as pjamt ,
(case when charindex('退票',o_remark)<>0 then (amt) else 0 end) as tpamt
into #t82
from accrecv
where
use_jy=1 AND o_remark<>'退票' and
edate <= convert(datetime,@EndDate)
insert #t82
select
* ,
(case when charindex('退票',o_remark)=0 and charindex('現金',out_type)<>0 then (amt) else 0 end) as xjamt ,
(case when charindex('退票',o_remark)=0 and charindex('現金',out_type)=0 then (amt) else 0 end) as pjamt ,
(case when charindex('退票',o_remark)<>0 then (amt) else 0 end) as tpamt
from accrecv
where
use_jy=0 and
( charindex('3.預收款',out_lb)<>0 and o_remark='退票') and
edate <= convert(datetime,@EndDate)
update #t81 set r_remark =(select shotname from mtlguest as a where a.guestno=#t81.guest_no)
update #t82 set r_remark =(select shotname from mtlguest as a where a.guestno=#t82.guest_no)
select
guest_no,
r_remark,
cb,
edate,
sum( (amt)) as ystotamt,
sum(pjamt) as yspjamt ,
sum(xjamt) as ysxjamt
into #t83
from #t81
group by guest_no,r_remark,cb,edate
select
guest_no,
r_remark,
cb,
edate,
sum(case when charindex('退票',o_remark)<>0 then 0 else abs(amt) end ) as ystotamt,
sum(pjamt) as yspjamt ,
sum(xjamt) as ysxjamt ,
sum(tpamt) as tpamt
into #t84
from #t82
group by guest_no,r_remark,cb,edate
select guest_no,r_remark,cb,edate into #t85 from #t83
union
select guest_no,r_remark,cb,edate from #t84
SELECT
* ,
isnull((SELECT ystotamt FROM #t83 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as ystotamt,
isnull((SELECT yspjamt FROM #t83 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as yspjamt,
isnull((SELECT ysxjamt FROM #t83 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as ysxjamt,
isnull((SELECT ystotamt FROM #t84 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as o_totamt,
isnull((SELECT yspjamt FROM #t84 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as opj,
isnull((SELECT ysxjamt FROM #t84 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as oxj,
isnull((SELECT tpamt FROM #t84 AS B where b.guest_no=a.guest_no and b.cb = a.cb and b.edate = a.edate),0) as otp into #t86
FROM #t85 AS A
if @i = 0
begin
select
@EndDate as EndDate,
*,
ystotamt-otp-o_totamt as ye ,
isnull((
select top 1
d.emp_no
from (select guest_no,emp_no,bdate,isnull(edate,convert(datetime,'2078-12-31')) as edate,cb from mtlguestye) as d
where
d.guest_no = #t86.guest_no and
d.cb = #t86.cb and
convert(varchar(10),d.bdate,21) <= @EndDate and
convert(varchar(10), d.edate,21) >= @EndDate and
convert(varchar(10),d.bdate,21) <= #t86.edate and
convert(varchar(10),d.edate,21) >= #t86.edate
order by d.bdate desc
),'') as SaveMoneyEmployee
into SaveMoney
from #t86
where ystotamt-otp-o_totamt <>0
end
else
begin
insert SaveMoney ----------怎么设这个条件.主要是循环的那个时间..
select
@EndDate,
*,
ystotamt-otp-o_totamt as ye ,
isnull((
select top 1
d.emp_no
from (select guest_no,emp_no,bdate,isnull(edate,convert(datetime,'2078-12-31')) as edate,cb from mtlguestye) as d
where
d.guest_no = #t86.guest_no and
d.cb = #t86.cb and
convert(varchar(10),d.bdate,21) <= @EndDate and
convert(varchar(10), d.edate,21) >= @EndDate and
convert(varchar(10),d.bdate,21) <= #t86.edate and
convert(varchar(10),d.edate,21) >= #t86.edate
order by d.bdate desc
),'') as SaveMoneyEmployee
from #t86
where ystotamt-otp-o_totamt <>0
end
update SaveMoney set otp=-otp where EndDate = @EndDate
drop table #t81
drop table #t82
drop table #t83
drop table #t84
drop table #t85
drop table #t86
set @i = @i + 1
set @EndDate = convert(varchar(10),convert(datetime,@EndDate) - 1,21)
end
end
每次计算都会把以前的数据删掉重新算,好长时间.我想取最近的一个月的数值开始算.怎么在insert上设条件/..高手帮忙啊
34 个解决方案
更具功能需求重写吧,这种代码没有修改的价值。
粗略看了一下,循环里面开8个临时表,每循环一次全部drop,快极都有限,更要命的是,这些临时表每次去数据都只和一个变量@Enddate相关,而@Enddate每次循环-1。
虽然我不知道具体的需求,但实现这样一个功能,超过10条语句算是失败。
我怎么认真看,倒是你需要看一下——如果没有开发文档的话,有开发文档的话就不要看它了,直接重写。
局外人很难帮到你,没有生产环境,写出来的东西不一定正确。
一点看法:
将循环里面的临时表提出来——这是速度的瓶颈,根据@DayCount计算一个时间范围,比如@MaxEndDate,一次性取完数据,其实数据应该就出来了,不需要循环,不外乎group by或累积。
不清楚业务逻辑,是很难插手滴。。。
建议楼主先把业务逻辑搞清楚,再决定是重写还是修改
想问下这里的高手,为何建了#table又把随即把它们删掉呢?这样不就是跟建了普通的table而不是临时表,再把它们删掉一样吗?