I am grouping by the date part of a DateTime column using the CONVERT function selecting the MAX and MIN price value for that date. I would like to include the first and last price values for the group based on the full DateTime value.
The current query looks like this:
SELECT CONVERT(date, dateColumn) [dateColumn], MAX(price) [maxColumn], MIN(price) [minColumn] FROM table GROUP BY CONVERT(date, dateColumn)
Is it possible to include the first and last price in this group with the smallest/largest full DateTime without using a subquery?
Advertisement
Answer
It is possible without a subquery, but at the expense of performance:
SELECT DISTINCT CONVERT(date, dateColumn) as dateColumn, MAX(price) OVER (PARTITION BY CONVERT(date, dateColumn)) as max_price, MIN(price) OVER (PARTITION BY CONVERT(date, dateColumn)) as min_price, FIRST_VALUE(price) OVER (PARTITION BY CONVERT(date, dateColumn) ORDER BY dateColumn ASC) as first_price, FIRST_VALUE(price) OVER (PARTITION BY CONVERT(date, dateColumn) ORDER BY dateColumn DESC) as last_price FROM table;