multiple personnel action records

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

multiple personnel action records

Author

Topic

janwane
Starting Member

9 Posts

Posted - 2011-07-08 : 12:36:23

In my relational world an employee can have 1-to-many personnel action records:

PID int
UserID int
PositionID int
JobTitle varchar(100)
DateEff smalldatetime
SupvUserID int
SupvName varchar(50)
Parttime bit
PosActnID int

In the first record for Emp A the "DateEff" is the date the person was hired (equates to Min(DateEff)). In the last record for Emp A the job title represents the emp's current position (equates to Max(DateEff)). The non-repeating employee data (name, email address, etc) is in a table named Users.

Can I get it all in 1 tsql query to get the emp info plus the Date Hired plus the current job title? I have figured out how to get either or but not both. Following is example of getting the date hired:

"SELECT Users.NameLast + ', ' + Users.NameFirst AS EmpName, PosEmpXref.DateEff AS DateHired FROM Users INNER JOIN PosEmpXref ON Users.UserID = PosEmpXref.UserID WHERE (Users.EmpFlag = 1) AND (PosEmpXref.DateEff = (SELECT MIN(DateEff) AS LatestPos FROM PosEmpXref AS PosEmpXref_1 WHERE (Users.UserID = UserID))) AND (PosEmpXref.DateEff > @DateHired) ORDER BY Users.NameLast, Users.NameFirst"

Since the key phrase is in the "WHERE" statement I don't see how I can do both. I think I should do it in the SELECT statement but haven't been able to get the syntax correct. Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-08 : 13:20:21

Give this a try:

SELECT 
u.NameLast + ', ' + u.NameFirst AS EmpName, 
p1.DateEff AS DateHired,
COALESCE(p2.JobTitle,p1.JobTitle,'no Title')
FROM Users u
INNER JOIN PosEmpXref p1 
  ON u.UserID = p1.UserID AND (p1.DateEff = (SELECT MIN(DateEff) 
                                             FROM PosEmpXref AS PosEmpXref_1 
                                             WHERE (u.UserID = PosEmpXref_1.UserID)))
                          AND (p1.DateEff > @DateHired)
LEFT JOIN PosEmpXref p2 
  ON u.UserID = p2.UserID AND (p2.DateEff = (SELECT MAX(DateEff) 
                                             FROM PosEmpXref AS PosEmpXref_2 
                                             WHERE (u.UserID = PosEmpXref_2.UserID)))
WHERE (u.EmpFlag = 1)  
ORDER BY u.NameLast, u.NameFirst

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources