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 |
|
RicoLX
Starting Member
3 Posts |
 Posted - 2007-11-30 : 02:41:52
|
| I have a table with fields group_id, Customer_current_rate and current_rate_date. My sql table contain 3 records where customer_id = 12 where the rate has been adjusted three times stamped with three unique current_rate_date entries. My boss wants to see what the current rate was for any given period. I tried a @date parameter to allow him to get the results but i keep pulling back the record within the group with the max date and not the most recent record where the max date is less than or = to the @date passed to the query. For example, if customer #12 have 3 entries with dates 1/1/07, 2/4/07 and 6/21/07 and i want to see the current_rate as of @date= 5/30/07 then i want to bring back the record with the 2/4/07 rate change date because its the most recent date closest my parameter date. Please help: select customer_id, current_rate, current_rate_date from customer_rates where customer_id = @customer_id and current_rate_date <=@dateRicoLX |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-30 : 02:56:16
|
| [code]Select customer_id, current_rate, current_rate_date from customer_rates where customer_id = @customer_id and current_rate_date = (Select max(current_rate_date) fromcustomer_rates where current_rate_date <= @date)[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
RicoLX
Starting Member
3 Posts |
Posted - 2007-11-30 : 09:49:46
|
| Thanks! this works!!!!RicoLX |
|
|
|
|
|
|
Get Max Date within Group of Records Using @Date