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
304 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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now