October 16, 2011 at 11:44 pm
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.
October 17, 2011 at 12:02 am
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...
October 17, 2011 at 9:01 pm
Thanks....that is the information I needed.
October 18, 2011 at 3:39 am
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