SQL min(), max()

Question

I have a table with price history for products(ProductCostHistory). Columns: ProductID(PK,FK), StartDate(PK), EndDate, StandardCost, ModifiedDate

This is what the table looks like:

ProductID   StartDate   EndDate     StandardCost    ModifiedDate
707         2011-05-31  2012-05-29  12,0278        2012-05-29
707         2012-05-30  2013-05-29  13,8782        2013-05-29
707         2013-05-30      NULL    13,0863        2013-05-16
708         2011-05-31  2012-05-29  12,0278        2012-05-29 
708         2012-05-30  2013-05-29  13,8782        2013-05-29
708         2013-05-30  NULL        13,0863        2013-05-16

For each product I want to view: Current Price, the highest and the lowest historical price.

This is my Code:

USE AdventureWorks2014
GO
SELECT Distinct ProductCostHistory.ProductID, ProductCostHistory.StandardCost, MAX(ProductCostHistory.StandardCost) AS HighestPrice, MIN(ProductCostHistory.StandardCost) AS LowestPrice FROM Production.ProductCostHistory
GROUP BY ProductID, StandardCost
Order by ProductID

Output looks like this:

ProductID   StandardCost    HighestPrice    LowestPrice
707         12,0278         12,0278         12,0278
707         13,0863         13,0863         13,0863
707         13,8782         13,8782         13,8782
708         12,0278         12,0278         12,0278
708         13,0863         13,0863         13,0863
708         13,8782         13,8782         13,8782

But I want it more like this (example):

PRoductID    StandardCost    HighestPrice     LowestPrice
707          13,0863         13,8787          12,0278
708          12,0278         13,8782          12,0278

Thanks

Answer 1

Consider using a conditional aggregate for CurrentPrice:

SELECT h.ProductID, 
       MAX(CASE WHEN h.EndDate IS NULL THEN h.StandardCost END) AS CurrentPrice,
       MAX(h.StandardCost) AS HighestPrice, 
       MIN(h.StandardCost) AS LowestPrice 
FROM Production.ProductCostHistory h
GROUP BY h.ProductID
ORDER BY h.ProductID