grouping addresses by hour - help!

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

SQL Server Development (2000)

grouping addresses by hour - help!

Author

Topic

tacec
Starting Member

8 Posts

Posted - 2008-09-10 : 17:02:37

I can't seem to wrap my little brain around this one.

I have a view which lists trips, including address and estimated hour the trip will be done. I'm trying to group the addresses together in each estimated hour. ie. if there are > 1 of an address in each esthour, display it. If there's not >

1 in the esthour, don't display.


SELECT BookingID, ClientName, Route, Activity, Activityvalue,EstHour,
EstTime, Address, City, SchedStatus 
FROM dbo.View_Manifest 
WHERE LDate = 20080910 
AND SchedStatusValue IN(0,1)
AND ActivityValue = 1
AND ccsecgroup = 'ct-mts'
AND Address IN 
        (SELECT Address FROM dbo.View_Manifest 
            WHERE LDate = 20080910 
            AND ActivityValue = 1 
            AND CCSecGroup = 'ct-mts' 
            GROUP BY Address  
            HAVING COUNT(Address) > 1)
ORDER BY EstHour, Address

Unfortunately, it's still showing me groups of one address inside of an esthour, and I'm sure I missed something. Anyone have any ideas? Any help would be much appreciated!

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 17:23:16

Sample data?

E 12°55'05.63"
N 56°04'39.26"

tacec
Starting Member

8 Posts

Posted - 2008-09-11 : 12:47:45

I'm sorry, i'm such a newbie to this...

Not sure how i can give sample data in here....

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-11 : 12:56:50

How to post a question for a database:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

hey

tacec
Starting Member

8 Posts

Posted - 2008-09-11 : 17:24:53

Hope this helps:
Table column structure:


CREATE TABLE Manifest
(
ClientName char(50),
Route varchar(20), 
EstHour varchar(2), 
EstTime char(4), 
Address varchar(100), 
City varchar(254)
)

(I stripped some of the unnecessary items from the original query):


SELECT ClientName, Route, EstHour, EstTime, Address, City
FROM dbo.Manifest 
WHERE LDate = 20080910
AND ActivityValue = 1 
AND Address IN 
        (SELECT Address FROM dbo.View_Manifest 
            WHERE LDate = 20080910 
            GROUP BY Address  
            HAVING COUNT(Address) > 1)
ORDER BY EstHour, Address

Expected results would be a little something like this:


JOE SCHMOE	555	08	0835	555 9TH AVE S	SEATTLE
JANE DOE	444	08	0840	555 9TH AVE S	SEATTLE
SPONGEBOB	546	09	0952	1234 5TH AVE	SEATAC
DAFFYDUCK	658	09	0955	1234 5TH AVE	SEATAC
SPIDERMAN	956	10	1020	555 9TH AVE S	SEATTLE
MICKEYM	        321	10	1049	555 9TH AVE S   SEATTLE
MINNYM	        123	10	1055	555 9TH AVE S	SEATTLE

Did I provide enought info?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-13 : 06:30:18

Hi...I don't Found any problem In your code expect that keep That 1 in single cots like '1'..so overall code is..like that,,,,

SELECT BookingID, ClientName, Route, Activity, Activityvalue,EstHour,
EstTime, Address, City, SchedStatus
FROM dbo.View_Manifest
WHERE LDate = 20080910
AND SchedStatusValue IN(0,1)
AND ActivityValue = 1
AND ccsecgroup = 'ct-mts'
AND Address IN
(SELECT Address FROM dbo.View_Manifest
WHERE LDate = 20080910
AND ActivityValue = 1
AND CCSecGroup = 'ct-mts'
GROUP BY Address
HAVING COUNT(Address) > '1')
ORDER BY EstHour, Address

and u can also do it like.....

,------------------- IN

(SELECT count(address) as Times,Address FROM dbo.View_Manifest
WHERE LDate = 20080910
AND ActivityValue = 1
AND CCSecGroup = 'ct-mts'
GROUP BY Address
HAVING COUNT(Address) > '1')
ORDER BY EstHour, Address

tacec
Starting Member

8 Posts

Posted - 2008-09-14 : 00:44:02

Thanks everyone for your help. I did get it after all.

I had to create my own field concatonating a few other fields, including esthour in order to group on it right.

THanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources