34,590
社区成员
发帖
与我相关
我的任务
分享
--try
update t
set OverLevel=isnull((select count(1) from CheckRec where Bill='jh' and BillNo=t.BillNo and Xh=t.xh),0),
CheckUser=(select AppUser from work_flow where Bill='jh' and
pLevel= SumLevel - (isnull(select count(1) from CheckRec where Bill='jh'
and BillNo=t.BillNo and Xh=t.xh ),0))
from #tmp_bill t
update t set
OverLevel=(select count(1) from #tmp_bill where BillNo=t.BillNo and Xh=t.xh),
CheckUser=(select max(AppUser) from work_flow where pLevel=t.SumLevel-t.OverLevel)
from #tmp_bill t
where bill='jh'
declare @BillNo varchar(20)
declare @xh int
declare @OverLevel int --已完成的审批次数
declare @SumLevel int --总共的审批次数
declare @CheckUser varchar(254)
declare my_cur cursor for
select BillNo,xh,SumLevel from #tmp_bill
open my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
while @@fetch_status=0
begin
select @OverLevel=count(1) from CheckRec where Bill='jh' and BillNo=@BillNo and Xh=@xh --计算已经完成的审批次数
select @CheckUser=AppUser from work_flow where Bill='jh' and pLevel=@SumLevel-@OverLevel --当前级别的审核人
update #tmp_bill set OverLevel=@OverLevel, CheckUser=@CheckUser WHERE CURRENT OF my_cur
fetch next from my_cur into @BillNo, @Xh, @SumLevel
end
close my_cur
deallocate my_cur