Busy Hour Query (Tricky) - SQL Server Forums

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)

Busy Hour Query (Tricky)

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2006-02-05 : 04:38:28

Hello,
I have the following table Traffic with the Street, CarsPassed, DateTime, Accidents.
For each Stree there are 24 rows corresponding to the day's hours. I want write a query that will give me the busy hour cars passed along with the corresponding Accidents. The busy hour is defined as the hour with max cars passed during the day.
Example
Street CarsPassed Accidents DateTime
------ ---------- ---------- ---------
Street1 23 6 05-02-2006 00:00:00
Street1 55 1 05-02-2006 01:00:00
Street1 47 4 05-02-2006 02:00:00
Street1 19 9 05-02-2006 03:00:00
Street1 12 0 05-02-2006 04:00:00
Street2 11 6 05-02-2006 00:00:00
Street2 20 1 05-02-2006 01:00:00
Street2 66 0 05-02-2006 02:00:00
Street2 56 2 05-02-2006 03:00:00
Street2 44 2 05-02-2006 04:00:00
and so on...
I want to get the following
Street1 55 1 05-02-2006 01:00:00
Street2 66 0 05-02-2006 02:00:00
and so on..
How can I do that in one select statement?

Thanks for your help in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-05 : 05:08:23

[code]select a.*
from traffic a inner join
(
select Street, CarsPassed
from traffic t
where CarsPassed = (select max(CarsPassed) from traffic x where x.Street = t.Street)
group by Street, CarsPassed
) as b
on a.Street = b.Street
and a.CarsPassed = b.CarsPassed[/code]

----------------------------------
'KH'

BigRetina
Posting Yak Master

144 Posts

Posted - 2006-02-05 : 05:57:58

Thanks ALOT...BUT..
This query will only bring one record for each street for the whole period of time. I need a record per day. Let me enhance my example for street1:
Street CarsPassed Accidents DateTime
------ ---------- ---------- ---------
Street1 23 6 05-02-2006 00:00:00
Street1 55 1 05-02-2006 01:00:00
Street1 47 4 05-02-2006 02:00:00
Street1 19 9 05-02-2006 03:00:00
Street1 12 0 05-02-2006 04:00:00
Street1 11 6 06-02-2006 00:00:00
Street1 20 1 06-02-2006 01:00:00
Street1 66 0 06-02-2006 02:00:00
Street1 56 2 06-02-2006 03:00:00
Street1 44 2 06-02-2006 04:00:00

The query should return two records(the busy hour for each day):
Street1 55 1 05-02-2006 01:00:00
and
Street1 66 0 06-02-2006 02:00:00
and so on for every other street...i have a period of about one week. So i need a record per day representing the busy hour for that street in that day.

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-05 : 07:37:24

Like this ?

select	a.*
from	traffic a inner join
(
	select 	[DateTime], max(CarsPassed) as maxCarsPassed
	from	traffic t
	group by [DateTime]
) as b
on	a.CarsPassed	= b.maxCarsPassed
and	a.[DateTime]	= b.[DateTime]
order by a.[DateTime], a.Street

----------------------------------
'KH'

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 08:06:55

[code]select a.*
from traffic a
inner join
( select Street, convert(char(8),DateTime,112) as TheDay, max(CarsPassed) as CarsPassed
from traffic
group by Street, convert(char(8),DateTime,112)) t
on a.Street = t.Street
and convert(char(8),a.DateTime,112) = t.TheDay
and a.CarsPassed = t.CarsPassed[/code]

rockmoose

BigRetina
Posting Yak Master

144 Posts

Posted - 2006-02-05 : 10:29:46

rockmoose,
thanks alot...it works fine.
khtan,
thanks but i think it is missing the Street column in the grouping and join condition. It brought only the busiest street for each hour...not the busy hour for each street...however, thanks again

AbuShreek
Starting Member

6 Posts

Posted - 2006-05-14 : 08:03:22

Hello Again,

What if the traffic was EQUAL in two hours of the same day for the same street? I will get two BUSY HOUR records for that street that day and I only need one. How can I do that?

Thanks In Advance.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-14 : 08:42:03

Is this BigRetina asking, with a new name?

In the case of a day with equal traffic for more than one hour.
Which hour should be returned as the "BUSY HOUR" ?

rockmoose

AbuShreek
Starting Member

6 Posts

Posted - 2006-05-14 : 09:03:13

Yes this is BigRetina :-)

I didnt know how to change my nickname so I created a new one!

Any HOUR would be OK if there were two busy hours!..I THINK!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-14 : 09:37:04

Adding a killing subquery in the where clause...

select	a.*
from	traffic a
	inner join
	(	select	Street, convert(char(8),DateTime,112) as TheDay, max(CarsPassed) as CarsPassed
		from	traffic
		group by Street, convert(char(8),DateTime,112)) t
	on a.Street = t.Street
	and convert(char(8),a.DateTime,112) = t.TheDay
	and a.CarsPassed = t.CarsPassed
	and a.[DateTime] = (	select	min([DateTime])
				from	traffic t2
				where	a.Street = t2.Street
					and convert(char(8),a.[DateTime],112) = convert(char(8),t2.[DateTime],112)
					and a.CarsPassed = t2.CarsPassed )

You can try it out, and see how it works.
It takes the "first busy hour".

rockmoose

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-15 : 07:27:14

Hi all,

I think I've come up with a simpler, more efficient way. I'm not sure though, so see what you think.

Basically, I figured the rows we need for each day are the ones which give the minimum "DateTime - 'CarsPassed' days".

--data
set dateformat dmy
declare @t table (Street varchar(10), CarsPassed int, Accidents int, DateTime datetime)
insert @t
          select 'Street1', 23, 6, '05-02-2006 00:00:00'
union all select 'Street1', 55, 1, '05-02-2006 01:00:00'
union all select 'Street1', 47, 4, '05-02-2006 02:00:00'
union all select 'Street1', 19, 9, '05-02-2006 03:00:00'
union all select 'Street1', 12, 0, '05-02-2006 04:00:00'
union all select 'Street1', 11, 6, '06-02-2006 00:00:00'
union all select 'Street1', 20, 1, '06-02-2006 01:00:00'
union all select 'Street1', 66, 0, '06-02-2006 02:00:00'
union all select 'Street1', 56, 2, '06-02-2006 03:00:00'
union all select 'Street1', 44, 2, '06-02-2006 04:00:00'
union all select 'Street1', 66, 3, '06-02-2006 05:00:00'

--calculation
select * from @t a where DateTime = (
    select dateadd(d, max(CarsPassed), min(dateadd(d, -CarsPassed, DateTime))) from @t
    where Street = a.Street and datediff(d, 0, DateTime) = datediff(d, 0, a.DateTime)
)

--results
Street     CarsPassed  Accidents   DateTime                                               
---------- ----------- ----------- ------------------------------------------------------ 
Street1    55          1           2006-02-05 01:00:00.000
Street1    66          0           2006-02-06 02:00:00.000

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-15 : 08:17:44

quote:
Originally posted by RyanRandall

Hi all,

I think I've come up with a simpler, more efficient way. I'm not sure though, so see what you think.

Basically, I figured the rows we need for each day are the ones which give the minimum "DateTime - 'CarsPassed' days".

Quite interesting approach! Good one!
I don't know about the efficiency.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources