Using Conditional DATEADD function

  • Trying to add X number of days to a date depending on the value of another variable.

    I have data in a single view with the following format:

    VULNID VULNDATE RISK

    123 10/01/2011 8

    312 09/14/2011 8

    435 08/30/2011 7

    342 08/04/2011 9

    I would like to return the following values with the DUEDATE being calculated based on the RISK. For example....if the RISK is 8 or above I would like to calculate the DUEDATE as VULNDATE + 30 days. If the RISK is anything else I'd like to calculate the DUEDATE as VULDATE + 90 Days.

    The output of the query should look like this:

    VULNID VULNDATE RISK DUEDATE

    123 10/01/2011 8 11/01/2011

    312 09/14/2011 8 10/14/2011

    435 08/30/2011 7 11/30/2011

    342 08/04/2011 9 09/03/2011

    I suspect that the solution involves using the case statement along with the DATEADD function but I'm getting tripped up by the syntax.

    Thank you in advance for your guidance.

  • Will this work?

    CASE WHEN RISK >= 8 THEN DATEADD(DAY, 30, VULNDATE) ELSE DATEADD(DAY, 90, VULNDATE) END AS DueDate

    EDIT:

    ">=" in the case statement...

  • Thanks....that is the information I needed.

  • case when riskno=8

    then dateadd(day,30,riskdt)

    else dateadd(day,90,riskdt)

    end as duedate

    try this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply