November 8, 2008 at 1:39 pm
I have a problem and it's doing my head in, mainly because I know the answer will be so simple. I just can't see it.
We've got a couple of systems that hold leave (aka vacation or holiday) information. However, it has been discovered that there are some discrepancies between them. For example, leave may be recorded in one system but not in the other. Or leave may have been recorded in one system as 2-3 November but ended up in the other system as 3-4 November.
So I have some information I have imported from two spreadsheets into two tables. The tables are pretty much identical. The basic structure is:
CREATE TABLE hrleave
(
EmplID nvarchar(6),
Surname nvarchar(50),
FirstName nvarchar(50),
LeaveDate datetime
);
CREATE TABLE misleave
(
EmplID nvarchar(6),
Surname nvarchar(50),
FirstName nvarchar(50),
LeaveDate datetime
);
The sample data I am providing (data changed to protect the innocent) has an all numerical EmplID, but there are a lot of records that have an alphanumeric EmplID (in case you are wondering about the EmplID being nvarchar). It's in tab-delimited format. One table has 31 rows, the other has 33 (due to the discrepancies between the two systems).
The output I am trying to get is a list of leave dates from each table for each employee, with dates matching up where they are present in both tables and nulls for the other side when they don't. Sounds like a simple OUTER JOIN right? Yeah, not working so much (using SQL 2005 Developer Edition, SP2, CU5).
So what I am wanting is output like the following:
EmplIDSurnameFirst NameHR LeaveMIS Leave
000672BloggsJoe2006-08-212006-08-21
000672BloggsJoe2006-08-222006-08-22
000672BloggsJoe2006-09-292006-09-29
000672BloggsJoeNULL2006-12-13
000672BloggsJoe2007-01-152007-01-15
000672BloggsJoe2008-07-29NULL
Everything I have tried so far either gets me 26 rows, 31 rows or 1023 rows. But still not returning what I want. Most notably, the 2006-12-13 date just disappears and cannot be retrieved.
Any help appreciated.
Thanks
Scott.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 8, 2008 at 3:09 pm
Please refer to the link in my signature for a better way to provide sample data in future posts. Try this:
[font="Courier New"]
SELECT DISTINCT
COALESCE(H1.EmplID,M.EmplID) EmplID,
COALESCE(H1.SurName,M.SurName) SurName,
COALESCE(H1.FirstName,M.FirstName) FirstName,
H1.LeaveDate HRLeave,
M.LeaveDate Misleave
FROM ( SELECT DISTINCT HI.EmplID, HI.LeaveDate AllLeave
FROM HrLeave HI
UNION ALL
SELECT DISTINCT MI.EmplID, MI.LeaveDate AllLeave
FROM MisLeave MI) H
LEFT JOIN misleave M ON H.EmplID = M.EmplID AND H.AllLeave = M.LeaveDate
LEFT JOIN hrleave H1 ON H.EmplID = H1.EmplID AND H.AllLeave = H1.LeaveDate
WHERE COALESCE(H1.LeaveDate, M.LeaveDate) IS NOT NULL
ORDER BY H1.LeaveDate, M.LeaveDate
[/font]
It's kinda sloppy due to a number of revisions, but it should work. I haven't quite figured out why the december 2006 date disappears with a lot of other(simpler) methods either.
November 8, 2008 at 7:28 pm
Have you tried a FULL OUTER JOIN ?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 8, 2008 at 8:44 pm
Ugh. That was actually the first thing I tried... I just didn't join on date in addition to emplID and it wasn't giving me what I wanted. Barry is right, use a full outer join on EmplID and LeaveDate and you get the same results as the above.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply