Code to compute length of time and insert into a column

Question

Code to compute length of time and insert into a column

gwilkins-1074841

Grasshopper

Points: 12
More actions
November 4, 2010 at 11:30 am

#228722

Hello everyone,
Hoping for a little guidance on a query I'm trying to build.
Basically we have 2 tables that have information in them and we want to pull the dates from the query and whichever colum has the latest date, get the date difference in years and update the column at the end of the 2nd column. Basically we have a hire date column, 2 re-hire date columns (for people called back, etc.) and one merger hire date.
If rehire2 and rehire1 are null, and the merger is null, it's just a datediff from hire to today's date. The query should figure out and insert based on if say rehire1 is not null, but rehire2 is, then compute the difference from rehire1.
However, I also need to get a way to extract the table data so it can be read by the DateDiff expression.
To get an idea here's the join I have to get the data set I need:
SELECT Employee.EmpID, Employee.HireDate, Employee.Status,
EmployeeCustom.ReHire1, EmployeeCustom.ReHire2, EmployeeCustom.MergerHireDate
FROM
Employee INNER JOIN EmployeeCustom
ON Employee.EmpID = EmployeeCustom.EmpID
WHERE Employee.Status = 'A' --just active employees
ORDER BY Employee.EmpID;
I just need a little guidance on how to get the data I want into the datediff expression - it's turned into a little more complicated than I thought but the join does pull the correct data, I just have to figure out the next step of where to take that collected data and process it into the datediff and then update the 2nd table (EmployeeCustom).
Thanks in advance for all guidance!
-gary

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You need to give a better definition of your rules for calculating "years".

Do you really mean number of full years based on hire or rehire date? If that is what you want, the function on the link below will do it.

This function returns age in years.

Age Function F_AGE_IN_YEARS:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

gwilkins-1074841 Grasshopper Points: 12 More actions · Answer 2

Thanks Michael.

Yes, we are just looking for the full (integer only) years between either hire or rehire dates. I'll take a look at the function, it looks like it might help out quite a bit!

I could run each individually and then update, but I thought we'd be able to do it more elegantly! 🙂

Thanks again!

-gary-

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

I don't understand why you want to store this information is a table, since the length of time changes from one day to the next and the data will be out of date every time you start a new day.

Why not just compute the length of time when you need it?

gwilkins-1074841 Grasshopper Points: 12 More actions · Answer 4

It's basically a one time (or once a year) update that will be static but it needs to be stored in a form. We just run once a year at end of year to update the table so the form can see it.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

So you are storing a calculated field that could just as easily be calculated when you need it? Then once a year you update the calculated field with a new value? That sounds horribly inefficient and destined to cause some problems when somebody forgets to run the update some year.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

gwilkins-1074841 (11/4/2010)
It's basically a one time (or once a year) update that will be static but it needs to be stored in a form. We just run once a year at end of year to update the table so the form can see it.

Why not use a persisted calculated column and never worry about it ever again?

Also... what happens if you hire the same person back 3 times?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)