Merging Data from two tables.

Question

Merging Data from two tables.

Ken at work

Hall of Fame

Points: 3426
More actions
January 24, 2018 at 6:25 am

#332847

Hello, I thought this would be easy but I'm kind of stuck. I need to get Job Position titles from one table and display them along side the Timecard history data. Here is what I have.
Create Table Timecard (PRCompany int, PREmployee int, WeekEndDate date, DateWorked date, Hours real);
You can see the above has the employee Id, plus the week end and well as the actual days worked. I'd like to select this table, above, as is. And add the employee's position/title for the days they worked.
There are three other tables, all in HR. HR employee, employee's history, and position/title
Create Table PositionHistory (HRCompany, HREmployee int, EffectiveDate date, PositionCode varchar(10));
I'm including these other two tables do you can see the whole picture.
Create Table Employee (HRCompany int, HREmployee int, PRCompany int, PREmployee int);
Create Table HRCodes (HRCompany, PositionCode varchar(10), Title varchar(30));
I thought it would be fairly easy, but my issue is that the DataWorked, from the Timecard table, is daily. Also the EffectiveDate, from the PositionHistory, is only occasional. I need to match these dates up. So I need to choose the Max EffectiveDate <= DateWorked and select the Position code for that date. But do this for every employee, for every day of the week, and be able to run historical reporting. This is where I'm stuck.
Thank you for any help you can give me.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

It's not entirely clear what your primary key and foreign keys are. Assuming PRCompany, PREmployee and HRCompany, HREmployee are paired together, one way to do this is:
SELECT tc.PRCompany, tc.PREmployee, pc.PositionCode, hr.Title FROM Timecard tc INNER JOIN Employee e ON tc.PRCompany = e.PRCompany AND tc.PREmployee = e.PREmployee CROSS APPLY (SELECT TOP 1 HRCompany, PositionCode FROM PositionHistory ph WHERE ph.HRCompany = e.HRCompany AND ph.HREmployee = e.HREmployee AND ph.EffectiveDate <= tc.DateWorked ORDER BY ph.EffectiveDate DESC) pc INNER JOIN HRCodes hr ON pc.HRCompany = hr.HRCompany AND pc.PositionCode = hr.PositionCode

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

Amazing!
I've never used Cross Apply. I have to do some reading up on that.

Thank you very much for your help,

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

Could I have used a regular join and ph.EffectiveDate <= tc.DateWorked, which I also didn't think of?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

the problem with doing an INNER JOIN instead of CROSS APPLY, is if there are multiple history records. you'd probably want to include a ROW_NUMBER() evaluation in a subquery, maybe something like:
ROW_NUMBER() OVER (PARTITION BY HRCompany, HREmployee ORDER BY ph.EffectiveDate DESC) AS recentness
and then you'd have to evaluate
WHERE recentness = 1
in the outer query.

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

I was wondering about the Row Number or something else being needed. That is why I couldn't get it to work at the start. I've started reading up on the Cross Apply, and it does seem to solve that problem. I've tested the script you've given me and it worked great the first try.

Thank you again for your help. I'm sure the Cross Apply will see more use to me.