Search code examples
sql-servercountmaxhour

SQL Server Group by Count of DateTime Per Hour and Update Max count into table?

I have this piece of code that counts the number of occurrences for a set of events on an hourly basis. Now I need the MAX COUNT and input that in a different table. I am new to SQL and I am having problems with the UPDATE statement coupled with MAX and all that is below. Can anyone help please? Thanks!

    SELECT CAST(locate_received_date as date) AS 'ForDate', 
    DATEPART(hh, locate_received_date) AS 'OnHour', 
    COUNT (*) AS 'Count'
    FROM BELL_DPRA2_locates_fact
    WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999'
    GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date);

This script results is attached. enter image description here

Edit: This answer worked for me thanks to @agfc. I modified a bit to work for me. 

UPDATE MyTable
SET MaxHourlyCount =
     (SELECT Max(A.HourCount)
      FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', 
    DATEPART(hh, locate_received_date) AS 'OnHour', 
    COUNT (*) AS HourCount
    FROM BELL_DPRA2_locates_fact
   WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999'
    GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) AS A)
Solution 
  • -- I'd rename your 'Count' Field to something else not to use the SQL function name.     
UPDATE myOtherTable
    SET MaxField = SELECT Max(HourCount) FROM (SELECT CAST(locate_received_date as date) AS 'ForDate', 
        DATEPART(hh, locate_received_date) AS 'OnHour', 
        COUNT (*) AS HourCount
        FROM BELL_DPRA2_locates_fact
        WHERE locate_received_date BETWEEN '2016-12-01 00:00:00.000' AND '2016-12-01 23:59:59.999'
        GROUP BY CAST(locate_received_date as date), DATEPART(hh, locate_received_date)) As MyMaxResult;