求两个带参数的存储过程,分别对下表插入一行和删除一行交费表:jf —— 每行是一个整月的起日和止日, 该表中所有类型为'收视'的行的 止日=DATEADD(month,1, 起日)
证号 端口 时间标记 起日 止日 类型
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.5.10 收视
1 1 2009-05-10 09:18:18.570 2009.5.11 2010.6.10 收视
1 1 2009-06-10 11:08:56.007 2009.6.11 2010.7.10 收视所求过程细节如下:过程1: 向该表插入一行数据:
格式:create proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))过程2: 从该表删除一行数据:
格式:create proc ShanChu(@证号 nvarchar(6),@端口 int,@起日 datetime)对于原始表jf执行:exec ChaRu 1,1,getdate(),'2009.4.20','2009.6.3','撤线'
则jf变成下面的样子:
证号 端口 时间标记 起日 止日 状态
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视 --在@起日之前,保持原样
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.4.19 收视
1 1 2009-06-12 12:32:17.055 2009.4.20 2009.6.3 撤线 --插入的新行,数据来自ChaRu的参数
1 1 2009-04-10 13:21:28.507 2009.6.4 2009.6.24 收视
1 1 2009-05-10 09:18:18.570 2009.6.25 2009.7.24 收视 --对原jf的2009.5.11-2009.6.10,31天变30天
1 1 2009-06-10 11:08:56.007 2009.7.25 2009.8.24 收视 --对原jf的2009.6.11-2009.7.10,30天变31天 上面表中两行红的相加对应原jf中的2009.4.11-2010.5.10,合计天数还是30,请注意它们的证号、端口、时间标记都相同,标志着它们原来是同一行。ChaRu过程的规则如下:
1.jf表中被拆分的交费记录分裂成两行,这两行的合计天数应和被拆分前保持一致。
2.jf表中,在被拆分行止日之后的各行,日期被向后顺延,但宁可改变天数,也要保持“止日=DATEADD(month,1, 起日)
”。 对于上面插入后的jf表执行:exec ShanChu 1,1,'2009.4.20'
则回复原状,即本帖顶部的样子。
证号 端口 时间标记 起日 止日 类型
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.5.10 收视
1 1 2009-05-10 09:18:18.570 2009.5.11 2010.6.10 收视
1 1 2009-06-10 11:08:56.007 2009.6.11 2010.7.10 收视所求过程细节如下:过程1: 向该表插入一行数据:
格式:create proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))过程2: 从该表删除一行数据:
格式:create proc ShanChu(@证号 nvarchar(6),@端口 int,@起日 datetime)对于原始表jf执行:exec ChaRu 1,1,getdate(),'2009.4.20','2009.6.3','撤线'
则jf变成下面的样子:
证号 端口 时间标记 起日 止日 状态
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视 --在@起日之前,保持原样
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.4.19 收视
1 1 2009-06-12 12:32:17.055 2009.4.20 2009.6.3 撤线 --插入的新行,数据来自ChaRu的参数
1 1 2009-04-10 13:21:28.507 2009.6.4 2009.6.24 收视
1 1 2009-05-10 09:18:18.570 2009.6.25 2009.7.24 收视 --对原jf的2009.5.11-2009.6.10,31天变30天
1 1 2009-06-10 11:08:56.007 2009.7.25 2009.8.24 收视 --对原jf的2009.6.11-2009.7.10,30天变31天 上面表中两行红的相加对应原jf中的2009.4.11-2010.5.10,合计天数还是30,请注意它们的证号、端口、时间标记都相同,标志着它们原来是同一行。ChaRu过程的规则如下:
1.jf表中被拆分的交费记录分裂成两行,这两行的合计天数应和被拆分前保持一致。
2.jf表中,在被拆分行止日之后的各行,日期被向后顺延,但宁可改变天数,也要保持“止日=DATEADD(month,1, 起日)
”。 对于上面插入后的jf表执行:exec ShanChu 1,1,'2009.4.20'
则回复原状,即本帖顶部的样子。
1 1 2009-04-10 13:21:28.507 2009.6.4 2009.6.24 收视
这条数据没看懂。尤其是2009.6.24 怎么来的。楼主可以解释下吗?
求两个带参数的存储过程,分别对下表插入一行和删除一行交费表:jf —— 每行是一个整月的起日和止日, 该表中所有类型为'收视'的行的 止日=DATEADD(month,1, 起日)
证号 端口 时间标记 起日 止日 类型
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.5.10 收视
1 1 2009-05-10 09:18:18.570 2009.5.11 2010.6.10 收视
1 1 2009-06-10 11:08:56.007 2009.6.11 2010.7.10 收视所求过程细节如下:过程1: 向该表插入一行数据:
格式:create proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))过程2: 从该表删除一行数据:
格式:create proc ShanChu(@证号 nvarchar(6),@端口 int,@起日 datetime)对于原始表jf执行:exec ChaRu 1,1,getdate(),'2009.4.20','2009.6.3','撤线'
则jf变成下面的样子:
证号 端口 时间标记 起日 止日 状态
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视 --在@起日之前,保持原样
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.4.19 收视
1 1 2009-06-12 12:32:17.055 2009.4.20 2009.6.3 撤线 --插入的新行,数据来自ChaRu的参数
1 1 2009-04-10 13:21:28.507 2009.6.4 2009.6.24 收视
1 1 2009-05-10 09:18:18.570 2009.6.25 2009.7.24 收视 --对原jf的2009.5.11-2009.6.10,31天变30天
1 1 2009-06-10 11:08:56.007 2009.7.25 2009.8.24 收视 --对原jf的2009.6.11-2009.7.10,30天变31天 上面表中两行红的相加对应原jf中的2009.4.11-2010.5.10,合计天数还是30,请注意它们的证号、端口、时间标记都相同,标志着它们原来是同一行。ChaRu过程的规则如下:
1.jf表中被拆分的交费记录分裂成两行,这两行的合计天数应和被拆分前保持一致。
2.jf表中,在被拆分行止日之后且 类型='收视' 的各行,日期被向后顺延,但宁可改变天数,也要保持“止日=DATEADD(month,1, 起日)
”。 对于上面插入后的jf表执行:exec ShanChu 1,1,'2009.4.20'
则回复原状,即本帖顶部的样子。
回头再写另外一部分。--创建数据
IF OBJECT_ID('JF') IS NOT NULL
DROP TABLE JF CREATE TABLE JF(@RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2010.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2010.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2010.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2010.7.10','收视' --插入记录
CREATE proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,@i int,@newend smalldatetime
select @i = 1
--1.定位
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME
select @beginMark = datediff(day,begintime,@begintime) from jf where RID = @MarkRID
select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkID
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @endtime,dateadd(d,30-@beginMark,@endtime),@dtype
select @newend= dateadd(d,30-@beginMark,@endtime)
while @i < @count
begin
update jf set begintime =@newend+ 1 + (@i-1) * 30,endtime = @newend+ 1 + @i * 30
where @rid = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
--3.插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
END
IF OBJECT_ID('JF') IS NOT NULL
DROP TABLE JF CREATE TABLE JF(@RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2010.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2010.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2010.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2010.7.10','收视'
出错信息如下:
消息 102,级别 15,状态 1,第 4 行
'@RID' 附近有语法错误。第二部分:
CREATE proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,@i int,@newend smalldatetime
select @i = 1
--1.定位
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME
select @beginMark = datediff(day,begintime,@begintime) from jf where RID = @MarkRID
select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkID
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @endtime,dateadd(d,30-@beginMark,@endtime),@dtype
select @newend= dateadd(d,30-@beginMark,@endtime)
while @i < @count
begin
update jf set begintime =@newend+ 1 + (@i-1) * 30,endtime = @newend+ 1 + @i * 30
where @rid = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
--3.插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
END
出错信息如下:
消息 137,级别 15,状态 2,过程 ChaRu,第 12 行
必须声明标量变量 "@MarkID"。
消息 137,级别 15,状态 2,过程 ChaRu,第 18 行
必须声明标量变量 "@rid"。
DROP TABLE JF CREATE TABLE JF(RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视' --插入数据的存储过程
Create proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,@i int,@newend smalldatetime, @MarkDType varchar(10)
select @i = 1
--1.定位
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME
select @beginMark = datediff(day,begintime,@begintime), @MarkDType = DType from jf where RID = @MarkRID
select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkRID
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @endtime + 1,dateadd(d,30-@beginMark,@endtime),@MarkDType
select @newend= dateadd(d,30-@beginMark,@endtime)
while @i <= @count
begin
update jf set begintime =@newend+ 1 + (@i-1) * 30,endtime = @newend+ 1 + @i * 30
where rid = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
--3.插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
END --测试插入
exec ChaRu 1,1,'2009-05-01','2009.4.20','2009.6.3','撤线'
select * from jf order by begintime /*
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-04-19 00:00:00 收视
6 1 1 2009-05-01 00:00:00 2009-04-20 00:00:00 2009-06-03 00:00:00 撤线
5 1 1 2009-05-01 00:00:00 2009-06-04 00:00:00 2009-06-24 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-25 00:00:00 2009-07-25 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-07-25 00:00:00 2009-08-24 00:00:00 收视
*/
只发现一处不符要求,标红部分应该是24就好了,因为有一条规则: 状态为"收视"的行的 止日=DATEADD(month,1, 起日)还有,能否劳驾也做下第二个存储过程? 请见楼顶问题后部分.
1.jf表中被拆分的交费记录分裂成两行,这两行的合计天数应和被拆分前保持一致。
2.jf表中,在被拆分行止日之后的各行,日期被向后顺延,但宁可改变天数,也要保持“止日=DATEADD(month,1, 起日) 楼主这句话让人费解,尤其是第二条。跟你前面的描述矛盾呀。
ChaRu过程的规则如下:
1.jf表中被拆分的交费记录分裂成两行,这两行的合计天数应和被拆分前保持一致。
2.jf表中,在被拆分行止日之后且 类型='收视' 的各行,日期被向后顺延,但宁可改变天数,也要保持“止日=DATEADD(month,1, 起日)
”。
您说和"前面"的矛盾,不知道是不是说和第一条矛盾,如果是,第一条说的是被拆分的行,要保持拆成的两半的天数和等于被拆前的天数. 第二条说的是在被拆的行的日期后且类型是'收视'的行.不好意思,没说清.
这个是最终的语句,至于时间问题,楼主可以自己稍微改下就OK了。IF OBJECT_ID('JF') IS NOT NULL
DROP TABLE JF CREATE TABLE JF(RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视' --插入数据的存储过程
Alter proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,@i int,@newend smalldatetime, @MarkDType varchar(10)
select @i = 1
--1.定位(此处修改为了便于重复插入计算)
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME
select @beginMark = datediff(day,begintime,@begintime), @MarkDType = DType from jf where RID = @MarkRID
select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
-- 插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
--更新操作
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkRID
insert into JF(zh,dk,timeflag,begintime,endtime,dtype) select @zh,@dk,@timeflag, @endtime + 1,dateadd(d,30-@beginMark,@endtime),@MarkDType
select @newend= dateadd(d,30-@beginMark,@endtime)
while @i <= @count
begin
update jf set begintime = dateadd(d,1,@newend),
endtime = dateadd(month,@i-1,dateadd(d,1,@newend))
where rid = @Rid + @i
SELECT @NEWEND = ENDTIME FROM JF WHERE RID = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END
----测试插入
exec ChaRu 1,1,'2009-05-01','2009.4.20','2009.6.3','撤线'
select * from jf order by begintime /×测试结果
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-04-19 00:00:00 收视
5 1 1 2009-05-01 00:00:00 2009-04-20 00:00:00 2009-06-03 00:00:00 撤线
6 1 1 2009-05-01 00:00:00 2009-06-04 00:00:00 2009-06-24 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-25 00:00:00 2009-06-25 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-06-26 00:00:00 2009-07-26 00:00:00 收视
×/
--删除数据的存储过程
CREATE proc ShanChu(@ZH nvarchar(6),@DK int,@begintime smalldatetime)
AS
BEGIN
DECLARE @PreRID INT, @CurRID INT,
@NextRID INT,@MARKtime smalldatetime,@COUNT INT ,@I INT
SELECT @I = 1
--1.定位
SELECT @CurRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = convert(varchar,@begintime,112)
SELECT @PreRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = (select convert(varchar,MAX(begintime),112) FROM JF WHERE Begintime < @begintime)
SELECT @NextRID = Min(RID) FROM JF WHERE RID > @CurRID --2.删除&&更新
BEGIN TRAN
DELETE FROM JF WHERE RID = @CurRID or RID = @NextRID
UPDATE JF SET Endtime = dateadd(MONTH,1,BEGINTIME) WHERE RID = @PreRID
SELECT @MARKtime = endtime FROM JF WHERE RID = @PreRID
SELECT @COUNT = COUNT(*) FROM JF WHERE RID > @PreRID
WHILE @I <= @COUNT
BEGIN
UPDATE JF SET BEGINTIME =Dateadd(month,@I-1,DATEADD(Day,1,@MARKTIME)),
ENDTIME = dateadd(month,@I,dateadd(d,1,@MARKTIME))
WHERE RID = @I + @PreRID
Select @I = @I + 1
END
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END--删除操作执行
exec ShanChu 1,1,'2009.4.20'
select * from JF /*测试结果
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-05-11 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-05-12 00:00:00 2009-06-12 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-06-12 00:00:00 2009-07-12 00:00:00 收视
*/
DROP TABLE JF CREATE TABLE JF(RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.11','收视' --插入数据的存储过程
Create proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,
@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,
@i int,@newend smalldatetime, @MarkDType varchar(10),@replaceMark int
select @i = 1
--1.定位
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME select @beginMark = datediff(day,begintime,@begintime), @MarkDType = DType,
@replaceMark = datediff(d,begintime,endtime) from jf where RID = @MarkRID select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
-- 插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype)
select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
--更新操作
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkRID
--取得截断日期
SELECT @newend= dateadd(d,@replaceMark - @beginMark,@endtime)
--插入另一半
INSERT into JF(zh,dk,timeflag,begintime,endtime,dtype)
select @zh,@dk,@timeflag, @endtime + 1,@newend,@MarkDType
--更新
while @i <= @count
begin
update jf set begintime = dateadd(d,1,@newend),
endtime = dateadd(d,datediff(d,begintime,endtime),dateadd(d,1,@newend))
where rid = @Rid + @i
SELECT @NEWEND = ENDTIME FROM JF WHERE RID = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END
----测试插入
exec ChaRu 1,1,'2009-05-01','2009.4.20','2009.6.3','撤线'
select * from jf order by begintime /*
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-04-19 00:00:00 收视
5 1 1 2009-05-01 00:00:00 2009-04-20 00:00:00 2009-06-03 00:00:00 撤线
6 1 1 2009-05-01 00:00:00 2009-06-04 00:00:00 2009-06-23 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-24 00:00:00 2009-07-24 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-07-25 00:00:00 2009-08-24 00:00:00 收视
*/
--删除数据的存储过程
Alter proc ShanChu(@ZH nvarchar(6),@DK int,@begintime smalldatetime)
AS
BEGIN
DECLARE @PreRID INT, @CurRID INT,
@NextRID INT,@MARKtime smalldatetime,
@COUNT INT ,@I INT,@replaceMark int
SELECT @I = 1
--1.定位
SELECT @CurRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = convert(varchar,@begintime,112)
SELECT @PreRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = (select convert(varchar,MAX(begintime),112) FROM JF WHERE Begintime < @begintime)
SELECT @NextRID = Min(RID) FROM JF WHERE RID > @CurRID --2.删除&&更新
BEGIN TRAN
--取得插入之前的差值
Select @replaceMark = datediff(d,L.begintime,P.endtime) From
(Select begintime from jf where RID = @PreRID) L,
(Select endtime from jf where RID = @NEXTRID) P
--删除插入的数据
DELETE FROM JF WHERE RID = @CurRID or RID = @NextRID
--更新现有的数据
UPDATE JF SET Endtime = dateadd(d,@replaceMark,BEGINTIME) WHERE RID = @PreRID
--定义循环更新的初始时间
SELECT @MARKtime = endtime FROM JF WHERE RID = @PreRID
--取得总循环次数
SELECT @COUNT = COUNT(*) FROM JF WHERE RID > @PreRID
WHILE @I <= @COUNT
BEGIN
UPDATE JF SET BEGINTIME =Dateadd(day,1,@time),
ENDTIME = dateadd(day,datediff(d,begintime,endtime),dateadd(d,1,@MARKTIME))
WHERE RID = @I + @PreRID
SELECT @MARKTIME = endtime from jf where RID = @I + @PreRID
Select @I = @I + 1
END
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END
--删除操作执行
exec ShanChu 1,1,'2009.4.20'
select * from JF /*测试结果
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-06-23 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-24 00:00:00 2009-07-24 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-07-25 00:00:00 2009-08-24 00:00:00 收视
*/
很少有贴能这样。
IF OBJECT_ID('jf') IS NOT NULL
DROP TABLE jf
gocreate table jf(证号 int,端口 int, 时间标记 datetime,起日 datetime,止日 datetime,类型 varchar(10))
go
insert into jf
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视'
go
alter proc ChaRu
(
@证号 nvarchar(6),
@端口 int,
@时间标记 datetime,
@起日 datetime,
@止日 datetime,
@类型 nvarchar(6)
)
as
begin
if @类型 = '撤线' and exists(select 1 from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口)
begin
declare @撤分时间标记 datetime
select @撤分时间标记 = 时间标记 from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口 update jf set 起日 = jf.起日,
止日 = dateadd(day,-1,@起日)
from
(
select * from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口
)T
where jf.起日 = T.起日 and jf.证号 = T.证号 and jf.端口 = T.端口 insert into jf
select @证号,@端口,@时间标记,@起日,@止日,@类型 insert into jf
select 证号,端口,时间标记,起日= dateadd(day,1,@止日),
止日 = dateadd(day,datediff(day,起日,dateadd(month,1,起日)) - (datediff(day,起日,止日)+1),@止日),类型
from
(
select * from jf where 时间标记 = @撤分时间标记 and 证号 = @证号 and 端口 = @端口
)A declare @标记止日 datetime
select @标记止日 = max(止日) from jf where 时间标记 = @撤分时间标记 and 证号 = @证号 and 端口 = @端口 update a
set 起日 = dateadd(month,(select count(1) from jf where 证号 = @证号 and 端口 = @端口 and 时间标记 <= a.时间标记 and 类型 = '收视' and 时间标记 > @撤分时间标记)-1,dateadd(day,1,@标记止日)),
止日 = dateadd(month,(select count(1) from jf where 证号 = @证号 and 端口 = @端口 and 时间标记 <= a.时间标记 and 类型 = '收视' and 时间标记 > @撤分时间标记),@标记止日)
from jf a
where a.时间标记 > @撤分时间标记 and a.类型 = '收视' and a.证号 = @证号 and a.端口 = @端口 end
else
insert into jf
select @证号,@端口,@时间标记,@起日,@止日,@类型
end
godeclare @date datetime
set @date = getdate()
exec ChaRu '1',1,@date,'2009-4-20','2009-6-3','撤线'
goselect * from jf order by 起日
go/*
1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-04-10 00:00:00.000 收视
1 1 2009-04-10 13:21:28.507 2009-04-11 00:00:00.000 2009-04-19 00:00:00.000 收视
1 1 2009-05-26 11:16:31.593 2009-04-20 00:00:00.000 2009-06-03 00:00:00.000 撤线
1 1 2009-04-10 13:21:28.507 2009-06-04 00:00:00.000 2009-06-24 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-06-25 00:00:00.000 2009-07-24 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-07-25 00:00:00.000 2009-08-24 00:00:00.000 收视
*/alter proc ShanChu
(
@证号 nvarchar(6),
@端口 int,
@起日 datetime
)
as
begin
if exists(select 1 from jf where 起日 = @起日 and 证号 = @证号 and 端口 = @端口)
begin
delete from jf where 起日 = @起日 and 证号 = @证号 and 端口 = @端口
declare @撤分时间标记 datetime
select @撤分时间标记 = 时间标记 from jf where 证号 = @证号 and 端口 = @端口 group by 时间标记 having count(1)>1 delete a from jf a
where a.时间标记 = @撤分时间标记 and a.起日 = (select max(起日) from jf where a.证号 = 证号 and a.端口 = 端口 and 时间标记 = @撤分时间标记)
and a.证号 = @证号 and a.端口 = @端口 update jf set 起日 = cast(convert(varchar(10),dateadd(day,1,时间标记),120) as datetime),
止日 = cast(convert(varchar(10),dateadd(month,1,时间标记),120) as datetime)
where 证号 = @证号 and 端口 = @端口 end
end
go
exec ShanChu 1,1,'2009.4.20'
select * from jf
go
/*
1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-04-10 00:00:00.000 收视
1 1 2009-04-10 13:21:28.507 2009-04-11 00:00:00.000 2009-05-10 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-05-11 00:00:00.000 2009-06-10 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-06-11 00:00:00.000 2009-07-10 00:00:00.000 收视
*/
go
create table [jf]([证号] int,[端口] int,[时间标记] datetime,[起日] datetime,[止日] datetime,[状态] varchar(4))
insert [jf]
select 1,1,'2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' union all
select 1,1,'2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' union all
select 1,1,'2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' union all
select 1,1,'2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视'
go
--select * from [jf]--创建存储过程:
if object_id('ChaRu','p') is not null
drop proc ChaRu
go
create proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))
as
set nocount on
--所需数据插入临时表
select 时间标记,起日,止日,状态 into #
from jf
where 证号=@证号 and 端口=@端口
--获取关键的时间点
declare @最小起日 datetime
select @最小起日=min(起日) from jf where 止日>@起日
--插入被撤线时间截断的后一条
insert #
select 时间标记,@止日+1,@止日+30-datediff(d,@最小起日,@起日),状态
from #
where 起日=@最小起日
--更新被撤线时间截断的前一条
update #
set 止日=@起日-1
where 起日=@最小起日
--插入撤线记录
insert # values(@时间标记,@起日,@止日,'撤线')
--将撤线止日之后的日期顺延
update #
set 起日=dateadd(m,datediff(m,@最小起日,#.起日),@止日+31-datediff(d,@最小起日,@起日))
,止日=dateadd(m,datediff(m,@最小起日,#.起日)+1,@止日+30-datediff(d,@最小起日,@起日))
from jf t
where #.起日=t.起日 and t.证号=@证号 and t.端口=@端口 and t.起日>@最小起日
--删除已有记录
delete jf where 证号=@证号 and 端口=@端口
--插入新整合的记录
insert jf
select @证号,@端口,时间标记,起日,止日,状态 from # order by 起日
go--测试结果:
exec ChaRu 1,1,'2009-06-12 12:32:17.055','2009.4.20','2009.6.3','撤线'
select * from [jf]
/*
证号 端口 时间标记 起日 止日 状态
----------- ----------- ----------------------- ----------------------- ----------------------- ----
1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-04-10 00:00:00.000 收视
1 1 2009-04-10 13:21:28.507 2009-04-11 00:00:00.000 2009-04-19 00:00:00.000 收视
1 1 2009-06-12 12:32:17.057 2009-04-20 00:00:00.000 2009-06-03 00:00:00.000 撤线
1 1 2009-04-10 13:21:28.507 2009-06-04 00:00:00.000 2009-06-24 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-07-25 00:00:00.000 2009-08-24 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-08-25 00:00:00.000 2009-09-24 00:00:00.000 收视(6 行受影响)
*/
发现两处错误:
1.上面红色的部分不符合这一规则:表中所有类型为'收视'的行的 止日=DATEADD(month,1, 起日),
2.被拆分那行加起来天数是29了,不等于原来的30为了让人理解,我说说这种要求背后的原因,用户交费看电视,有时中间有事会暂停,一停往往会把一个月的费截断,这截断的一个月如果加起来等于没断前的天数,用户没什么说的,公司也没什么说的。
截断之前的和之后的行,需要保持上月到下月的整月数,这样方便按月统计报表,以下这些例子都符合这种要求:特殊情况:
2008.2.1 - 2008.2.29 --润年2月
2009.2.1 - 2008.2.28 --平年2月
2008-1-31 - 2008-02-29 --润年2月
2009-1-31 - 2009-02-28 --平年2月
2009.3.1 - 2008.3.31 --普通月份
最一般情况:
2009.5.17 - 2009.5.16 --16比17小1,这行是最常见的情况以上的统一规律是:止日=DATEADD(month,1, 起日)
这主要是怪我问题没说清楚,我上面应该说:无论怎么插删,行之间的日期都应是衔接的。
非常感谢执灬心的朋友们。
if object_id('[jf]') is not null drop table [jf]
go
create table [jf]([证号] int,[端口] int,[时间标记] datetime,[起日] datetime,[止日] datetime,[状态] varchar(4))
insert [jf]
select 1,1,'2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' union all
select 1,1,'2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' union all
select 1,1,'2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' union all
select 1,1,'2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视'
go
--select * from [jf]--创建存储过程:
if object_id('ChaRu','p') is not null
drop proc ChaRu
go
create proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))
as
set nocount on
--所需数据插入临时表
select 时间标记,起日,止日,状态 into #
from jf
where 证号=@证号 and 端口=@端口
--获取关键的时间点
declare @最小起日 datetime
select @最小起日=min(起日) from jf where 止日>@起日
--插入被撤线时间截断的后一条
insert #
select 时间标记,@止日+1,@止日+30-datediff(d,@最小起日,@起日),状态
from #
where 起日=@最小起日
--更新被撤线时间截断的前一条
update #
set 止日=@起日-1
where 起日=@最小起日
--插入撤线记录
insert # values(@时间标记,@起日,@止日,'撤线')
--将撤线止日之后的日期顺延
update #
set 起日=dateadd(m,datediff(m,@最小起日,#.起日)-1,@止日+31-datediff(d,@最小起日,@起日))
,止日=dateadd(m,datediff(m,@最小起日,#.起日),@止日+30-datediff(d,@最小起日,@起日))
from jf t
where #.起日=t.起日 and t.证号=@证号 and t.端口=@端口 and t.起日>@最小起日
--删除已有记录
delete jf where 证号=@证号 and 端口=@端口
--插入新整合的记录
insert jf
select @证号,@端口,时间标记,起日,止日,状态 from # order by 起日
go--测试结果:
exec ChaRu 1,1,'2009-06-12 12:32:17.055','2009.4.20','2009.6.3','撤线'
select * from [jf]
/*
证号 端口 时间标记 起日 止日 状态
----------- ----------- ----------------------- ----------------------- ----------------------- ----
1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-04-10 00:00:00.000 收视
1 1 2009-04-10 13:21:28.507 2009-04-11 00:00:00.000 2009-04-19 00:00:00.000 收视
1 1 2009-06-12 12:32:17.057 2009-04-20 00:00:00.000 2009-06-03 00:00:00.000 撤线
1 1 2009-04-10 13:21:28.507 2009-06-04 00:00:00.000 2009-06-24 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-06-25 00:00:00.000 2009-07-24 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-07-25 00:00:00.000 2009-08-24 00:00:00.000 收视(6 行受影响)
*/
由于,这个问题的确有些复杂,发现我用尽全力也无法一次把问题说清,只能这样和大侠们交流了。
谢谢朋友们的耐心。declare @date datetime
set @date = getdate()
exec ChaRu '1',1,@date,'2009-4-20','2009-6-3','撤线'
go
exec ChaRu '1',1,@date,'2009-3-31','2009-4-9','报停'
go
select * from jf order by 起日1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-04-10 00:00:00.000 收视
1 1 2009-05-26 12:41:05.357 2009-03-31 00:00:00.000 2009-04-09 00:00:00.000 报停
1 1 2009-04-10 13:21:28.507 2009-04-11 00:00:00.000 2009-04-19 00:00:00.000 收视
1 1 2009-05-26 12:38:14.750 2009-04-20 00:00:00.000 2009-06-03 00:00:00.000 撤线
1 1 2009-04-10 13:21:28.507 2009-06-04 00:00:00.000 2009-06-24 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-06-25 00:00:00.000 2009-07-24 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-07-25 00:00:00.000 2009-08-24 00:00:00.000 收视
--获取关键的时间点
declare @最小起日 datetime
select @最小起日=min(起日) from jf where 止日>@起日-------->--获取关键的时间点
declare @最小起日 datetime
select @最小起日=min(起日) from # where 止日>@起日
IF OBJECT_ID('jf') IS NOT NULL
DROP TABLE jf
gocreate table jf(证号 int,端口 int, 时间标记 datetime,起日 datetime,止日 datetime,类型 varchar(10))
go
insert into jf
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视'
go
alter proc ChaRu
(
@证号 nvarchar(6),
@端口 int,
@时间标记 datetime,
@起日 datetime,
@止日 datetime,
@类型 nvarchar(6)
)
as
begin
if @类型 <> '收视' and exists(select 1 from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口 and 类型 = '收视' )
begin
declare @撤分时间标记 datetime
select @撤分时间标记 = 时间标记 from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口 and 类型 = '收视' update jf set 起日 = jf.起日,
止日 = dateadd(day,-1,@起日)
from
(
select * from jf where @起日 between 起日 and 止日 and 证号 = @证号 and 端口 = @端口
)T
where jf.起日 = T.起日 and jf.证号 = T.证号 and jf.端口 = T.端口 and jf.类型 = '收视' insert into jf
select @证号,@端口,@时间标记,@起日,@止日,@类型 insert into jf
select 证号,端口,时间标记,起日= dateadd(day,1,@止日),
止日 = dateadd(day,datediff(day,起日,dateadd(month,1,起日)) - (datediff(day,起日,止日)+1),@止日),类型
from
(
select * from jf where 时间标记 = @撤分时间标记 and 证号 = @证号 and 端口 = @端口 and 类型 = '收视'
)A declare @标记止日 datetime
select @标记止日 = max(止日) from jf where 时间标记 = @撤分时间标记 and 证号 = @证号 and 端口 = @端口 update a
set 起日 = dateadd(month,(select count(1) from jf where 证号 = @证号 and 端口 = @端口 and 时间标记 <= a.时间标记 and 类型 = '收视' and 时间标记 > @撤分时间标记)-1,dateadd(day,1,@标记止日)),
止日 = dateadd(month,(select count(1) from jf where 证号 = @证号 and 端口 = @端口 and 时间标记 <= a.时间标记 and 类型 = '收视' and 时间标记 > @撤分时间标记),@标记止日)
from jf a
where a.时间标记 > @撤分时间标记 and a.类型 = '收视' and a.证号 = @证号 and a.端口 = @端口 end
else
insert into jf
select @证号,@端口,@时间标记,@起日,@止日,@类型
end
godeclare @date datetime
set @date = getdate()
exec ChaRu '1',1,@date,'2009-4-20','2009-6-3','撤线'
exec ChaRu '1',1,@date,'2009-3-31','2009-4-9','报停'
go
select * from jf order by 起日
go/*
1 1 2009-03-10 20:19:08.577 2009-03-11 00:00:00.000 2009-03-30 00:00:00.000 收视
1 1 2009-05-26 12:57:27.170 2009-03-31 00:00:00.000 2009-04-09 00:00:00.000 报停
1 1 2009-03-10 20:19:08.577 2009-04-10 00:00:00.000 2009-04-20 00:00:00.000 收视
1 1 2009-05-26 12:57:27.170 2009-04-20 00:00:00.000 2009-06-03 00:00:00.000 撤线
1 1 2009-04-10 13:21:28.507 2009-05-21 00:00:00.000 2009-06-20 00:00:00.000 收视
1 1 2009-04-10 13:21:28.507 2009-05-21 00:00:00.000 2009-06-20 00:00:00.000 收视
1 1 2009-05-10 09:18:18.570 2009-06-21 00:00:00.000 2009-07-20 00:00:00.000 收视
1 1 2009-06-10 11:08:56.007 2009-07-21 00:00:00.000 2009-08-20 00:00:00.000 收视
*/
我以为你只有撤线和收视两种情况 ,稍微改了下条件
go
exec ChaRu '1',1,@date,'2009-3-31','2009-4-9','报停'
go 你先插入撤线的一个时间段('2009-4-20'--'2009-6-3'),
后面又插入一个时间更早的报停时间段('2009-3-31'--'2009-4-9'),
那原来插入撤线的时间段还要顺延吗?
如果顺延似乎不合逻辑,
如果不顺延时间又衔接不上。
原则有如下几条:
1. 行与行永远要衔接。
2. 新插入的行(不包括被拆的),依先后衔接顺序顺延。
3. 没被拆的月,依先后衔接顺序顺延,同时调整为 止日=DATEADD(month,1, 起日) ,以便以后按月统计报表。
4. 被拆分的用,依先后衔接顺序顺延,保持合计天数等于没拆分前的天数,就是说不做 止日=DATEADD(month,1, 起日) 调整,也做不了。可以看出,都是要顺延的,区别只是整月没拆的,按大小月,做一两天的调整,同时考虑到2月是28还是29天。
DROP TABLE JF CREATE TABLE JF(RID INT IDENTITY(1,1),ZH INT, DK INT, TIMEFLAG SMALLDATETIME, BEGINTIME SMALLDATETIME, ENDTIME SMALLDATETIME,DTYPE VARCHAR(10))
INSERT JF (ZH,DK,TIMEFLAG, BEGINTIME,ENDTIME,DTYPE)
SELECT 1,1, '2009-03-10 20:19:08.577','2009.3.11','2009.4.10','收视' UNION ALL
SELECT 1,1, '2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' UNION ALL
SELECT 1,1, '2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' UNION ALL
SELECT 1,1, '2009-06-10 11:08:56.007','2009.6.11','2009.7.10','收视' --插入数据的存储过程
ALTER proc ChaRu(@zh nvarchar(6),@dk int,@timeflag SMALLDATETIME,
@begintime SMALLDATETIME,@endtime SMALLDATETIME,@dtype nvarchar(6))
AS
BEGIN
declare @MarkRID int ,@beginMark int,@countMark int,
@i int,@newend smalldatetime, @MarkDType varchar(10),@replaceMark int
select @i = 1
--1.定位
select @MarkRID = MAX(RID) FROM JF WHERE BEGINTIME < @BEGINTIME select @beginMark = datediff(day,begintime,@begintime), @MarkDType = DType,
@replaceMark = datediff(d,begintime,endtime) from jf where RID = @MarkRID select @countMark = count(*) from jf where RID >@Rid
--2.更新
BEGIN TRAN
-- 插入新纪录
insert into JF(zh,dk,timeflag,begintime,endtime,dtype)
select @zh,@dk,@timeflag, @begintime,@endtime, @dtype
--更新操作
UPDATE JF SET endtime = dateadd(d,-1,@begintime) WHERE RID = @MarkRID
--取得截断日期
SELECT @newend= dateadd(d,@replaceMark - @beginMark + 1,@endtime)
--插入另一半
INSERT into JF(zh,dk,timeflag,begintime,endtime,dtype)
select @zh,@dk,@timeflag, @endtime + 1,@newend,@MarkDType
--更新
while @i <= @count
begin
update jf set begintime = dateadd(d,1,@newend),
endtime = dateadd(d,-1,dateadd(month,1,dateadd(d,1,@newend)))
-- endtime = dateadd(d,@REPLACEMARK -1 ,dateadd(d,1,@newend))
where rid = @Rid + @i
SELECT @NEWEND = ENDTIME FROM JF WHERE RID = @Rid + @i
select @i = @i + 1
end
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END
----测试插入
exec ChaRu 1,1,'2009-05-01','2009.4.20','2009.6.3','撤线'
select * from jf order by begintime /*
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-04-19 00:00:00 收视
5 1 1 2009-05-01 00:00:00 2009-04-20 00:00:00 2009-06-03 00:00:00 撤线
6 1 1 2009-05-01 00:00:00 2009-06-04 00:00:00 2009-06-24 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-25 00:00:00 2009-07-24 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-07-25 00:00:00 2009-08-24 00:00:00 收视
*/
Alter proc ShanChu(@ZH nvarchar(6),@DK int,@begintime smalldatetime)
AS
BEGIN
DECLARE @PreRID INT, @CurRID INT,
@NextRID INT,@MARKtime smalldatetime,
@COUNT INT ,@I INT,@replaceMark int
SELECT @I = 1
--1.定位
SELECT @CurRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = convert(varchar,@begintime,112)
SELECT @PreRID = RID FROM JF
WHERE Convert(varchar,BEGINTIME,112) = (select convert(varchar,MAX(begintime),112) FROM JF WHERE Begintime < @begintime)
SELECT @NextRID = Min(RID) FROM JF WHERE RID > @CurRID --2.删除&&更新
BEGIN TRAN
--取得插入之前的差值
Select @replaceMark = L.fMark + P.fMark + 1 From
(Select datediff(d, begintime, endtime) as fMark from jf where RID = @PreRID) L,
(Select datediff(d, begintime, endtime) as fMark from jf where RID = @NEXTRID) P
--删除插入的数据
DELETE FROM JF WHERE RID = @CurRID or RID = @NextRID
--更新现有的数据
UPDATE JF SET Endtime = dateadd(d,@replaceMark,BEGINTIME) WHERE RID = @PreRID
--定义循环更新的初始时间
SELECT @MARKtime = endtime FROM JF WHERE RID = @PreRID
--取得总循环次数
SELECT @COUNT = COUNT(*) FROM JF WHERE RID > @PreRID
WHILE @I <= @COUNT
BEGIN
UPDATE JF SET BEGINTIME =Dateadd(day,1,@time),
ENDTIME = dateadd(d,-1,dateadd(month,1,Dateadd(day,1,@time)))
WHERE RID = @I + @PreRID
SELECT @MARKTIME = endtime from jf where RID = @I + @PreRID
Select @I = @I + 1
END
COMMIT TRAN
IF @@ERROR <>0
ROLLBACK TRAN
END
--删除操作执行
exec ShanChu 1,1,'2009.4.20'
select * from JF/*测试结果
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-05-10 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-05-11 00:00:00 2009-06-10 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-06-11 00:00:00 2009-07-10 00:00:00 收视
*/
发现了两处不符
1.你的插入结果集中"时间标记"和我楼顶要求的不是一样的,这个时间标记不是可以随意的,被拆行一分为二后,应具有相同的时间标记,用来表明它们是一体的.下面红色的是我的,兰的是您的,请对比:
证号 端口 时间标记 起日 止日 状态
1 1 2009-03-10 20:19:08.577 2009.3.11 2010.4.10 收视 --在@起日之前,保持原样
1 1 2009-04-10 13:21:28.507 2009.4.11 2010.4.19 收视
1 1 2009-06-12 12:32:17.055 2009.4.20 2009.6.3 撤线 --插入的新行,数据来自ChaRu的参数
1 1 2009-04-10 13:21:28.507 2009.6.4 2009.6.24 收视
1 1 2009-05-10 09:18:18.570 2009.6.25 2009.7.24 收视 --对原jf的2009.5.11-2009.6.10,31天变30天
1 1 2009-06-10 11:08:56.007 2009.7.25 2009.8.24 收视 --对原jf的2009.6.11-2009.7.10,30天变31天
RID ZH DK TIMEFLAG BEGINTIME ENDTIME DTYPE
1 1 1 2009-03-10 20:19:00 2009-03-11 00:00:00 2009-04-10 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-04-11 00:00:00 2009-04-19 00:00:00 收视
5 1 1 2009-05-01 00:00:00 2009-04-20 00:00:00 2009-06-03 00:00:00 撤线
6 1 1 2009-05-01 00:00:00 2009-06-04 00:00:00 2009-06-24 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-06-25 00:00:00 2009-07-24 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-07-25 00:00:00 2009-08-24 00:00:00 收视2.连续执行两次插入,结果就大乱了.下面是我执行两次插入后的混乱结果:
exec ChaRu 1,1,'2009-05-01','2009.4.20','2009.6.3','撤线'
exec ChaRu 1,1,'2009-03-31','2009.4.9','2009.6.3','撤线' 7 1 1 2009-03-31 00:00:00 2009-04-09 00:00:00 2009-06-03 00:00:00 撤线
8 1 1 2009-03-31 00:00:00 2009-06-04 00:00:00 2009-06-05 00:00:00 收视
2 1 1 2009-04-10 13:21:00 2009-06-06 00:00:00 2009-07-05 00:00:00 收视
3 1 1 2009-05-10 09:18:00 2009-07-06 00:00:00 2009-08-05 00:00:00 收视
4 1 1 2009-06-10 11:09:00 2009-08-06 00:00:00 2009-09-05 00:00:00 收视
5 1 1 2009-05-01 00:00:00 2009-09-06 00:00:00 2009-10-05 00:00:00 撤线
6 1 1 2009-05-01 00:00:00 2009-10-06 00:00:00 2009-11-05 00:00:00 收视
1.整月行的,这种行的特点是: a.状态='收视' b.止日=DATEADD(month,1, 起日)
2.用ChaRu插入的新行,特点:数据由ChaRu的参数决定
3.拆分出来的行
无论插入删除,所有日期相邻行的起止日期上都要衔接.
不管哪种行,上方发生插入,都要向下漂移顺延, 上方发生删除,都要向上漂移顺延,不然就不能衔接了.
在漂移顺延的过程中,没被拆分过的整月的止日要根据这个公式重新计算: 止日=DATEADD(month,1, 起日). 这有时会造成起止日之间的天数发生改变,主要是由于大小月、二月、润年。但是其它种类的行的止日不按大小月二月调整,这时止日和起日保持原天数距离。
这点我也同意,写SQL的时候就感觉有些问题。
只是因为具体业务流程并不是特别清楚。所以就没有详细询问。
可以专门弄一个变更表。用来记录所有的变更。
这样比在同一表中操作效果会好些吧。
个人意见,仅供参考。