Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-03 : 08:37:59
|
| John writes "I have an employees table. Each employee can have multiple entries. one of the fields in the table is the person's dept code. An employee can transfer to a differnent dept during their empolyment. I need to query against another table and pull a s ingle record for every employee for a given date, how do I write the subquery so that it returns the correct recod for an employee and what dept they worked for based on a certain date. IE. employee A changed departments every month, a record in the emplyee table would reflect everytime the person changed depts. How can I query what dept that person worked for the third week of any given month?" |
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2001-12-03 : 08:48:21
|
| Hi, id have your table structure have StartDate and EndDateif it is the current record then have EndDate = NULLthen just do a selectwhere SearchDate between StartDate and isnull(EndDate, SearchDate)should do the trickcol |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-03 : 09:10:53
|
Assuming there's a unique constraint on (employeeID, startDate):SELECT *FROM Employees aWHERE startDate = ( SELECT MAX(startDate) FROM Employees b WHERE a.employeeID = b.employeeID AND startDate <= @searchDate) |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-03 : 12:38:28
|
| Did I read your question right? Are you saying that every time an employee changes departments, that you create a new row in the Employees table? Doesn't that create a lot of repetetive information, such as Address? You might consider leaving the Employee info static and create a separate child table that contains EmployeeID, DeptNo, StartDate, EndDate, and then just insert rows into this child table when the employee moves.-------------------It's a SQL thing... |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2001-12-03 : 12:53:24
|
| Marks solution makes sense....I use it myself all over the place, as I need to track movement of people/ things over time....but you might need to consider what values appear in EndDate....If you leave it null....for active/current records...what happens if you want the 'movement history' records sorted by enddate....the records with null in them may end up being in the wrong place/order...what I find to work...is to put a value of 'infinity' in the enddate field....to represent "no-end-date-yet"...but since infinity isn't a value SQL date (yet), I substitute a date value which is so far into the future that it might as well be infinity...for me 23:59:59 31/12/2099....and when I know when a person is moving, then I update the enddate of the current record to a 'now' value, and then create a new history record with "now" as the next start value and "my infinity" as the end value...sample data...person - job - start - endbill finance 01/01/2001 09:00 31/12/2099 23:59bill then moves to sales, so above is updated to becomebill finance 01/01/2001 09:00 03/12/2001 17:00and below is insertedbill sales 03/12/2001 17:00 31/12/2099 23:59this way bill can't do 2 jobs at the same time....and will have no gaps in his employment history.and if I want bill's work experience in reverse end-date order...ie most relevent first...then i'll get bill sales 03/12/2001 17:00 31/12/2099 23:59bill finance 01/01/2001 09:00 03/12/2001 17:00Regards, Andrew |
|
|
|
chaugner
Starting Member
9 Posts |
Posted - 2001-12-03 : 15:23:12
|
| Does your Employee table have some kind of identity?If yes you can do the following.select * from (select Max(YourID) YourID from EMPLOYEE group by EmployeeName) idMax join EMPLOYEE e on idMax.YourID = e.YourIDwhere ....this will result in the latest record for every employee.You then can add a date filter inside the subquery to "step back in time". For example if you want to know what person worked in the third week for a department in 1998.Is that what you are looking for?Edited by - chaugner on 12/03/2001 15:25:23 |
|
|
|
|
|
|
|
|
Subquery