Solved

Comparing two columns in a table, creating another column based on said column, then updating other columns in the table based on the new column

Posted on 2014-03-14
5
316 Views
Last Modified: 2014-03-17
Hi Experts...

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??

--UPDATE myTable
declare @d1 DATETIME
set @d1 = (SELECT CASE WHEN AdjustedHireDate >= StartDate
				Then AdjustedHireDate
				else StartDate
				end
			FROM myTable)

--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))


SELECT
		SupervisorID
		, EmployeeID
		, StartDate						
		, AdjustedHireDate
		, UserDefinedDate1
		, UserDefinedDate2
		, UserDefinedDate3

FROM dbo.tblPaEmpGenInfo

Open in new window


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.
0
Comment
Question by:Erick W
  • 2
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39930563
UPDATE dbo.tblPaEmpGenInfo
SET
    UserDefinedDate1 = UserDefinedDate1,
    UserDefinedDate3 = DATEADD(DAY, 60, UserDefinedDate1),
    UserDefinedDate5 = DATEADD(DAY, 180, UserDefinedDate1)
FROM dbo.tblPaEmpGenInfo peg
    /* (SELECT CAST('20130303' AS datetime) AS AdjustedHireDate,
               CAST('20130207' AS datetime) AS StartDate
    ) AS test_data */
CROSS APPLY (
    SELECT CASE WHEN AdjustedHireDate >= StartDate
        THEN AdjustedHireDate
            ELSE StartDate
        END AS LaterDate
) AS cross_apply_1
CROSS APPLY (
    SELECT DATEADD(YEAR, YEAR(GETDATE())-YEAR(LaterDate), LaterDate) AS UserDefinedDate1
) AS cross_apply_2
0
 

Author Comment

by:Erick W
ID: 39930666
When I tried your solution (which is great, I've never seen CROSS APPLY) I get an ambiguous column name for the second UserDefinedDate1 field in

 UserDefinedDate1 = UserDefinedDate1,

Any ideas?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930744
I suspect Scott meant (no points please):
SET     UserDefinedDate1 = peg.UserDefinedDate1,
        UserDefinedDate3 = DATEADD(DAY, 60, peg.UserDefinedDate1),
        UserDefinedDate5 = DATEADD(DAY, 180, peg.UserDefinedDate1)

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 39930889
D'OH, sorry, here's the
CORRECTION:

...
SET
    UserDefinedDate1 =  cross_apply_2.UserDefinedDate1,
    UserDefinedDate3 = DATEADD(DAY, 60,  cross_apply_2.UserDefinedDate1),
    UserDefinedDate5 = DATEADD(DAY, 180,  cross_apply_2.UserDefinedDate1)
...


Maybe I should have used a different column name in c_a_2 -- naturally change it to a unique name if you prefer.  But I like names that are self-documenting whenever possible.
0
 

Author Comment

by:Erick W
ID: 39934443
Scott,

Thank you very much. Incidentally, I did the same thing Anthony did. LOL wrong table.

Thank you again. Full points from your original answer.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 48
Query for timesheet application 3 24
Limit number of characters returned to 999 9 31
How can I find this data? 3 23
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question