SQL Server组通过每小时DateTime计数并更新Max计入表中？(SQL Server Group by Count of DateTime Per Hour and Update Max

SQL Server组通过每小时DateTime计数并更新Max计入表中？(SQL Server Group by Count of DateTime Per Hour and Update Max count into table?)

我有这段代码来计算每小时一组事件的发生次数。 现在我需要MAX COUNT并将其输入到不同的表中。 我是SQL新手，并且遇到与MAX结合的UPDATE语句和下面的所有问题。 任何人都可以帮忙吗？ 谢谢！

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);

此脚本结果已附加。

编辑：这个答案为我工作，感谢@agfc。 我修改了一下为我工作。

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)

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.

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)

最满意答案

-- 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; -- 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;