[100元话费]来个精通数学和SQL的高手指导下一个SQL语句

Question

--表结构
CREATE TABLE [dbo].[ERPZJPhaseTime](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [bianhao] [int] NOT NULL,
    [phase] [int] NULL,
    [phasename] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
    [starttime] [smalldatetime] NULL,
    [endtime] [smalldatetime] NULL,
    [days] [int] NULL,
    [bzstate] [int] NULL,
    [shstate] [int] NULL,
    [AchieveState] [int] NULL,
    [category] [int] NULL,
    [orderid] [int] NULL)
--测试用数据
insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( 2,2,1,NULL,'2012-07-04 00:00:00.000','2012-07-05 00:00:00.000',2,NULL,NULL,NULL,2,1)
insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( 3,2,2,NULL,'2012-07-06 00:00:00.000','2012-07-08 00:00:00.000',3,NULL,NULL,NULL,2,1)
insert ERPZJPhaseTime (id,bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( 4,2,3,NULL,'2012-07-10 00:00:00.000','2012-07-13 00:00:00.000',4,NULL,NULL,NULL,2,1)

要求：表中已有bianhao=2的3个时间段，它们是一个整体，也就是说表中原有数据bianhao相同的要按整体看待。现在我将要插入一行新的时间段，要求就是不能产生重复时间。如果重复，新增的时间段不变动，表中原有的时间将向后顺延。
--测试用代码

declare  @starttime smalldatetime ,@endtime smalldatetime ,@days int ,@category int ,@effectrow int
set @starttime ='2012/07/03';set @endtime='2012/07/05';set @days=3; set @bianhao=2;


以下内容是我想的思路，仅供参考。
先检测时间是否存在冲突(@starttime between starttime and endtime or @endtime between starttime and endtime),存在冲突则依情况一和二来处理。
情况一：starttime >=@startime，时间接着新增的时间向后移动;情况二：@starttime > starttime and @starttime <=endtime.将冲突所在行截为两段。

Answer 1

StartFragment declare  @starttime1 smalldatetime ,@endtime1 smalldatetime ,@days1 int ,@bianhao1 INT;set @starttime1 ='2012/07/04';set @endtime1='2012/07/06';set @days1=3; set @bianhao1=2;    DECLARE @id INT;    DECLARE @bianhao int;    DECLARE @phase int;    DECLARE @phasename nvarchar(200);    DECLARE @starttime smalldatetime;    DECLARE @endtime smalldatetime;    DECLARE @days int;    DECLARE @bzstate int;    DECLARE @shstate int;    DECLARE @AchieveState int;    DECLARE @category int;    DECLARE @orderid INT;    DECLARE @OP INT;    DECLARE @NUM INT;    DECLARE @pendtime smalldatetime;    SET @OP=0;    SET @NUM=0;    DECLARE C1 CURSORFOR SELECT * FROM ERPZJPhaseTime WHERE bianhao=2 ORDER BY starttimeOPEN C1;FETCH NEXT FROM C1 INTO @id,@bianhao,@phase,@phasename,@starttime,@endtime,@days,@bzstate,@shstate,@AchieveState,@category,@orderid WHILE @@FETCH_STATUS=0 BEGIN IF @OP>0 BEGIN IF @NUM>0 BEGIN   UPDATE ERPZJPhaseTime SET starttime=DATEADD(DAY,@NUM,@starttime),endtime=DATEADD(DAY,@NUM,@endtime) WHERE id=@id; END END  ELSE IF @OP<=0 BEGIN  IF DATEDIFF(dd,@starttime1,@starttime)>=0  BEGIN    INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( @bianhao1,NULL,NULL,@starttime1,@endtime1,@days1,NULL,NULL,NULL,2,1);SET @OP=1;IF DATEDIFF(dd,@endtime1,@starttime)<=0BEGIN  SET @NUM=DATEDIFF(dd,@starttime,@endtime1)+1;  UPDATE ERPZJPhaseTime SET starttime=DATEADD(DAY,@NUM,@starttime),endtime=DATEADD(DAY,@NUM,@endtime) WHERE id=@id;  SET @OP=1;END  END  ELSE IF (DATEDIFF(dd,@starttime1,@starttime)<0 AND DATEDIFF(dd,@starttime1,@endtime)>=0)  BEGIN    INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values ( @bianhao1,NULL,NULL,@starttime1,@endtime1,@days1,NULL,NULL,NULL,2,1)SET @OP=1;UPDATE ERPZJPhaseTime SET endtime=DATEADD(DAY,-1,@starttime1),days=DATEDIFF(dd,starttime,DATEADD(DAY,-1,@starttime1))+1 WHERE id=@id;SET @NUM=@days1+DATEDIFF(dd,@starttime1,@endtime)+1;INSERT INTO ERPZJPhaseTime (bianhao,phase,phasename,starttime,endtime,days,bzstate,shstate,AchieveState,category,orderid)  values (@bianhao,@phase,@phasename,DATEADD(DAY,@days,@starttime1),DATEADD(DAY,@days,@endtime),DATEDIFF(dd,DATEADD(DAY,@days,@starttime1),DATEADD(DAY,@days,@endtime))+1,@bzstate,@shstate,@AchieveState,@category,@orderid)  END   END FETCH NEXT FROM C1 INTO @id,@bianhao,@phase,@phasename,@starttime,@endtime,@days,@bzstate,@shstate,@AchieveState,@category,@orderid ENDCLOSE C1;DEALLOCATE C1;SELECT * FROM ERPZJPhaseTime WHERE bianhao=2 ORDER BY starttime

Answer 2

呵呵,太复杂了,大家都懒得看

Answer 3

变成 (1,1) (2,6) (7,8) (9,11)