资费标准过程.sql

水费管理系统

set nocount on
go

/*
	生成指定资费标准所有时间段的资费单价
	输入参数：
		@code    资费标准代码
	返回值: 
			0
	调用方法：
			gen_fee_time_span QY
	
*/
if exists(select * from sysobjects where type='P' and name = 'gen_fee_time_span')
	drop proc gen_fee_time_span
go

create proc gen_fee_time_span
	@code varchar(20)
as

-- 准备进行生成工作
truncate table fee_std_time
delete fee_price where fee_std_code = @code

-- 插入临时数据
insert into fee_std_time select fee_std_code,begin_time, unit_price, w_unit_price, price_unit
	from fee_std_price v 
	where v.fee_std_code=@code and begin_time < getdate() order by begin_time 

if @@rowcount = 0
	return 0

insert into fee_std_time(fee_std_code,begin_time,unit_price, w_unit_price,price_unit) values(@code,getdate() + 1,0,0,1)

declare mytime cursor  for select  fee_std_code,begin_time, unit_price, w_unit_price, price_unit from fee_std_time order by begin_time
declare @bt1 datetime, @et1 datetime, @std_code1 varchar(10), @uint_price1 money, @w_uint_price1 money, @cost_uint1 tinyint
declare @bt2 datetime, @et2 datetime, @std_code2 varchar(10), @uint_price2 money, @w_uint_price2 money, @cost_uint2 tinyint
open mytime
fetch mytime into @std_code1,@bt1, @uint_price1, @w_uint_price1, @cost_uint1 

while @@FETCH_STATUS = 0 
begin
	fetch mytime into @std_code2,@bt2, @uint_price2, @w_uint_price2, @cost_uint2
	if @@fetch_status <> 0 
	begin
		break
	end
	insert into fee_price(fee_std_code, bt, et,price,w_price) values(@code,@bt1,@bt2,@uint_price1,@w_uint_price1)
	select @bt1 = @bt2, @uint_price1 = @uint_price2, @w_uint_price1 = @w_uint_price2, @cost_uint1 = @cost_uint2
end
close mytime
deallocate mytime
return 0
go

set nocount off
go