SQLTeam.com | Weblogs | Forums

Show 3 or more occurrences within a 24 hour period

Hi,

I have a table with a field that gets populated with a 1 for an occurrence, and another field that stores the DTTM that the occurrence was entered. I need to find anytime more than 3 occurrences were entered within a 24 hour period. The sum of the field that is populated with a 1 could be used as well. I just need to know when the sum is greater than 3 in any 24 hour period. Any help with this is greatly appreciated!

please help us help you, provide at least minimum the table structure (DDL)

create table #boonbangle(id int, name varchar(50) )---etc

hi hope this helps

drop table SampleData 
go 

create table SampleData 
(
occurence int , 
Date_Time datetime
)
go 

insert into SampleData select 1 , '2021-03-26 20:43:46.180'
insert into SampleData select 1 , '2021-03-26 12:43:46.180'
insert into SampleData select 1 , '2021-03-26 09:43:46.180'
insert into SampleData select 1 , '2021-03-26 14:43:46.180'
insert into SampleData select 1 , '2021-03-26 14:35:00.180'
insert into SampleData select 1 , '2021-03-25 20:43:46.180'
insert into SampleData select 1 , '2021-03-25 12:43:46.180'

declare @start_date_time datetime = '2021-03-26'
declare @end_date_time datetime = dateadd(hh,24,@start_date_time) 

; with cte as 
(
select count(*) as cnt from SampleData where Date_Time between @start_date_time and @end_date_time
) 
select * from  SampleData, cte  where Date_Time between @start_date_time and @end_date_time and cte.cnt > 3