Complex Select Statement for a Dispatch System

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Select Statement for a Dispatch System

Author

Topic

TorreyB
Starting Member

3 Posts

Posted - 2007-08-14 : 13:49:57

Say we have a table containing data like below:

PickupTime | ContactName | FromStreet
--------------------------------------
8/9/2007 12:00:00 PM | Amy Smith | 44 Pearl St.
8/9/2007 3:00:00 PM | Amy Smith | 3011 Navarre Ave.
8/9/2007 3:00:00 PM | Amy Smith | 2341 First Ave.

The actual table contains more data than this, but that should be enough for some of the experts here to help me with what I'm trying to figure out. What I'd like to happen is that my sql statement return all records, except the PickupTimes that repeat, and if the time does repeat just grab the first one. I'd end up with an output like the below:

8/9/2007 12:00:00 PM | Amy Smith | 44 Pearl St.
8/9/2007 3:00:00 PM | Amy Smith | 3011 Navarre Ave.

It is possible that Amy Smith could have multiple transportations and when her return time is not known the PickupTime from the previous transport is repeated. The client I'm programming this interface for whats the known times to show up in the dispatch window, and this is where I'm having an issue with these repeated times.

Hopefully what I have explained is not too confusing, but if it is just ask any question that would help you to help me.

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 14:01:25

SELECT PickupTime, ContactName, MIN(FromStreet)
FROM YourTable
GROUP BY PickupTime, ContactName

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TorreyB
Starting Member

3 Posts

Posted - 2007-08-14 : 14:18:54

quote:
Originally posted by dinakar

SELECT PickupTime, ContactName, MIN(FromStreet)
FROM YourTable
GROUP BY PickupTime, ContactName

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

The example worked great, and I test it out in DbaMgr2k, but when I tried to recreate it with the statement I had previous in Visual Studio I didn't have luck. Here's the statement I used in VS:

SELECT     WaitTime, DriverName, VehicleNumber, CONVERT(varchar, PickupTime, 108) AS PickupTime, ContactName AS [Contact Name], ContactPhone, 
                      MIN(FromStreet) AS FromStreet, ContactCity, Status, TimeCreated, ReservationCode
FROM         Dispatch
WHERE     (DATEDIFF(DAY, PickupDate, GETDATE()) = 0) AND (Status IN ('MULTIREADY', 'READY', 'SCHEDULED'))
GROUP BY WaitTime, DriverName, VehicleNumber, PickupTime, ContactName, ContactPhone, ContactCity, Status, TimeCreated, ReservationCode
ORDER BY WaitTime DESC

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 14:41:37

Can you explain what you mean when you say it didnt work? did you get any errors?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TorreyB
Starting Member

3 Posts

Posted - 2007-08-14 : 14:47:42

Using the sql statement:

SELECT     WaitTime, DriverName, VehicleNumber, CONVERT(varchar, PickupTime, 108) AS PickupTime, ContactName AS [Contact Name], ContactPhone, 
                      MIN(FromStreet) AS FromStreet, ContactCity, Status, TimeCreated, ReservationCode
FROM         Dispatch
WHERE     (DATEDIFF(DAY, PickupDate, GETDATE()) = 0) AND (Status IN ('MULTIREADY', 'READY', 'SCHEDULED'))
GROUP BY WaitTime, DriverName, VehicleNumber, PickupTime, ContactName, ContactPhone, ContactCity, Status, TimeCreated, ReservationCode
ORDER BY WaitTime DESC

Returned all three items from the example in my first post, instead of the desired results I also mentioned in the first post. I know it's something I'm doing wrong in the way I translated your example over to what I'm working on. I just haven't figured out what is wrong with the statement I used.

[edit]

I took the statement piece by piece and came up with the answer. It turned out the city was causing the problem. Here's the final solution that worked:

SELECT     WaitTime, DriverName, VehicleNumber, CONVERT(varchar, PickupTime, 108) AS PickupTime, ContactName AS [Contact Name], ContactPhone, 
                      MIN(FromStreet) AS FromStreet, MIN(ContactCity) AS ContactCity, MIN(Status), TimeCreated, ReservationCode
FROM         Dispatch
WHERE     (DATEDIFF(DAY, PickupDate, GETDATE()) = 0) AND (Status IN ('MULTIREADY', 'READY', 'SCHEDULED'))
GROUP BY WaitTime, DriverName, VehicleNumber, PickupTime, ContactName, ContactPhone, TimeCreated, ReservationCode
ORDER BY WaitTime DESC

Thanks a lot dinakar!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-14 : 16:22:43

Welcome.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources