Lucas A Lucas A - 7 months ago 20
SQL Question

DateAdd with abbreviation from column

I have have the following SQL that increases the starttime of each row in the result from MOCKTABLE.

SELECT
DATEADD( "d", ROW_NUMBER() OVER (ORDER BY mt.ID), mt.StartTime ) AS INCREMENT
FROM
MOCKTABLE mt


Now I would like to trade the "d" to a column specified in MOCKTABLE. Like this:

SELECT
DATEADD( mt.PeriodTime, ROW_NUMBER() OVER (ORDER BY mt.ID), mt.StartTime ) AS INCREMENT
FROM
MOCKTABLE mt


How can I do this? The column PeriodTime is nvarchar(5) and will contain 'd', 'ww' or 'm'. Reason I want this is because the user should decide the incrementation of the orginial date.

Answer

You can do it with a big case statement:

SELECT (case when PeriodTime = 'day'
             then DATEADD(day, ROW_NUMBER() OVER (ORDER BY mt.ID), mt.StartTime)
             when PeriodTime = 'month'
             then DATEADD(month, ROW_NUMBER() OVER (ORDER BY mt.ID), mt.StartTime)
             when PeriodTime = 'year'
             then DATEADD(year, ROW_NUMBER() OVER (ORDER BY mt.ID), mt.StartTime)
        end) AS INCREMENT
FROM MOCKTABLE mt