Here is my dilemma. I have a table (myTable) that contains two columns (StartDate, AdjustedHireDate). Each row contains information for each employee with EmployeeID as the PK.
I need to evaluate these two columns and select whichever date is latest (d1).
Then I am going to update the table based on a date this year that matches the month and day of the 'd1' date (UserDefinedDate1). Following that, I will add 60 days to the UserDefinedDate1 column and get a date in the future.
I'm close, but I cant get the d1 column populated because it's returning more than one value. I don't know how to separate the update for each row.
Does that make sense??
declare @d1 DATETIME
set @d1 = (SELECT CASE WHEN AdjustedHireDate >= StartDate
--SET UserDefinedDate1 = DATEADD(YEAR, year(getdate())-year(d1), D1)
-- , UserDefinedDate3 = DATEADD(DAY, 60, DATEADD(YEAR, year(getdate())-year(D1), D1))
-- , UserDefinedDate5 = DATEADD(DAY, 180, DATEADD(YEAR, year(getdate())-year(D1), D1))
In the end I would like to have this result:
startdate AHDate d1 UDDate1 UDDate2
02/01/2007 05/16/2009 5/16/2009 5/16/2014 4/16/2014
Thanks for any help you can provide.