using MAX - 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)

using MAX

Author

Topic

wireless
Starting Member

8 Posts

Posted - 2003-04-17 : 15:53:57

How would I use the max function to only return the maximum hour out of a period of time? My data is hourly and I only want to return one hour from the whole day using a criterion.

For example, I only want the hour where MOUs are at their maximum out of 24 hours. The way this statement is written it returns every hour of the day.

SELECT [Date], SwitchName, MAX(MOUs) AS MaxMous, TotalAtt
FROM DataLucent
WHERE ([Date] >= '4/16/03') AND ([Date] < '4/17/03')
GROUP BY [Date], SwitchName, TotalAtt

The output should be something like:


Date                MaxMous    TotalAtt
4/16/03 5:00 PM      3456        8765

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 16:45:29

Not really sure what your asking, but the following gets the Max Datetime for a day:



USE Northwind
GO
 
CREATE TABLE Table1 (Col1 varchar(5), col2 int, col3 datetime, userId varchar(8))
GO

DECLARE @x Int
SELECT @x = 0
WHILE @x < 1000 BEGIN
INSERT INTO Table1 (Col1, col2, col3, UserId)
SELECT 'AAA', 1, Convert(datetime,'2001-01-01'), 'x002548' UNION ALL
SELECT 'AAA', 1, Convert(datetime,'2002-01-01'), 'x002549' UNION ALL 
SELECT 'AAA', 2, Convert(datetime,'2003-01-01'), 'x002548' UNION ALL
SELECT 'AAA', 2, Convert(datetime,'2004-01-01'), 'x002549' UNION ALL 
SELECT 'AAB', 1, Convert(datetime,'2005-01-01'), 'x002548' UNION ALL
SELECT 'AAB', 3, Convert(datetime,'2006-01-01'), 'x002549' UNION ALL 
SELECT 'AAC', 2, Convert(datetime,'2007-01-01'), 'x002548' UNION ALL
SELECT 'AAD', 2, Convert(datetime,'2008-01-01'), 'x002549' 
SELECT @x = @x +1
END
GO


CREATE INDEX IX1 ON Table1 (col3)
GO

UPDATE STATISTICS Table1
GO

INSERT INTO Table1 (Col1, col2, col3, UserId)
SELECT 'AAA', 1, Convert(datetime,'2001-01-01 12:00:00'), 'x002548'

SELECT Max(col3) FROM Table1 Where convert(varchar(10),col3,101) = '01/01/2001'

DROP TABLE Table1

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-17 : 17:01:18

This should be close to what you need:

SELECT [Date], SwitchName, MOUs, TotalAtt
FROM DataLucent WHERE
[Date] BETWEEN '4/16/03' AND '4/17/03'
AND MOUs =
(SELECT MAX(MOUs) FROM DataLucent
WHERE [Date] BETWEEN '4/16/03' AND '4/17/03')

This will return the hour with the maximum MOUs (with ties)

OS

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-17 : 17:09:24

I guess is something like this:

SELECT [Date], SwitchName, MOUs, TotalAtt
FROM DataLucent b1
WHERE ([Date] >= '4/16/03') AND ([Date] < '4/17/03') AND
( MoUs=
( SELECT MAX(MOUs) AS MaxMous
FROM DataLucent
WHERE [Date]=b1.[Date]
GROUP BY [Date] ))

wireless
Starting Member

8 Posts

Posted - 2003-04-18 : 13:58:34

Thanks, both techniques above work well.

A couple more questions:

1) How would I use the form of these queries to return the max hour per day over multiple days?

For example, if the date range was selected for a period over several days, then there would be an hour from each day that contained Max MOUs not just a single busiest hour out of all the days.


date                   MOUs
4/15/03  9:00 pm       2000
4/16/03 11:00 pm       2300
4/17/03  4:00 pm       2200

.
.

2) next, and I think this may be difficult. I have different entities and would like to aggregate the MOUs (minutes of use) for each hour of the day and then determine the busiest hour. I'd like to do something like MAX(SUM(MOUs)) but sql complains about having an aggregate function inside another aggregate function.

Something like this:


SELECT [Date],  MOUs
FROM DataLucent WHERE 
[Date] BETWEEN '4/16/03' AND '4/17/03' 
AND MOUs = 
(SELECT MAX(SUM(MOUs)) FROM DataLucent 
WHERE [Date] BETWEEN '4/16/03' AND '4/17/03' and (switch = 1 OR switch = 2))

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-19 : 02:53:34

1) This is very similar to what shifis suggested in the previous post, except that you need to convert the date into a date-only format.

SELECT [Date], MaxMOUs
FROM DataLucent d WHERE
[Date] BETWEEN '4/15/03' AND '4/18/03'
AND MOUs =
(SELECT MAX(MOUs) AS MaxMOUs FROM DataLucent i
WHERE CONVERT(varchar, i.[Date], 101) = CONVERT(varchar, d.[Date], 101))

2) I dont get it, arent the MOUs stored as an aggregate per hour? then why do would you need SUM(MOUs)? Anyway that can be accomplished with derived tables like:

SELECT a.blah1, a.MAX(theSumOfQty)
FROM
(SELECT blah1, SUM(Qty) as theSumOfQty
FROM table1 GROUP BY blah1) a
GROUP BY a.blah1

Edit: Added GROUP BY (duh)

OS

Edited by - mohdowais on 04/19/2003 02:57:44

wireless
Starting Member

8 Posts

Posted - 2003-04-21 : 13:53:31

Thanks again.

The thing is there are multiple switches. Data needs to be output in one of several formats.

For example there is the one in the first part of your post which if varied as follows


SELECT [Date], Switch, MaxMOUs 
FROM DataLucent d WHERE 
[Date] BETWEEN '4/15/03' AND '4/18/03' 
AND MOUs = 
(SELECT MAX(MOUs) AS MaxMOUs FROM DataLucent i 
WHERE Switch = 1 AND CONVERT(varchar, i.[Date], 101) = CONVERT(varchar, d.[Date], 101))

.
returns:


Date             Switch   MOUs
4/15/03 7:00 PM     1     3343
4/16/03 8:00 PM     1     2373
4/17/03 5:00 PM     1     5342

.
This is good. It is one part of what might be requested.

But since there are multiple switches, the user might want to see an overview of all individually, for example, if the user wants to see the busiest hours for both switches 1 and 2 for these dates then the desired output would be:


Date             Switch   MOUs
4/15/03 7:00 PM     1     3343
4/16/03 8:00 PM     1     2373
4/17/03 5:00 PM     1     5342
4/15/03 3:00 PM     2     1343
4/16/03 9:00 PM     2     2273
4/17/03 2:00 PM     2     5347

.
On the other hand the user might want to see what the the system busy hour was so I need to sum up the MOUs from each hour for each switch then select the busiest hour and output that data:


Date             Switch   MOUs
4/15/03 7:00 PM    All    7343
4/16/03 8:00 PM    All    6383
4/17/03 5:00 PM    All    9368

.
This is where the sum comes in. Each hour needs to be summed and then the busiest hour selected.

The sql would have Switch = 'All' in it.

Is this something that can be done in sql? I sure it can because a more appropriate question would be is there anything that can't be done in sql.

-David

Edited by - wireless on 04/21/2003 13:58:07

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources