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 |
|
shotz1015
Starting Member
4 Posts |
 Posted - 2009-11-25 : 14:28:02
|
| I have two tables, one has a price based on a date, the other has transactions based on a date. I need to be able to select the price based on the most recent date that is older than the current transaction date. For example i have 2 price changes, one in january and one in february, I do a transaction in march. I want to select the price in february only. |
|
|
Wannabe67
Starting Member
14 Posts |
Posted - 2009-11-25 : 15:39:23
|
| You have to use the MAX function if you always want the latest date. I believe the example will help you.SELECT MyFields FROM MyTable WHERE MyDate = (SELECT TOP 1 MAX(MyDate) FROM MyPriceTable) |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-25 : 15:47:23
|
Something like this but you are not very clear with that minimum given info:selectt1.*,(select top 1 price from price_table t2 where theDate = (select max(theDate) from price_table where theDate < t1.theDate))as pricefrom trans_table t1
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
shotz1015
Starting Member
4 Posts |
Posted - 2009-11-25 : 16:14:30
|
| This may help a bit. Lets say the price is again changed after the transaction in March. When the transaction occurred in March, the customer was charged the prices from February. If the price is changed again lets say in April, I still want to calculate based on the prices from February and not the new prices from April. |
|
|
|
Wannabe67
Starting Member
14 Posts |
Posted - 2009-11-26 : 10:34:13
|
| From what it sounds like you are asking, you would use the transaction date from your trans table, like webfred showed. You may need to use <= in the where clause, but this would give you the row from the price table that has the date matching the transaction table for each customer. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-11-26 : 12:08:24
|
| If you want a final answer post the table(s) structure, some sample data and the desired result. That way there can be no confusion!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Selecting Dates