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 |
|
gwguy7
Starting Member
3 Posts |
 Posted - 2011-06-09 : 15:45:08
|
I have a list of data which looks like this:Date Rate Time06/01 3.25 03.00.00 pm06/01 3.24 01.00.00 pm06/01 3.23 11.00.00 am06/08 3.00 01.15.00 pm06/08 3.02 11.25.00 amI want to get the last updated rate for every day. So my sql result should look like this:06/01 3.25 03.00.00 pm06/08 3.00 01.15.00 pmAny help is appreciated!!!  |
|
|
Andrew Zwicker
Starting Member
11 Posts |
Posted - 2011-06-09 : 19:25:44
|
Hi, For this, I'm going to assume the date and time values above are both varchar fields. If they're actually datetime fields, I can provide a modified solution.Here's a create table statement and statements to insert the data:create table Prices (date varchar(10), rate float, [Time] varchar(20))insert into Prices values('06/01',3.25,'03.00.00 pm')insert into Prices values('06/01',3.24,'01.00.00 pm')insert into Prices values('06/01',3.23,'11.00.00 am')insert into Prices values('06/08',3.00,'01.15.00 pm')insert into Prices values('06/08',3.02,'11.25.00 am')The following query gets the requested result. It uses a derived table to determine the maximum time in each of the days [I need to convert the varchar to datetime to do the comparison]:select Prices.date, rate, [time] from Prices inner join( select date, max(cast(replace([time],'.', ':') as datetime)) as 'maxTime' From Prices group by date) MaxTimePerDay on MaxTimePerDay.date = Prices.date and maxTime = cast(replace([time],'.', ':') as datetime) If the date field needs to be converted to a datetime as well, one thing you can do is to use: cast(date + '/1900' as datetime). Substitute the correct year for 1900 if you know the year.Also, if the trailing zeros are needed in the rate, you can use the following in the select statement: cast(rate as decimal(10, 2))I hope this helps.Visit http://www.helpwithsql.com |
 |
|
|
|
|
|
|
|
Updated Data for each day