UnionAll help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

UnionAll help

Author

Topic

james.burger
Starting Member

4 Posts

Posted - 2010-02-26 : 10:50:13

I have two tables, tbl_employees and tbl_employees_retired that I unionall together in a view (Qry_AllEmployees). I work for a local government entity, so retired employees can return to work. HR will use their same employee id, so I have individuals in both tables (only their status coding is different). When I union all in my above view, I get two records but only want one.

Any suggestions of how I can handle this? Thanks!

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 10:55:10

You could use UNION - which would remove (EXACT) duplicates.

But to do that UNION has to sort, and de-dupe, so my preference would be


SELECT ...
FROM tbl_employees
WHERE ...
UNION ALL
SELECT ...
FROM tbl_employees_retired AS R
WHERE NOT EXISTS(SELECT * FROM tbl_employees AS E WHERE E.EmployeeID = R.EmployeeID)
      AND ...

so that the second SELECT will not include any people who are in the first.

(You will need some "common" data to match them up, might be social security number, I've indicated that with "EmployeeID")

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-26 : 11:10:19

This might be a good case for some sample data and expected output:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Does EVERYTHING match except Status coding? And when that is different which code do you want?

james.burger
Starting Member

4 Posts

Posted - 2010-02-26 : 14:29:40

Yes, the columns are exactly the same, except for the status. An Active employee will have a status with "A", on leave "L", retired "R", terminated "T", and a "Z" code. If they have an active "A" record, I would prefer that one. Else, a "R" or "Z".

There is also a twist, the retired table could have you listed twice, as a status "R" and as a status "Z"(all within the tbl_employee_retired table). If you returned to work, you also have an "A" status record in the tbl_employee table. So I have some users with three records.

Unfortunately, I have no control on the data entered into our ERP system, nor the HR department.

Thanks again for your assistance!

quote:
Originally posted by Lamprey

This might be a good case for some sample data and expected output:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Does EVERYTHING match except Status coding? And when that is different which code do you want?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-26 : 15:26:32

Without DDL and DML it makes it hard to play around with your data, but I put together a partial suggestion using UNION. I suspect that you might be able to get some better performance using a LEFT OUTER JOIN. But, here is something that might help:

SELECT
	-- All Columns except RowNum
	*
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER 
			(
				PARTITION BY EmployeeID 
				ORDER BY 
					CASE
						WHEN Status = 'A' THEN 1
						WHEN Status = 'R' THEN 2
						WHEN Status = 'Z' THEN 3
					END
			) AS RowNum
		FROM
			(
				SELECT *
				FROM tbl_employees

				UNION ALL

				SELECT *
				FROM tbl_employees_retired
			) AS A
		WHERE
			RowNum = 1
	) AS T

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-26 : 15:29:33

Or if you don't care witch retired row is returned when there is not a current row this might work:

SELECT
	-- All Columns except RowNum
	*
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Ord) AS RowNum
		FROM
			(
				SELECT *, 1 AS Ord
				FROM tbl_employees

				UNION ALL

				SELECT *, 2 AS Ord
				FROM tbl_employees_retired
			) AS A
		WHERE
			RowNum = 1
	) AS T

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 15:40:28

Looks very complicates ... what's wrong with my NOT EXISTS example, earlier? or have I missed a requirement??

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-26 : 15:47:59

I'm a little confused about how the statuses fit together. But, NOT EXISTS might work just fine. But, I'm a little curious about the performance aspect, that's why I suggested to try some different options including a left join. I assume the data set is not very large, but depending on the data size one option might perform significantly better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources