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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query to calculate avaerage 21 52
Find results from sql within a time span 11 46
Re-appearing SQL Server Agent jobs 7 29
SQL Database Restore 2008 R2 1 12
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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