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
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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