|
declare opener Cursor for select CRM_WL_MaterielBatchId,CRM_WL_MaterielListId,Psort,Date,Name from #temp where Date>=@BeginDate order by CRM_WL_MaterielListId,Date,Name open opener fetch next from opener into @CRM_WL_MaterielBatchId,@CRM_WL_MaterielListId,@Psort,@Date,@Code while(@@fetch_status=0)--循環(huán)開始 begin set @StartNumber=(select isnull(sum(StartNumber),0) from #b where CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and CRM_WL_MaterielListId=@CRM_WL_MaterielListId) if(@Psort='借(收入)方') begin set @In=(select isnull(sum(Number),0) from #temp where Psort='借(收入)方' and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name <=@Code and CRM_WL_MaterielListId=@CRM_WL_MaterielListId ) set @Out=(select isnull(sum(Number),0) from #temp where Psort='貸(支出)方' and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name in(select Name from #temp1) and CRM_WL_MaterielListId=@CRM_WL_MaterielListId ) end else begin set @In=(select isnull(sum(Number),0) from #temp where Psort='借(收入)方' and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name in(select Name from #temp1) and CRM_WL_MaterielListId=@CRM_WL_MaterielListId ) set @Out=(select isnull(sum(Number),0) from #temp where Psort='貸(支出)方' and Date<=@Date and CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Name <=@Code and CRM_WL_MaterielListId=@CRM_WL_MaterielListId ) end insert into #temp1 select @Date,@Code,Remark, @StartNumber+@In-@Out,CostPrice,(@StartNumber+@In-@Out)*CostPrice ,GroupName,@CRM_WL_MaterielBatchId, @CRM_WL_MaterielListId,'結存' from #temp where CRM_WL_MaterielBatchId=@CRM_WL_MaterielBatchId and Psort=@Psort and Date=@Date and CRM_WL_MaterielListId=@CRM_WL_MaterielListId and Name =@Code fetch next from opener into @CRM_WL_MaterielBatchId,@CRM_WL_MaterielListId,@Psort,@Date,@Code end --循環(huán)結束 close opener--關閉游標 deallocate opener
|
|
|
來自： Hermanli > 《SQL SERVER》
