Login    Forum    Search    FAQ   Blog

Board index » Server Side Development » SQL




 Page 1 of 1 [ 6 posts ] 



Author Message
 Post subject: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:05 pm 
Offline

Joined: Sat Feb 21, 2009 11:48 am
Posts: 22
I couldnot seem 2 wrap ma lil brain round this one.

I m havin a view which lists the trips, includin the address nd then
estimated hour da trip would be done. I' m tryin 2 group da addresses
together in each estimated hour. ie. if der a are > 1 of an addres in each
esthour, juss display it. If there was not > 1 in da esthour, dunt
display.

Code:
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 was still showin me groups of 1 address inside of an
esthour, and I m pretty sure I missd sumthi . Anyone have any kinda ideas?
Any help would be much appreciated!

Thanks

_________________
A computer once beat me at chess, but it was no match for me at kick boxing.


Top 
 Post subject: Re: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:05 pm 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Sample data?


Top 
 Post subject: Re: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:05 pm 
Offline

Joined: Sat Feb 21, 2009 11:48 am
Posts: 22
Im really sorry!!! i'm such a newbie to this...

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

_________________
A computer once beat me at chess, but it was no match for me at kick boxing.


Top 
 Post subject: Re: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:06 pm 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Hope dis helps:
Table column structure:

Code:
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):

Code:

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 lil sumthin like this:


Code:
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 enough information ?


Top 
 Post subject: Re: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:06 pm 
Offline

Joined: Sat Feb 21, 2009 1:08 pm
Posts: 549
Hi...I didnot Found any type of issue In ur code expect
dat really keep That 1 in single cots like '1'..so overall code was ..
like that,,,,

Code:
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


Top 
 Post subject: Re: groupin the addresses by hour
 Post Posted: Sat Nov 21, 2009 2:06 pm 
Offline

Joined: Sat Feb 21, 2009 11:48 am
Posts: 22
Thanks everyone for ur help. I did get it after all.

I had 2 create ma own field concatonatin a few other fields, includin the
esthour in order t2 group on it right.

THanks again!

_________________
A computer once beat me at chess, but it was no match for me at kick boxing.


Top 
Display posts from previous:  Sort by  
 
 Page 1 of 1 [ 6 posts ] 




Board index » Server Side Development » SQL


Who is online

Users browsing this forum: No registered users and 1 guest

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron