CODEWITHSUNDEEP
sqlsql-serversql-server-2016window-functions
Dave
Dave

Reputation: 155

SQL Server 2016 max in partition by select statement not working

I'm trying to return the min and max datetime for a grouped set via a partition by statement. The min line works but the max is returning the max date of ungrouped results. SQL Server 2016. What am I missing?

SELECT 
    [temp_Emp], [temp_EmpID], [temp_Date], [Temp_Start], [Temp_End],
    MIN(Temp_Start) OVER (PARTITION BY temp_EmpID, temp_Date ORDER BY Temp_Start, Temp_End) AS 'Start',
    MAX(Temp_End) OVER (PARTITION BY temp_EmpID, temp_Date ORDER BY Temp_Start, Temp_End) AS 'End'
FROM 
    tmp_startend

Result:

 temp_EmpID temp_Date   Temp_Start  Temp_End    Start   End
 300    7/08/2017   7/08/2017 8:00  7/08/2017 8:15  7/08/2017 8:00  7/08/2017 8:15
 300    7/08/2017   7/08/2017 8:15  7/08/2017 8:30  7/08/2017 8:00  7/08/2017 8:30
 300    7/08/2017   7/08/2017 9:00  7/08/2017 10:00 7/08/2017 8:00  7/08/2017 10:00
 300    7/08/2017   7/08/2017 9:15  7/08/2017 14:30 7/08/2017 8:00  7/08/2017 14:30
 300    7/08/2017   7/08/2017 9:30  7/08/2017 14:00 7/08/2017 8:00  7/08/2017 14:30
 300    7/08/2017   7/08/2017 10:00 7/08/2017 13:00 7/08/2017 8:00  7/08/2017 14:30
 300    7/08/2017   7/08/2017 10:00 7/08/2017 14:30 7/08/2017 8:00  7/08/2017 14:30
 300    7/08/2017   7/08/2017 14:00 7/08/2017 15:00 7/08/2017 8:00  7/08/2017 15:00

Upvotes: 1

Views: 241

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Leave out the order by. That makes the values cumulative:

SELECT [temp_Emp], [temp_EmpID],[temp_Date],[Temp_Start],[Temp_End],
       min(Temp_Start) over (partition by temp_EmpID, temp_Date) as [Start],
       max(Temp_End) over (partition by temp_EmpID, temp_Date) as [End]
FROM tmp_startend;

The min() just happened to work because the order by put the smallest value first -- so the cumulative min() is the same as the overall min().

Upvotes: 1

Related Questions