MSSQL query: How to count by every 2 hours (dynamic)?

Question

I'm new to this site and couldn't find a similar question, and not sure if this is even possible to query in SQL, but, what the heck, worth trying...

I have a access control system which writes the entries log into my DB. Attached sample from my data table:

tpxID   Rec_Creation_Date
2995392 2018-03-06 11:50:45.000
2995391 2018-03-06 11:39:48.000
2995390 2018-03-06 11:30:58.453
2995389 2018-03-06 11:30:49.297
2995388 2018-03-06 11:30:30.687
2995387 2018-03-06 11:30:22.547
2995386 2018-03-06 11:30:13.483
2995385 2018-03-06 11:30:04.813
2995384 2018-03-06 11:29:57.640
2995383 2018-03-06 11:29:49.670

The idea is, that I would like to find out if there have been a time frame of 2 hours which I had more than 200 entries?

I mean dynamic query which will not look at round hours only.

Like not only to query 11:00-13:00 but also to query 11:01-13:01 and 11:02-13:02 etc...

Thanks in advance.

Possible duplicate of [SQL SERVER - Group records by n minutes interval](https://stackoverflow.com/questions/26788729/sql-server-group-records-by-n-minutes-interval) *(Note, the link is for minutes, but it's a simple case of changing from minutes to hours).* — Thom A, Mar 06 '18 at 10:13
do u have to group them by `TpxID`, or you need to find a 2 hour window for a particular date ? — Ven, Mar 06 '18 at 10:14
@BHouse, I do not have to group them by tpxID, but there's also no specific date. — John Arn, Mar 06 '18 at 11:13
@Larnu, this is no duplicate, wish it was, in that topic they are discussing round hours, which are not a solution for my case — John Arn, Mar 06 '18 at 11:16
I think you need to add **consumable** sample data, and expected result sets here then, please. — Thom A, Mar 06 '18 at 11:22

paparazzo · Answer 1 · 2018-03-06T13:24:32.210

1

select t1.dt, count(*) as cnt 
from table t1 
join table t2 
  on t2.dt > t1.dt 
 and datediff(mi, t1.dt, t2.dt) < 120 
group by t1.dt 
having count(*) > 200

edited Mar 06 '18 at 13:24

answered Mar 06 '18 at 10:22

paparazzo

44,497
23
105
176

Didn't solved my case, it simply descending counted all of the entries in a specific date range. – John Arn Mar 06 '18 at 11:20
Works for me now that the edit to `mi` has been made. Think another edit is needed though: `tw` should be `t2` - I can't edit though as edits need to be at least 6 characters! – Joe Mar 06 '18 at 13:23
If that is the answer you can give it the check mark – paparazzo Mar 06 '18 at 13:25

score 0 · Answer 2 · edited Mar 06 '18 at 13:28

We can use CTE and GROUP BY with HAVING for this -

declare @xyz table (id int identity(1,1),tpxID int,Rec_Creation_Date datetime)

insert into @xyz (tpxID, Rec_Creation_Date)
select 2995392, '2018-03-06 11:50:45.000' union all
select 2995391, '2018-03-06 11:39:48.000' union all
select 2995390, '2018-03-06 11:30:58.453' union all
select 2995389, '2018-03-06 11:30:49.297' union all
select 2995388, '2018-03-06 11:30:30.687' union all
select 2995387, '2018-03-06 11:30:22.547' union all
select 2995386, '2018-03-06 11:30:13.483' union all
select 2995385, '2018-03-06 11:30:04.813' union all
select 2995384, '2018-03-06 11:29:57.640' union all
select 2995383, '2018-03-06 11:29:49.670' union all
select 2995383, '2018-03-06 09:29:49.670'

;with cte as (
    select
        x.Rec_Creation_Date as StartDT,
        y.Rec_Creation_Date as EndDT
    from @xyz as x
    inner join @xyz as y on x.Rec_Creation_Date < y.Rec_Creation_Date
    where datediff(HOUR,x.Rec_Creation_Date, y.Rec_Creation_Date) >= 2
)
select
    cte.StartDT,
    cte.EndDT
from cte
inner join @xyz as x on x.Rec_Creation_Date between cte.StartDT and cte.EndDT
group by cte.StartDT , cte.EndDT
having count(*) > 200
order by cte.StartDT , cte.EndDT

MSSQL query: How to count by every 2 hours (dynamic)?

2 Answers2