Apologize in advance if this is not posted in the correct format. I'm fairly new to asp and boy is it fun struggling..... I mean learning!

I am using asp.net 2.0 with a sql database using sql server 2005 and have a table with 4 columns.

as an example


ID(key) TubeDestination TimeSent ContainsItems
1 88 20:00 yes
2 88 21:00 no
3 88 22:00 yes
4 100 20:00 yes
5 100 21:00 yes
6 100 22:00 yes
7 150 23:00 yes

I'm trying to make a sql statment to return into a gridview the most recent TubeDestination and TimeSent based on the TimeSent for each tubestation in the table but only show the TubeDestination and TimeSent if the TimeSent has occured more than on hour ago from the current time. As shown above there can be multiple deliveries to a tubedestination in the log but each will have its own time.


For the above table if it was currently 23:30 I would hope for the below return:

TubeDestination TimeSent
88 22:00
100 22:00


I've spent days on this and would love some help with this problem and if anyone had a nice wrox text suggestion for sql/databases. I like the wrox asp.net 2.0 proffesional book I have but it leaves me with a lot of questions about sql statements. Thanks in advance. Dan

Hi there..

your problem is totally on the sql side???

This look like you need a group by TubeDestination and a max for the TimeSent , and then compare that max with the time you need..

Sorry I can recall it by heart, but if you need more help just ask...

__________________
HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the proof.
================================================== =========

Thank you so much for helping me think through this!

As I mentioned I'm a little new to sql and was having a hard time telling if I was using the wrong functions or bad syntax (although it was most likely both) and totally overthinking it.

I used the following and it seems to be working. I'll have to test it against a full dataset but looks good!

SELECT TubeDestination, MAX(TimeSent)
FROM Table1
WHERE TimeSent < dateadd(hh,-1,getdate())
GROUP BY TubeDestination

I'm still not quite sure how to get the TimeSent to display as well for the selected TubeDestination but I think the hard part is done. Thanks Dan

Doh "AS TimeSent" of course.

Thank you again for your help!