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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.