SQL Server: Data Counting grouped by date
The following SQL Transact Code example aggregates the total number of records in a day.
The number is counted up by the ID
SELECT COUNT(IDViewLog) AS nViews,
|
And summarized above the day's date
GROUP BY CAST(dtView AS date) |
example
/****** Script for SelectTopNRows command from SSMS ******/ SELECT COUNT(IDViewLog) AS nViews, CAST(dtView AS date) as DateViews FROM tbl_Notes_View_Log GROUP BY CAST(dtView AS date) ORDER BY CAST(dtView AS date) DESC
|
It is important that the actual date is stored as a time stamp with date time to millisecond
/****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP (1000) [IDViewLog] ,[IDNote] ,[IDUser] ,[dtView] FROM [readdy].[dbo].[tbl_Notes_View_Log]
|
IDViewLog IDNote IDUser dtView 1 0 1 2018-02-26 11:45:50.997 2 0 1 2018-02-26 13:26:13.260 3 0 1 2018-02-26 13:29:44.320 4 0 1 2018-02-26 13:29:49.973 5 0 1 2018-02-26 13:29:50.487 |