sql-server - 使用列中的日期部分进行日期添加

Question

是否可以使用 DATEADD 函数并从每一行的列中获取日期部分参数？这是一个例子 -

declare @maintenance table
(
  UserID int,
  PropertyID int,
  TaskID int,
  Interval int,
  IntervalUOM varchar(10),
  DateCompleted date,
  primary key (UserID, PropertyID, TaskID, DateCompleted)
);

insert into @maintenance (UserID, PropertyID, TaskID, Interval, IntervalUOM, DateCompleted) values
(2, 1, 4, 6, 'months', '9/30/2010'),
(2, 1, 4, 6, 'months', '4/16/2011'),
(2, 1, 4, 6, 'months', '9/28/2011'),
(2, 1, 4, 6, 'months', '4/10/2012'),
(2, 1, 7, 1, 'years', '12/20/2001'),
(2, 1, 7, 1, 'years', '12/21/2002'),
(2, 1, 7, 1, 'years', '12/16/2003'),
(2, 1, 7, 1, 'years', '12/22/2004'),
(2, 1, 7, 1, 'years', '12/27/2005'),
(2, 1, 7, 1, 'years', '12/31/2006');

select *, NextService = null from @maintenance;
go

目标是填写名为 NextService 的列 - 例如，第 1 行需要 6 个月，并将其添加到 DateCompleted (9/30/2010) 中显示的日期:

-----稍后添加...发布的答案有效；但是，我想知道是否有办法将 IntervalUOM 中的值直接转换为 datepart 参数。这样只需要一个 DATEADD 行。

Answer 1

这是您要找的吗？

SELECT 
    CASE [IntervalUOM] 
        WHEN 'Months' THEN 
            DATEADD(MONTH, Interval, [DateCompleted]) 
        WHEN 'Years' THEN 
            DATEADD(YEAR, Interval, [DateCompleted]) 
    END

这是整个查询:

DECLARE @maintenance TABLE
(
    UserID int,
    PropertyID int,
    TaskID int,
    Interval int,
    IntervalUOM varchar(10),
    DateCompleted date,
    NextService date,
    PRIMARY KEY (UserID, PropertyID, TaskID, DateCompleted)
);

INSERT INTO 
    @maintenance (
        UserID, 
        PropertyID, 
        TaskID, 
        Interval, 
        IntervalUOM, 
        DateCompleted, 
        NextService) 
VALUES 
    (2, 1, 4, 6, 'months', '9/30/2010', NULL),
    (2, 1, 4, 6, 'months', '4/16/2011', NULL),
    (2, 1, 4, 6, 'months', '9/28/2011', NULL),
    (2, 1, 4, 6, 'months', '4/10/2012', NULL),
    (2, 1, 7, 1, 'years', '12/20/2001', NULL),
    (2, 1, 7, 1, 'years', '12/21/2002', NULL),
    (2, 1, 7, 1, 'years', '12/16/2003', NULL),
    (2, 1, 7, 1, 'years', '12/22/2004', NULL),
    (2, 1, 7, 1, 'years', '12/27/2005', NULL),
    (2, 1, 7, 1, 'years', '12/31/2006', NULL);

UPDATE 
    @maintenance 
SET 
    NextService = CASE [IntervalUOM] 
        WHEN 'Months' THEN 
            DATEADD(MONTH, Interval, [DateCompleted]) 
        WHEN 'Years' THEN 
            DATEADD(YEAR, Interval, [DateCompleted]) END
GO