Count item Occurrence over a rolling 72 hour period

Transact-SQL
1

All,

My first post here and i'm still relatively new to t-sql. I have searched around and I am completely stuck on where to even start. I am using MS SQL 2012 and have a pretty simple table dbo.MigrationBreakdown with sample data as follows.

DepartDateTime	             ZoneMovement
2015-06-26 14:00:00.000	     6 to 4
2015-06-26 14:00:00.000	     11 to 7
2015-06-26 15:30:00.000	     9 to 6
2015-06-26 21:00:00.000	     7 to 3
2015-06-27 08:01:00.000	     7 to 4
2015-06-27 09:30:00.000	     10 to 4
2015-06-27 10:36:00.000	     9 to 3
2015-06-27 11:00:00.000	     6 to 3
2015-06-27 11:30:00.000	     6 to 9
2015-06-27 12:00:00.000	     3 to 7
2015-06-28 08:01:00.000	     3 to 6
2015-06-28 08:01:00.000	     6 to 9
2015-06-28 09:00:00.000	     9 to 3
2015-06-28 09:55:00.000	     9 to 10

What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business. Thanks in advance for any direction.

2

Option 1:

select *
  from (select *
              ,count(*) over(partition by zonemovement) as c
          from dbo.migrationbreakdown
       ) as a
 where c>=3

Option 2:

select b.*
  from (select zonemovement
          from dbo.migrationbreakdown
         group by zonemovement
         having count(*)>=3
       ) as a
       inner join dbo.migrationbreakdown as b
               on b.zonemovement=a.zonemovement
3

Bitsmed,

Thanks for the response. This certainly gives me a total count of occurrences over the total data set, but I am still struggling about how to do this count within a rolling 72 hour period.

4

Whoops, my mistake.

Option 1:

select *
  from (select *
              ,count(*) over(partition by zonemovement) as c
          from dbo.migrationbreakdown
         where departdatetime>=dateadd(hh,-72,getdate())
       ) as a
 where c>=3

Option 2:

select b.*
  from (select zonemovement
          from dbo.migrationbreakdown
         where departdatetime>=dateadd(hh,-72,getdate())
         group by zonemovement
         having count(*)>=3
       ) as a
       inner join dbo.migrationbreakdown as b
               on b.zonemovement=a.zonemovement
              and b.departdatetime>=dateadd(hh,-72,getdate())
5

I think this is closer but I think it only deals with 72 hours prior to TODAY. I need the individual movements grouped into 72 hour periods. I'm wondering if altering my source data to specific the begin and end date/time of the window for each occurrence would help?

6

Maybe this then:

select a.departdatetime
      ,a.zonemovement
  from dbo.migrationbreakdown as a
       inner join dbo.migrationbreakdown as b
               on b.zonemovement=a.zonemovement
              and b.departdatetime>=dateadd(hh,-72,a.departdatetime)
              and b.departdatetime<a.departdatetime
 group by a.departdatetime
         ,a.zonemovement
 having count(*)>=3