当前位置: 代码迷 >> Sql Server >> 日期运算:求插入和删除一行数据的带参存储过程解决方案
  详细解决方案

日期运算:求插入和删除一行数据的带参存储过程解决方案

热度:324   发布时间:2016-04-27 22:00:01.0
日期运算:求插入和删除一行数据的带参存储过程
求两个带参数的存储过程,分别对下表插入一行和删除一行

交费表: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 收视 [email protected],保持原样 
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-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 收视 
这条数据没看懂。尤其是2009.6.24 怎么来的。楼主可以解释下吗?

------解决方案--------------------
月份多加了一个月,改一个小地方即可。
SQL code
if object_id('[jf]') is not null drop table [jf]gocreate 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 allselect 1,1,'2009-04-10 13:21:28.507','2009.4.11','2009.5.10','收视' union allselect 1,1,'2009-05-10 09:18:18.570','2009.5.11','2009.6.10','收视' union allselect 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 ChaRugocreate proc ChaRu(@证号 nvarchar(6),@端口 int,@时间标记 datetime,@起日 datetime,@止日 datetime,@类型 nvarchar(6))as    set nocount on--所需数据插入临时表    select 时间标记,起日,止日,状态 into #    from jf    where [email protected] and [email protected] --获取关键的时间点    declare @最小起日 datetime    select @最小起日=min(起日) from jf where 止日>@起日--插入被撤线时间截断的后一条    insert #    select 时间标记,@止日+1,@止日+30-datediff(d,@最小起日,@起日),状态    from #    where [email protected]--更新被撤线时间截断的前一条    update #    set [email protected]    where [email protected]--插入撤线记录    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 [email protected] and [email protected] and t.起日>@最小起日--删除已有记录    delete jf where [email protected] and [email protected] --插入新整合的记录    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 行受影响)*/
  相关解决方案