Get the top record based on date and hour

Question

My project has a table named CheckIns, basically containing all checking-ins performed by employees. Now I need to get 2 reports of the check-ins.

First report: The total number of check-ins performed by day and hour
Second report: The only check-in peak per day (ie. in what hour that most employees checked-in per day)

For the 1st requirement, I'm able to achieve it by using this query

SELECT CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns]
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
ORDER BY CONVERT(DATE,CheckInDateTime)

For the 2nd requirement, by adding CheckInCount DESC into the ORDER BY part, I got the hour that had most check-ins on top for each day, as you can see an example of 03 Jun (most of the checkins appeared to be between 8pm and 9pm, so 8pm - 20:00 is on top).

However, this still does not satisfy the requirement, as it should only display the top peak record only for each day. Can someone please advise me on what to add to this query to achieve that goal?

SELECT CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns]
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
ORDER BY CONVERT(DATE,CheckInDateTime), CheckInCount DESC

Thank you.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jun 08 '22 at 01:59
I think you can use the `Partition By` with `ROW_NUMBER()`, then select the records with row number equal to 1 — mtdot, Jun 08 '22 at 02:13
Thanks @mtdot. I've managed to get the peak records. However, if there are 2 or more records (same day but different hours) that have the same CheckInCount number, I need to get them both, not just the first one. — Anthony, Jun 08 '22 at 02:47
If you are expecting ties in your dataset then use `DENSE_RANK` instead of `ROW_NUMBER`. `DENSE_RANK` will give same ranking to ties. In your case, records with same day and CheckInCount number will have same rank i.e 1. — PankajSanwal, Jun 08 '22 at 03:40
Take a look at: https://stackoverflow.com/questions/176964/select-top-10-records-for-each-category — AliNajafZadeh, Jun 08 '22 at 04:04
[dup on MS Q&A](https://learn.microsoft.com/en-us/answers/questions/880812/sql-server-get-the-top-record-based-on-date-and-ho.html) and marked as answered — SMor, Jun 08 '22 at 12:12

score 2 · Answer 1 · answered Jun 08 '22 at 04:07

Here is one way to do this

with data 
  as (
SELECT CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns]
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
      )
   ,ranked_data
    as (
select rank() over(partition by [Date] order by CheckInCount desc) as rnk
      ,[Date]
      ,[Hour]
      ,CheckInCount
  from data
       )
select *
  from ranked_data
 where rnk=1

Get the top record based on date and hour

1 Answers1