dateadd using datepart from column

Question

Is it possible to use the DATEADD function and get the datepart argument from a column for each row? Here is an example -

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

The goal is to fill in the column called NextService - for example row 1 would take 6 months and add that to the date which appears in DateCompleted (9/30/2010):

-----added later... The posted answers work; however, I was wondering if there was a way to convert the value in IntervalUOM into the datepart argument directly. That way only one DATEADD line would be needed.

Answer 1

Is this what you're looking for?

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

Here's the whole query:

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

Answer 2

A select query like this:

SELECT *, 
  CASE [IntervalUOM] 
    WHEN 'months' THEN DATEADD(MONTH, Interval, [DateCompleted]) 
    WHEN 'years' THEN DATEADD(YEAR, Interval, [DateCompleted]) 
  END AS NextService 
FROM @maintenance;

Would give the following result:

UserID   PropertyID  TaskID    Interval    IntervalUOM DateCompleted NextService
-------- ----------- --------- ----------- ----------- ------------- -----------
2        1           4         6           months      2010-09-30    2011-03-30
2        1           4         6           months      2011-04-16    2011-10-16
2        1           4         6           months      2011-09-28    2012-03-28
2        1           4         6           months      2012-04-10    2012-10-10
2        1           7         1           years       2001-12-20    2002-12-20
2        1           7         1           years       2002-12-21    2003-12-21
2        1           7         1           years       2003-12-16    2004-12-16
2        1           7         1           years       2004-12-22    2005-12-22
2        1           7         1           years       2005-12-27    2006-12-27
2        1           7         1           years       2006-12-31    2007-12-31

(10 row(s) affected)

To use it in to update the @maintenance table do this:

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

This assumes the table has a column called NextService.