March 20, 2012 at 6:55 am
I had a question someone asked me the other day that stumped me a bit. I know it's something fairly easy, but I couldn't get it.
If I have a table of employee salaries from 2010, and another table for 2011, how can I combine the tables in a query that will give me the common results, a col for 2010 salary and one for 2011 salary?
2010 table:
employeeID, dateHired, employeeName, salary
2011 table:
employeeID, dateHired, employeeName, salary
resultTable:
employeeID, dateHired, employeeName, 2010salary, 2011salary
I made an attempt, but the only thing I could come up with was to right join to get all of the employees in the 2011 table, but that leaves out the people who were let go before 2011 who have 2010 salaries. If I do it the other way, I leave out anyone hired in 2011.
Again, I know this is something fairly simple.
Thanks!
March 20, 2012 at 7:06 am
You should use FULL OUTER JOIN!
March 20, 2012 at 7:08 am
Damn it, I knew it was something like that. Thanks. I have to be honest, I've never used it before.
March 20, 2012 at 7:20 am
Actually, you may not need to use it if you have dedicated Employee table (which most likely you do have). Then your query can use just left joins, something like:
SELECT e.employeeID AS employeeID
,ISNULL(s10.dateHired, s11.dateHired) AS dateHired
,s10.salary AS 2010salary
,s11.salary AS 2011salary
FROM Employee AS e
LEFT JOIN Salary2010 AS s10
ON s10.employeeID = e.employeeID
LEFT JOIN Salary2011 AS s11
ON s11.employeeID = e.employeeID
Also, the dateHired will be better placed in the Employee table (is any reason for its denormalization?).
March 20, 2012 at 7:22 am
It would have been much easier if I did, but in the example I was given, it was just two tables of data I had to combine. Thanks tho, that makes a lot of sense.
March 20, 2012 at 7:32 am
try this:
[font="System"]SELECT
employeeID, dateHired, employeeName, max([2010Salary]), max([2011Salary])
FROM (
SELECT
employeeID, dateHired, employeeName, salary as [2010Salary], 0 as [2011Salary]
FROM [2010Table]
UNION
SELECT
employeeID, dateHired, employeeName, 0 as [2010Salary], salary as [2011Salary]
FROM [2011Table]
)x
GROUP BY
employeeID, dateHired, employeeName[/font]
March 20, 2012 at 7:53 am
slowder (3/20/2012)
try this:[font="System"]SELECT
employeeID, dateHired, employeeName, max([2010Salary]), max([2011Salary])
FROM (
SELECT
employeeID, dateHired, employeeName, salary as [2010Salary], 0 as [2011Salary]
FROM [2010Table]
UNION
SELECT
employeeID, dateHired, employeeName, 0 as [2010Salary], salary as [2011Salary]
FROM [2011Table]
)x
GROUP BY
employeeID, dateHired, employeeName[/font]
????? Why ?????
1. It will not work, if dateHired is only populated in the year employee was employed
2. It will be slower than FULL OUTER JOIN
March 20, 2012 at 9:22 am
Just a tip. The two jears worth of employee data probably shouldn't be in separate tables to begin with. Understanding that it may not be possible to actually rectify that problem, you might want to look into "Partitioned Views" for future queries so that all you have to do is change the content of a view to make yearly changes instead of changing code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2012 at 9:27 am
Oh, don't get me wrong. I would never set it up like that. It was a question that someone came to me with to see if I could answer it. To tell the truth, I think it might have been an interview question 🙂
March 20, 2012 at 4:21 pm
Matthew Cushing (3/20/2012)
Oh, don't get me wrong. I would never set it up like that. It was a question that someone came to me with to see if I could answer it. To tell the truth, I think it might have been an interview question 🙂
interview? i would have gone with a different schema
CREATE TABLE NewPay (
employeeID INT, -- Or what ever data type it really is
dateHired DATETIME,
employeeName VARCHAR(64),
PayYear INT,
Salary Numeric(8,2), -- or how ever many places you need
CONSTRAINT CK_Year CHECK (PayYear LIKE [1-2][0-9][0-9][0-9]) -- validate a 4 digit year
)
INSERT INTO NewPay
SELECT employeeID, dateHired, employeeName, 2010, Salary
FROM 2010salary
UNION ALL
SELECT employeeID, dateHired, employeeName, 2011, Salary
FROM 2011salary
gets us normalized to boot (not having a column for each year of salary)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 20, 2012 at 4:23 pm
show off 🙂
March 20, 2012 at 5:16 pm
Matthew Cushing (3/20/2012)
show off 🙂
why not show off a little. maby some newbie will search the forum (LOL) and find this reply and go wow thats cool, did not know i could do that.
(Its how i came up with it, well googled and found it but still.)
😛
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 20, 2012 at 5:42 pm
another good point 🙂
Thanks for finding that for me.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply