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.
| Author |
Topic |
|
itaybarda
Starting Member
12 Posts |
 Posted - 2007-06-27 : 11:06:53
|
| HELLO.this is how my table looks:ITEM| VENDOR | REQ DATE | QTY |--------------------------------555 | RRR ||01/2009 | 10 |--------------------------------555 | VVV ||01/2008 | 5 |-------------------------------555 | VVV ||09/2007 | 9 |-------------------------------555 | RRR |01/2001 | 7 |i would like to know, for each item, from which vendor we should expect to get it next, so for item 555 it will be "VVV" vendor, because he will bring it on 09/2007.BUT!!! if there are no future arrivals for this item, i would like to see the last vendor we got it from, which is "RRR", came on 01/2001.thank you for your help. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-27 : 11:28:35
|
| select t1.item, vendor = coalesce(t2.vendor, t2.vendor), reqdate = coalesce(t2.reqdate, t2.reqdate)from(select distinct item from tbl) t1left join (select * from tbl t1 where reqdate = (select min(reqdate) from tbl t2 where t2.item = t1.item and t2.reqdate > getdate()) t2on t1.item = t2.itemleft join (select * from tbl t1 where reqdate = (select max(reqdate) from tbl t2 where t2.item = t1.item and t2.reqdate < getdate()) t3on t1.item = t3.item==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
itaybarda
Starting Member
12 Posts |
Posted - 2007-06-27 : 11:44:20
|
| working very good!!!thank you for your help. |
|
|
|
|
|
|
self join (i think)