March 15, 2010 at 9:58 pm
Hey All,
Im loosing whatever lil bit of hair that I have in here as I just cant write this and the reports guy is out this whole week. Please see attachment for any clarification as that is far more legible.
Thanks,
Olaide
March 15, 2010 at 10:26 pm
I didn't test but this should work..
; WITH cteTableDetails AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY EmployeeID ORDER BY T1.StartDate DESC ) Row, T1.*
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.EmployeeID = T2.EmployeeID
AND ( T2.EnrollDate BETWEEN T1.StartDate AND T2.EndDate OR
T2.EndDate BETWEEN T1.StartDate AND T2.EndDate )
)
SELECT*
FROMcteTableDetails
WHERERow = 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 16, 2010 at 1:19 am
-------------------------------------------------------------------------------------------------Table1
Name EmployeeID Section Fare Amount StartDate Enddate
Kane, Charles677Advance111100.002010-03-10 00:00:00.0002010-04-30 00:00:00.000
Kane, Charles677Advance8100.002010-05-01 00:00:00.0009999-12-31 00:00:00.000
Delta, Tie 890Advance2223.002010-03-11 00:00:00.0009999-12-31 00:00:00.000
Delta, Tie 890Misc DedNULL32.002010-03-11 00:00:00.0009999-12-31 00:00:00.000
Table2
EmployeeID EnrollDate EnrollEnddate
6772010-04-13 00:00:00.0002010-04-19 00:00:00.000
8902010-03-13 00:00:00.0002010-04-19 00:00:00.000
In the above situation, I expect to see both rows, for Tie Delta as they belong two different Sections in the Fare Class. However for Charles Kane, I expect to see only the First row which has the Enrolldate & EnrollEnddate within the startdate & Enddates in Table1.
Please advice.
Im quite thankful for the help extended.
regards,
Olaide
March 16, 2010 at 1:42 am
Just add the column Section in the PARTITION BY Clause as shown below
ROW_NUMBER() OVER ( PARTITION BY EmployeeID, Section ORDER BY T1.StartDate DESC ) Row
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 16, 2010 at 1:50 am
Kingston Dhasian,
You just did it Pal. I cant be thankful enough for your help. Seriously ! I owe you Pal.
Btw, how do you solve this without any analytical functions ? If you can show me that, that would be twice as nice.
regards,
Olaide.
March 16, 2010 at 2:04 am
Glad i could help you out:-)
Look for the explanation of ROW_NUMBER in Books Online. You can also Google for some good examples on the same.
You can also play with the query to understand it better. Just comment the Where Clause in the query and try to understand the values of the column Row.
In simple words, the column Row contains as unique number for different combinations of EmployeeID and Section ordered by StartDate in a descending Order. The topmost row is the one we need.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 16, 2010 at 2:24 am
Respect your humbleness. Thanks again & I promise I will do my part in getting a better understanding of the Analytical Functions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply