SQL Server:根据起始日期和终止日期以及缴费方式计算每月的缴费记录

做项目时经常碰到的业务：根据合同指定的起租日期和终止日期以及缴费方式，计算出两个日期之间的缴费记录，以便提醒缴费。

 

说明：

1、例子中的两个日期、缴费方式从合同中获取。

2、缴费方式主要有：月份、季付、半年付、年付，且以汉字的形式保存在数据库。

 

思路：根据终止日期和起租日期，计算出两个日期之间相差多少个月份，再根据缴费方式进行月份叠加，即

月付*1，季付*3，半年付*6，年付*12，如此计算出每个月缴费的日期以及下个月缴费的日期，再计算这两个日期之间的天数，最后乘以每天的房租费用，就可以得到每个月应该缴纳的总房租。

SQL如下：

CREATE PROCEDURE [dbo].[calcuteContractPay] 
	@id int --合同序号
AS
BEGIN
	
	SET NOCOUNT ON;

	--1、校验合同序号是否存在
	if @id is null
	begin
		raiserror('必须告知合同序号',16,1);
		return ;
	end   

	--2、获取缴费方式
	declare @field12 varchar(20);--缴费方式
	declare @field39 datetime;--起租日期
	declare @field40 datetime;--截止日期
	declare @field66 numeric(18,2);--每天总租金
	select @field12 = field12,@field39=field39,@field40=field40,@field66=isnull(field66,0) from table3 where field1=@id;
	if @field12 is null or len(@field12)<=0
	begin
		raiserror('必须告知缴费方式',16,1);
		return ;
	end
	
	
	--3、校验日期是否合法
	declare @day int;--两者之间的天数
	set @day = datediff(day,@field39,@field40);
	if(@day<=0)
	begin
		raiserror('终止日期必须大于起租日期',16,1);
		return ;
	end	
	
	--4、根据缴费方式判断总共要输入多少条缴费记录
	set @field12 = rtrim(ltrim(@field12));--去除空格
	declare @monthes int;
	declare @totalRent numeric(18,2);--总租金
	declare @type int;--缴费类型
	if(@field12='月付') set @type=1;
	else if(@field12='季付') set @type=2;
	else if(@field12='半年付') set @type=3;
	else if(@field12='年付') set @type=4;
	
	--4、1 计算开始日期与终止之间有多少个月份		
	set @monthes = datediff(month,@field39,@field40);--月份
	
	if @monthes=0 --说明是在当月/当季/半年之内/当年起租、终止，那么只需要直接计算两者之间的天数
	begin
		set @totalRent = @day*@field66;--天数*每天的总价
		begin
			--添加缴费记录
			insert into table6  
			select field1,field2,field4,field5,field12,field39,@totalRent,field42,field64,field11,0,NULL,0,@totalRent,@totalRent,0,							field13,@day,field40 
			from table3 where field1=@id;
		end
	end
	else --说明至少有一个月
	begin
		declare @startTime datetime;--开始日期
		declare @endTime datetime;--结束日期
		declare @num int;
		set @num=0;
		while(@num<=@monthes)	
		begin
			--print @num;
			set @startTime = dateadd(month,@num,@field39);--计算每个月应缴费日期
			if(@type=1) set @num=@num+1;--月付
			else if(@type=2) set @num=@num+3;--季付
			else if(@type=3) set @num=@num+6;--半年付
			else if(@type=4) set @num=@num+12;--年付

			set @endTime = dateadd(month,@num,@field39);--下个月的缴费日期
			--终止日期减去下一个月的日期，如果终止日期比下一个月的日期大，说明还需要再缴一个月，那么使用下一个月的日期，否则使用终止日期
			set @day = datediff(day,@endTime,@field40);
			if(@day<0) set @endTime=@field40;
			--计算开始日期和结束日期之间的天数
			set @day = datediff(day,@startTime,@endTime);
			set @totalRent = @day*@field66;--天数*每天的总价
			if(@totalRent>0)
			begin
				insert into table6  
				select field1,field2,field4,field5,field12,@startTime,@totalRent,field42,field64,field11,0,NULL,0,@totalRent,@totalRent,0,							field13,@day,@endTime 
				from table3 where field1=@id;
			end
		end		
	end

END
GO