数据库是 SQL SERVER 2000想写一个函数或一个存储过程假设时间段为: 2010-01-20 至 2010-10-19参数为1(即一个月)生成数据:检查项目 周期序号 开始 结束
'A1' 1 2010-01-20 2010-02-19
'A1' 2 2010-02-20 2010-03-19
'A1' 3 2010-03-20 2010-04-19
'A1' 4 2010-04-20 2010-05-19
'A1' 5 2010-05-20 2010-06-19
'A1' 6 2010-06-20 2010-07-19
'A1' 7 2010-07-20 2010-08-19
'A1' 8 2010-08-20 2010-09-19
'A1' 9 2010-09-20 2010-10-19参数为2(即两个月)检查项目 周期序号 开始 结束
'A1' 1 2010-01-20 2010-03-19
'A1' 2 2010-03-20 2010-05-19
'A1' 3 2010-05-20 2010-07-19
'A1' 4 2010-07-20 2010-09-19
'A1' 5 2010-09-20 2010-10-19 --不够两个月参数为3(即三个月)检查项目 周期序号 开始 结束
'A1' 1 2010-01-20 2010-04-19
'A1' 2 2010-04-20 2010-07-19
'A1' 3 2010-07-20 2010-10-19当然参数也可能为其它整数
go
create function f_test(@s datetime,@e datetime,@i int)
returns @t table(检查项目 varchar(10),周期序号 int identity,开始 datetime,结束 datetime)
as
begin
declare @d datetime
while dateadd(month,@i,@s)-1<=@e
begin
insert @t select 'A1',@s,dateadd(month,@i,@s)-1
set @s=dateadd(month,@i,@s)
end
if @s<@e
insert @t select 'A1',@s,@e
return
end
go
--
declare @s datetime,@e datetime,@i int
set @s='2010-01-20'
set @e='2010-10-19'
set @i=1
select 检查项目,周期序号,convert(varchar(10),开始,120) 开始, convert(varchar(10),结束,120)结束 from f_test(@s,@e,@i)
/*
检查项目 周期序号 开始 结束
---------- ----------- ---------- ----------
A1 1 2010-01-20 2010-02-19
A1 2 2010-02-20 2010-03-19
A1 3 2010-03-20 2010-04-19
A1 4 2010-04-20 2010-05-19
A1 5 2010-05-20 2010-06-19
A1 6 2010-06-20 2010-07-19
A1 7 2010-07-20 2010-08-19
A1 8 2010-08-20 2010-09-19
A1 9 2010-09-20 2010-10-19(9 行受影响)
*/set @i=2
select * from f_test(@s,@e,@i)
/*
检查项目 周期序号 开始 结束
---------- ----------- ----------------------- -----------------------
A1 1 2010-01-20 00:00:00.000 2010-03-19 00:00:00.000
A1 2 2010-03-20 00:00:00.000 2010-05-19 00:00:00.000
A1 3 2010-05-20 00:00:00.000 2010-07-19 00:00:00.000
A1 4 2010-07-20 00:00:00.000 2010-09-19 00:00:00.000
A1 5 2010-09-20 00:00:00.000 2010-10-19 00:00:00.000(5 行受影响)
*/
set @i=3
select * from f_test(@s,@e,@i)
/*
检查项目 周期序号 开始 结束
---------- ----------- ----------------------- -----------------------
A1 1 2010-01-20 00:00:00.000 2010-04-19 00:00:00.000
A1 2 2010-04-20 00:00:00.000 2010-07-19 00:00:00.000
A1 3 2010-07-20 00:00:00.000 2010-10-19 00:00:00.000(3 行受影响)*/
if object_id('P_getinfo')is not null drop PROCEDURE P_getinfo
go
create PROCEDURE P_getinfo(@s datetime,@e datetime,@i INT)
as
BEGIN
DECLARE @t table(检查项目 varchar(10),周期序号 int identity,开始 datetime,结束 datetime)
declare @d DATETIME
while dateadd(month,@i,@s)-1<=@e
begin
insert @t select 'A1',@s,dateadd(month,@i,@s)-1
set @s=dateadd(month,@i,@s)
END
if @s<@e
begin
insert @t select 'A1',@s,@e
END
SELECT 检查项目,周期序号,convert(varchar(10),开始,120) AS 开始,convert(varchar(10),结束,120)AS 结束 FROM @T
end
GO参考777的存储过程
A1 4 2010-04-20 00:00:00.000 2010-06-19 00:00:00.000
A1 6 2010-06-20 00:00:00.000 2010-08-19 00:00:00.000
A1 8 2010-08-20 00:00:00.000 2010-10-19 00:00:00.000