ROW NUMBER LINKING based on date

Question

Post reply

ROW NUMBER LINKING based on date

Johnny D

Ten Centuries

Points: 1030

July 11, 2017 at 7:00 am

#402268

Hi,

Thanks in advance for anyone who helps but I thought I was quite a SQL expert but this issue has baffled me for a few days now and I can't seem to work out the best way to achieve a result.
I have two tables:-

Table A					Table B
EMPNO	OCODE	STARTDATE	ENDDATE	ROWNUM	EMPNO	CODE	STARTDATE	ROWNUM
1012253	1027398	09-Apr-02	31-Mar-05	1	1012253	A	09-Apr-02	1
1012253	1031383	01-Apr-05	14-Jan-09	2	1012253	B	01-Apr-05	2
1012253	1027394	15-Jan-09	24-Jan-16	3	1012253	F	15-Jan-09	3
1012253	1026425	25-Jan-16		4	1012253	S	01-Jan-12	4
					1012253	V	25-Jan-16	5

I have used the row number in order to link Table A with Table B. And the link is fine for most scenarios. However i have noticed sometimes Table B has more rows then Table A and in the above scenario I would need to match Table B row 5 with table A row 4, Table B rownum 4 with Table A rownum 3 and so on based on the date.
The problem I have is Table B could quite easily have 6 rows and Table A could have 4 rows and in this case I would need to link rownum 6 with rownum 4, rownum 5 with rownum 3 and so on.
As you can see this is quite a tricky scenario and am not sure how best to tackle this.
Thanks

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

If I'm correct, you effectively want to match the Maximum RowNum in Table A to the Maximum RowNum in Table B, and then work your way down, correct? Using a CTE, you could create a new Row Number to join on. Something like:
WITH TableA AS ( SELECT EMPNO, OCODE,STARTDATE, ENDDATE,ROWNUM, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS JOINID FROM [Table A]) , TableB AS ( SELECT EMPNO, CODE,STARTDATE,ROWNUM, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS JOINID FROM [Table B]) SELECT * FROM TableA A JOIN TableB B ON A.JOINID = B.JOINID;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Johnny D Ten Centuries Points: 1030 More actions · Answer 2

Hi Thom,

Thanks for your suggestion. Not sure if I've understood you correctly but that does not work as sometimes table B will have 6 rows and Table A will have 3 so rownum 6 will need to link with rown 3. Somehow the sql needs to say link the top 3 from Table A with the top 3 from Table B regardless of the row number. If that makes sense.

Thom A SSC Guru Points: 99130 More actions · Answer 3

The above would work. If [Table A] has 3 rows, and [Table B] has 6, then the links would go (A -> B):
1 -> 4
2 -> 5
3 -> 6

From what you've posted, that seems correct to me. If not, provide DDL and DLM, along with expected results.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Johnny D Ten Centuries Points: 1030 More actions · Answer 4

Hi Thom,
Excellent. After a second look it certainly does work. Just what I needed. Also only needed a slight change to my current script.
Thanks for your help.