I have two tables like the following, using MS SQL Server 2000:
Table A:
ID Date
--- ------
A1 1999-01-08
A2 1999-03-02
===================
Table B:
ID Date Amt
--- ---------- ------
A1 1999-01-11 100
A1 1999-02-17 49
A1 1999-02-18 50
A2 1999-03-01 200
A2 1999-03-10 122
====================
Now I have do join the two so that I have 1 table that looks like this with and additional column, so that if the Date in table B is between the Date in Table A first record and second record, I will assign the same sequence number, and the last date will be between the Date in Table A last record and current system date:
ID Addl Date Amt
---- ------- -------------- ------
A1 1 1999-01-11 100
A1 1 1999-02-17 49
A1 1 1999-02-18 50
A2 2 1999-03-01 200
A2 2 1999-03-10 122
==========================
==
How do I do that? Is it possible to do it in one query?
Thanks
Start Free Trial
