Q
Problem solve Get help with specific problems with your technologies, process and projects.

Aggregates with associated detail values

In MS SQL Server, I have a table with the following columns: DateTime, Item, Value. I need to write a query that...

returns the maximum and minimum value on each day of the current month for each item. In addition, the query must return the DateTime coincident with the maximum or minimum value. I am fairly new to SQL and although I do OK with the basics, I could use some help with this one.

Of course, there are always several legitimate ways to solve a SQL problem; especially for interesting problems like this. To solve the problem without the DateTime columns, the SQL is fairly simple, that is if you don't mind the somewhat inelegant way I've truncated the time from a DateTime.

select Item, 
       convert(datetime,convert(varchar,DateTime,112)) as Day, 
       min(Value) as minvalue, 
       max(Value) as maxvalue 
  from Items
  group by Item, convert(datetime,convert(varchar,DateTime,112))
To get the actual DateTime values associated with the max and min values, I've embedded the original SQL in the FROM clause and included two in-line sub-queries in the SELECT clause.
select z.Item, 
       z.minvalue, 
       z.maxvalue,
       ( select DateTime from Items x 
           where x.Item = z.Item 
             and convert(datetime,convert(varchar,x.DateTime,112)) = z.Day
             and x.Value = z.minvalue ) as MinValueTime, 
       ( select DateTime from Items y 
           where y.Item = z.Item 
             and convert(datetime,convert(varchar,y.DateTime,112)) = z.Day
             and y.Value = z.maxvalue ) as MaxValueTime
  from ( select Item, 
                convert(datetime,convert(varchar,DateTime,112)) as Day, 
                min(Value) as minvalue, 
                max(Value) as maxvalue 
           from Items
           group by Item, convert(datetime,convert(varchar,DateTime,112)) ) z

For More Information


This was last published in October 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close