How to retrieve min/max values and corresponding dates in a table

Question

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value.

Example Data:

CREATE TABLE mytable
    ([ID] int, [TEMP] FLOAT, [DATE] DATE)
;

INSERT INTO mytable
    ([ID], [TEMP], [DATE])
VALUES
    (8305,  16.38320208,  '03/22/2002'),
    (8305,  17.78320208,  '11/15/2010'),
    (8305,  16.06320208,  '03/11/2002'),
    (8305,  18.06320208,  '02/01/2007'),
    (2034,  5.2,  '03/12/1985'),
    (2034,  2.24,  '05/31/1991'),
    (2034,  6.91,  '09/15/1981'),
    (2034,  7.98,  '07/16/1980'),
    (2034,  10.03,  '03/21/1979'),
    (2034,  6.85,  '11/19/1982')
;

I'm struggling use Common Table Expressions to obtain both Min values & associated date, Max values and associated date, AND the actual min/max dates from the whole dataset in the same query

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [TEMP] DESC) AS rn
   FROM mytable
)
SELECT cte.ID, 
    COUNT(*) AS COUNT, 
    cte.TEMP AS MAXTEMP, 
    cte.DATE AS MAXTEMPDATE,
    MAX(mt.DATE) AS MAXDATE
FROM cte
    INNER JOIN mytable mt ON cte.ID = mt.ID
WHERE rn = 1
GROUP BY cte.ID, 
    cte.TEMP, 
    cte.DATE;

Edit 1: Expected output per user

|   ID | COUNT |     MAXTEMP | MAXTEMPDATE |       MAXDATE |       MINTEMP | MINTEMPDATE |    MINDATE|
|------|-------|-------------|-------------|---------------|---------------|-------------|-----------|
| 2034 |     6 |       10.03 |  1979-03-21 |    1991-05-31 |          2.24 |  1991-05-31 |1979-03-21 |
| 8305 |     4 | 18.06320208 |  2007-02-01 |    2010-11-15 |   16.06320208 |  2002-03-11 |2002-03-11 |

Answer 1

You can achieve this in two ways upfront - one is simpler to write but will hit the base table 3 times, the other is more complex but only hits the base table twice.

The first is to create your aggregate table from your base table, then join your base table back twice - once to get the date of each max/min temp value -

SELECT agg.ID
     , agg.COUNT
     , agg.MAXTEMP
     , det1.DATE AS MAXTEMPDATE
     , agg.MAXDATE
     , agg.MINTEMP
     , det2.DATE AS MINTEMPDATE
     , agg.MINDATE
FROM
(
    SELECT m.ID
         , COUNT(1) AS COUNT
         , MAX(m.TEMP) AS MAXTEMP
         , MAX(m.DATE) AS MAXDATE
         , MIN(m.TEMP) AS MINTEMP
         , MIN(m.DATE) AS MINDATE
    FROM #mytable AS m
    GROUP BY m.ID
) AS agg
    INNER JOIN #mytable AS det1
        ON det1.TEMP = agg.MAXTEMP
    INNER JOIN #mytable AS det2
        ON det2.TEMP = agg.MINTEMP
ORDER BY agg.ID;

The other is to start with the same aggregated base table, but cross apply a pivoted table that contains both the max/min temp values -

SELECT agg.ID
     , agg.COUNT
     , agg.MAXTEMP
     , minmax.MAXTEMPDATE
     , agg.MAXDATE
     , agg.MINTEMP
     , minmax.MINTEMPDATE
     , agg.MINDATE
FROM
(
    SELECT m.ID
         , COUNT(1) AS COUNT
         , MAX(m.TEMP) AS MAXTEMP
         , MAX(m.DATE) AS MAXDATE
         , MIN(m.TEMP) AS MINTEMP
         , MIN(m.DATE) AS MINDATE
    FROM #mytable AS m
    GROUP BY m.ID
) AS agg
    CROSS APPLY
(
    SELECT [1] AS MAXTEMPDATE
         , [0] AS MINTEMPDATE
    FROM
    (
        SELECT m2.DATE
             , CASE m2.TEMP
                   WHEN MAXTEMP THEN
                       1
                   WHEN MINTEMP THEN
                       0
               END AS MINMAX
        FROM #mytable AS m2
        WHERE m2.TEMP IN ( MAXTEMP, MINTEMP )
    ) AS X
    PIVOT
    (
        MAX(DATE)
        FOR MINMAX IN ([1], [0])
    ) P
) minmax
ORDER BY agg.ID;

If you run both and view the execution plan, you will see that the second one is slightly more efficient, but slightly more complex to write.

They both yield this result set -

ID          COUNT       MAXTEMP                MAXTEMPDATE MAXDATE    MINTEMP                MINTEMPDATE MINDATE
----------- ----------- ---------------------- ----------- ---------- ---------------------- ----------- ----------
2034        6           10.03                  1979-03-21  1991-05-31 2.24                   1991-05-31  1979-03-21
8305        4           18.06320208            2007-02-01  2010-11-15 16.06320208            2002-03-11  2002-03-11