?
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
Medium Priority
?
327 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

592 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