Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 4th, 2011, 10:56 PM
Registered User
 
Join Date: Apr 2011
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default Trouble returning most recent transaction with sql statment

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
  #2 (permalink)  
Old April 5th, 2011, 02:30 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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.
================================================== =========
The Following User Says Thank You to gbianchi For This Useful Post:
Dumb Dan (April 5th, 2011)
  #3 (permalink)  
Old April 5th, 2011, 10:11 PM
Registered User
 
Join Date: Apr 2011
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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

Last edited by Dumb Dan; April 5th, 2011 at 10:23 PM..
  #4 (permalink)  
Old April 5th, 2011, 10:25 PM
Registered User
 
Join Date: Apr 2011
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Doh "AS TimeSent" of course.

Thank you again for your help!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hard SQL languge statment Questioin Mangore SQL Language 8 October 27th, 2008 03:01 AM
VB2005 trouble returning structure from function MikeCS Visual Basic 2005 Basics 1 November 29th, 2006 07:45 PM
Trouble with returning parameter values EricJ VB.NET 0 July 5th, 2005 08:58 AM
SQL Statment btpoole VB Databases Basics 1 February 18th, 2004 01:03 PM
SQL statment arshad mahmood VB Databases Basics 1 July 1st, 2003 02:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.