sqlserver 时间段拆分

以前遇到的一个问题，csdn上求助得到的答案,记录下

输入一个时间      2015/1/1 -2016/12/31   价格500
 在输入一个时间   2015/2/1-2015/10/3       价格600
这时数据变成  
2015/1/1 - 2015/2/1 价格500  
2015/2/1 - 2015/10/3价格600 
2015/10/3 - 2016/12/31价格500

如果在上结果基础上 在输入2015/5/3-2016/5/3 价格800
结果变成 
2015/1/1-2015/5/3 价格500
2015/5/3 -2016/5/3 价格800
2016/5/3 -2016/12/31价格500

 

CREATE TABLE table1(
    dt1 datetime,
    dt2 datetime,
    price int
)
GO
CREATE PROCEDURE SetPrice(@dt1 datetime,@dt2 datetime,@price int)
AS 
BEGIN
    IF EXISTS(SELECT * FROM table1 WHERE dt1 = @dt1 AND dt2 = @dt2)
    BEGIN
        -- 有完全相同的分段，直接更新价格
        UPDATE table1 SET price = @price WHERE dt1 = @dt1 AND dt2 = @dt2
    END
    ELSE
    BEGIN
        -- 因为有一段截掉中间变二段的可能，用INSERT插入截取后的段
        INSERT INTO table1
        SELECT dt1, DateAdd(day,-1,@dt1), price
          FROM table1
         WHERE dt1 < @dt1 AND @dt1 <= dt2

        INSERT INTO table1
        SELECT DateAdd(day,1,@dt2), dt2, price
          FROM table1
         WHERE dt1 <= @dt2 AND @dt2 < dt2

        -- 删除所有和新段有交集（MAX(@dt1,dt1) <= MIN(@dt2,dt2)）的段
        DELETE FROM table1
         WHERE (CASE WHEN @dt1 > dt1 THEN @dt1 ELSE dt1 END) <=
               (CASE WHEN @dt2 < dt2 THEN @dt2 ELSE dt2 END)

        -- 插入新分段
        INSERT INTO table1 VALUES (@dt1,@dt2,@price)
    END

    RETURN
END
GO
DELETE FROM table1
EXEC SetPrice '2015-01-01','2016-12-31',500
SELECT * FROM table1 ORDER BY dt1
EXEC SetPrice '2015-02-01','2015-10-03',600
SELECT * FROM table1 ORDER BY dt1
EXEC SetPrice '2015-05-03','2016-05-03',800
SELECT * FROM table1 ORDER BY dt1