sqlserver 时间分割

Question

sqlserver

Answer 1

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
dt1                     dt2                           price
----------------------- ----------------------- -----------
2015-01-01 00:00:00.000 2016-12-31 00:00:00.000         500dt1                     dt2                           price
----------------------- ----------------------- -----------
2015-01-01 00:00:00.000 2015-01-31 00:00:00.000         500
2015-02-01 00:00:00.000 2015-10-03 00:00:00.000         600
2015-10-04 00:00:00.000 2016-12-31 00:00:00.000         500dt1                     dt2                           price
----------------------- ----------------------- -----------
2015-01-01 00:00:00.000 2015-01-31 00:00:00.000         500
2015-02-01 00:00:00.000 2015-05-02 00:00:00.000         600
2015-05-03 00:00:00.000 2016-05-03 00:00:00.000         800
2016-05-04 00:00:00.000 2016-12-31 00:00:00.000         500