Help with Query - Counting item Occurrence over a rolling 72 hour period

Question

Help with Query - Counting item Occurrence over a rolling 72 hour period

jparks 85798

SSC Veteran

Points: 262
More actions
June 18, 2015 at 9:13 am

#398617

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.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

jparks 85798 (6/18/2015)
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.

first off Welcome to SSC

second......the code below is the best way to get tried and tested answers

USE [tempdb]

CREATE TABLE [dbo].[MigrationBreakdown](

[DepartDateTime] [datetime] NOT NULL,

[ZoneMovement] [varchar](50) NOT NULL

) ON [PRIMARY]

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'6 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'11 to 7')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 15:30:00.000' AS DateTime), N'9 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 21:00:00.000' AS DateTime), N'7 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 08:01:00.000' AS DateTime), N'7 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 09:30:00.000' AS DateTime), N'10 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 10:36:00.000' AS DateTime), N'9 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:00:00.000' AS DateTime), N'6 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:30:00.000' AS DateTime), N'6 to 9')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 12:00:00.000' AS DateTime), N'3 to 7')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 08:01:00.000' AS DateTime), N'3 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 08:01:00.000' AS DateTime), N'6 to 9')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 09:00:00.000' AS DateTime), N'9 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 09:55:00.000' AS DateTime), N'9 to 10')

third......based on the sample data you provided...what results are you expecting?

me thinks that the sample data needs more rows to fully explain your requirements...

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jparks 85798 SSC Veteran Points: 262 More actions · Answer 2

Thanks for the suggestions. You are correct, looking back my sample data does not contain enough to illustrate what I am looking for. Let me see if I can explain further. This data represents aircraft movements between defined geographic locations (ZoneMovement). I need to parse through my existing data and isolate those instances in which we have 3 or more movements from and to the same zone ie. '3 to 10' in ANY given 72 hour period. The end result would list the movement and then a date(s). Not sure if this helps at all.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

jparks 85798 (6/18/2015)
Thanks for the suggestions. You are correct, looking back my sample data does not contain enough to illustrate what I am looking for. Let me see if I can explain further. This data represents aircraft movements between defined geographic locations (ZoneMovement). I need to parse through my existing data and isolate those instances in which we have 3 or more movements from and to the same zone ie. '3 to 10' in ANY given 72 hour period. The end result would list the movement and then a date(s). Not sure if this helps at all.

ok...so please provide more representative data and based on that data,your expected results

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

jparks 85798 SSC Veteran Points: 262 More actions · Answer 4

USE [tempdb]

CREATE TABLE [dbo].[MigrationBreakdown](

[DepartDateTime] [datetime] NOT NULL,

[ZoneMovement] [varchar](50) NOT NULL

) ON [PRIMARY]

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'6 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'11 to 7')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 15:30:00.000' AS DateTime), N'9 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 21:00:00.000' AS DateTime), N'7 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 08:01:00.000' AS DateTime), N'3 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 09:30:00.000' AS DateTime), N'10 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 10:36:00.000' AS DateTime), N'9 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:00:00.000' AS DateTime), N'6 to 3')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:30:00.000' AS DateTime), N'3 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 12:00:00.000' AS DateTime), N'7 to 4')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 08:01:00.000' AS DateTime), N'3 to 6')

INSERT [dbo].[MigrationBreakdown] ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 09:00:00.000' AS DateTime), N'9 to 3')

From this data set I would expect:

Date Movement Count

2015-06-27 3 to 6 4

Where the date would be the first instance within the rolling 72 hour period where the movement occurred.

sgmunson SSC Guru Points: 110644 More actions · Answer 5

jparks 85798 (6/18/2015)
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.

The first order of business here is to define EXACTLY what constitutes your 72 hour window. You can't just look for anything that's within a particular time interval of any single record, as then you could end up pairing records that aren't in the same window, and similarly, an arbitrarily defined window from any given record could easily miss records that should actually count. This problem is actually quite complex. If Microsoft's implementation of windowed aggregates was more complete (meaning a more useful implementation of the RANGE form of window specification), we might have a much easier time with this, but even then, we'd still have to determine exactly what constitutes a window. Just saying ANY 72 hour window opens a rather HUGE can of worms. It's not insoluble, it's just going to take a lot of work. The problem is that the only way to solve this is to look at EVERY POSSIBLE WINDOW and count the number of records in that window. So... do we define the window at the full resolution of the datetime field? Or do we limit it to starting at the beginning of each second? Or is resolution granular to the nearest minute sufficient? How is the data generated for the datetime field, and does it only come in with an hour and minute value? The more precise the stored value is, the more complicated this task becomes. Also, what if someone later does an UPDATE query on this table and uses a full resolution datetime value, so that now, some records have that full resolution, while other don't? That could seriously mess things up if we assume we'll only have values at a lower resolution.

jparks 85798, what say you ?

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

jparks 85798 SSC Veteran Points: 262 More actions · Answer 6

Steve,

Thanks for the follow up. I agree, the windowed functions are nice, but not quite as robust as I had hoped. As for the datetime resolution, I don't even need minute. The nearest hour would be perfectly sufficient. We are a very small environment and this data set is the result of an ETL package that I created to pull from an internal APP so it is very regularized and not at risk of being updated with rogue data. Does that clarify?

sgmunson SSC Guru Points: 110644 More actions · Answer 7

jparks 85798 (6/18/2015)
Steve,
Thanks for the follow up. I agree, the windowed functions are nice, but not quite as robust as I had hoped. As for the datetime resolution, I don't even need minute. The nearest hour would be perfectly sufficient. We are a very small environment and this data set is the result of an ETL package that I created to pull from an internal APP so it is very regularized and not at risk of being updated with rogue data. Does that clarify?

Knowing that at least brings the number of possible windows down to a more manageable number, in that only 24 windows begin in a given day. So the next step is to define each window, where the first window begins at the start of the hour of the very earliest datetime in your dataset, and the last window ends at the end of the hour of the latest datetime in your dataset. Can you write the query that would provide the starting and ending datetime values and an ordered 'WindowID" value that increases with the start time of that window? Once you have that, the next step is fairly easy. I don't have the time right now to write this one, but why don't you at least give that a try and let me know what you come up with.

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

jparks 85798 SSC Veteran Points: 262 More actions · Answer 8

This is probably a mess, but it does the trick:

declare @start datetime

declare @end datetime

set @start = GetDate()

set @end = dateadd(hour,72,GetDate())

while @start < dateadd(day,30,getdate()) begin

Insert Into tempdb.dbo.DateRanges

Values(@start,@end)

set @start = dateadd(hour,1,@start)

set @end = dateadd(hour,1,@end)

end

What next?

sgmunson SSC Guru Points: 110644 More actions · Answer 9

jparks 85798 (6/18/2015)
This is probably a mess, but it does the trick:
declare @start datetime
declare @end datetime
set @start = GetDate()
set @end = dateadd(hour,72,GetDate())
while @start < dateadd(day,30,getdate()) begin
Insert Into tempdb.dbo.DateRanges
Values(@start,@end)
set @start = dateadd(hour,1,@start)
set @end = dateadd(hour,1,@end)
end
What next?

Let's start with asking whether what you coded does what is needed. There's no obvious WindowID indicated, and you only have a 3 day overall window, which is far too short a timeframe to look at. Also, the values are based on raw GETDATE(), which will be arbitrary on minute and have seconds values as well, and we need to stay away from those kinds of values.

I had more time this evening so I coded something all the way through:

CREATE TABLE #MigrationBreakdown (

[DepartDateTime] [datetime] NOT NULL,

[ZoneMovement] [varchar](50) NOT NULL

);

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 00:00:00.000' AS DateTime), N'6 to 4')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'6 to 4')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 14:00:00.000' AS DateTime), N'11 to 7')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 15:30:00.000' AS DateTime), N'9 to 6')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-26 21:00:00.000' AS DateTime), N'7 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 08:01:00.000' AS DateTime), N'3 to 6')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 09:30:00.000' AS DateTime), N'10 to 4')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 10:36:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:00:00.000' AS DateTime), N'6 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 11:30:00.000' AS DateTime), N'3 to 6')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-27 12:00:00.000' AS DateTime), N'7 to 4')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 08:01:00.000' AS DateTime), N'3 to 6')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 09:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 13:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-28 21:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-06-30 23:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-07-01 23:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-07-02 23:00:00.000' AS DateTime), N'9 to 3')

INSERT #MigrationBreakdown ([DepartDateTime], [ZoneMovement]) VALUES (CAST(N'2015-07-03 23:00:00.000' AS DateTime), N'9 to 3')

CREATE TABLE #DateRanges (

WindowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

StartDate datetime NOT NULL,

EndDate datetime NOT NULL

);

CREATE INDEX IX_StartDate_EndDate ON #DateRanges

(

StartDate ASC

)

INCLUDE

(

WindowID,

EndDate

);

DECLARE @START_DT AS datetime = DATEADD(minute, 0 - DATEPART(minute, (SELECT MIN(DepartDateTime) FROM #MigrationBreakdown)), (SELECT MIN(DepartDateTime) FROM #MigrationBreakdown));

DECLARE @END_DT AS datetime = DATEADD(minute, 60 - DATEPART(minute, (SELECT MAX(DepartDateTime) FROM #MigrationBreakdown)), (SELECT MAX(DepartDateTime) FROM #MigrationBreakdown));

PRINT @START_DT;

PRINT @END_DT;

--DECLARE @END AS datetime;

WHILE @START_DT < DATEADD(hour, -72, @END_DT)

BEGIN

--SET @END = dateadd(hour, 72, @START_DT)

INSERT INTO #DateRanges VALUES(@START_DT, dateadd(hour, 72, @START_DT))

PRINT @START_DT

--PRINT @END

SET @START_DT = dateadd(hour, 1, @START_DT)

END

SELECT *

FROM #DateRanges;

WITH ALL_WINDOWS AS (

SELECT W.WindowID, MB.ZoneMovement, COUNT(MB.DepartDateTime) AS MoveCount

FROM #DateRanges AS W

INNER JOIN #MigrationBreakdown AS MB

ON MB.DepartDateTime BETWEEN W.StartDate AND W.EndDate

GROUP BY W.WindowID, MB.ZoneMovement

HAVING COUNT(MB.DepartDateTime) > 3

)

SELECT AW.ZoneMovement, MAX(AW.MoveCount) AS MoveCount

FROM ALL_WINDOWS AS AW

GROUP BY AW.ZoneMovement;

DROP TABLE #DateRanges;

DROP TABLE #MigrationBreakdown;

Let me know what you think...

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

jparks 85798 SSC Veteran Points: 262 More actions · Answer 10

Steve,

Thank you SO much for the assistance. I didn't think about removing the extraneous Minutes and seconds. I adjusted the code to pull from my actual data table as follows:

CREATE TABLE #DateRanges (

WindowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

StartDate datetime NOT NULL,

EndDate datetime NOT NULL

);

CREATE INDEX IX_StartDate_EndDate ON #DateRanges

(

StartDate ASC

)

INCLUDE

(

WindowID,

EndDate

);

DECLARE @START_DT AS datetime = DATEADD(minute, 0 - DATEPART(minute, (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));

DECLARE @END_DT AS datetime = DATEADD(minute, 60 - DATEPART(minute, (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));

PRINT @START_DT;

PRINT @END_DT;

--DECLARE @END AS datetime;

WHILE @START_DT < DATEADD(hour, -72, @END_DT)

BEGIN

--SET @END = dateadd(hour, 72, @START_DT)

INSERT INTO #DateRanges VALUES(@START_DT, dateadd(hour, 72, @START_DT))

PRINT @START_DT

--PRINT @END

SET @START_DT = dateadd(hour, 1, @START_DT)

END

SELECT *

FROM #DateRanges;

WITH ALL_WINDOWS AS (

SELECT W.WindowID, MB.ZoneMovement, COUNT(MB.DepartDateTime) AS MoveCount

FROM #DateRanges AS W

INNER JOIN FOSKPIData.dbo.MigrationBreakdown AS MB

ON MB.DepartDateTime BETWEEN W.StartDate AND W.EndDate

GROUP BY W.WindowID, MB.ZoneMovement

HAVING COUNT(MB.DepartDateTime) > 3

)

SELECT AW.ZoneMovement, MAX(AW.MoveCount) AS MoveCount

FROM ALL_WINDOWS AS AW

GROUP BY AW.ZoneMovement

DROP TABLE #DateRanges;

With this result:

ZoneMovementMoveCount

10 to 7 5

9 to 7 9

4 to 1 4

7 to 6 4

6 to 4 5

3 to 7 5

6 to 9 5

7 to 9 7

7 to 4 6

9 to 6 4

4 to 6 5

1 to 4 4

6 to 5 4

It is SO close. Is there any way to add in the actual date window in which the MoveCount's occur. I have tried to manipulate, but I can't seem to get it to work.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

This is an optimization of what Steve was going after. The main insight is this: while you certainly could scan every 72-hour timeslice ever for this content (or every 72-slice within a lager timeframe), the only time slices that have some chance of qualifying are those where you at least have some data. So - base the timeslices you're evaluating on the datetime values you have in the table.

As a result you don't have to use any form of cursor or loop to build up or to scan through, and can do it in a set-based fashion.

;with DepartTimesCTE as

(select Row_number() over (ORDER BY departDateTime) DepartRN,

DepartDateTime Startdate,

dateadd(hour,72,departdatetime) EndDate,

ZoneMovement ZoneToMatch

from migrationBreakdown)

select d.departRN, d.Startdate,d.zoneToMatch, count(*)

from DepartTimesCTE d join MigrationBreakDown m on m.zonemovement=d.zoneToMatch and m.DepartDateTime between d.startdate and d.enddate

group by d.departRN, d.Startdate,d.zoneToMatch

having count(*)>3

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sgmunson SSC Guru Points: 110644 More actions · Answer 12

jparks 85798 (6/19/2015)
Steve,
Thank you SO much for the assistance. I didn't think about removing the extraneous Minutes and seconds. I adjusted the code to pull from my actual data table as follows:
CREATE TABLE #DateRanges (
WindowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL
);
CREATE INDEX IX_StartDate_EndDate ON #DateRanges
(
StartDate ASC
)
INCLUDE
(
WindowID,
EndDate
);
DECLARE @START_DT AS datetime = DATEADD(minute, 0 - DATEPART(minute, (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));
DECLARE @END_DT AS datetime = DATEADD(minute, 60 - DATEPART(minute, (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));
PRINT @START_DT;
PRINT @END_DT;
--DECLARE @END AS datetime;
WHILE @START_DT < DATEADD(hour, -72, @END_DT)
BEGIN
--SET @END = dateadd(hour, 72, @START_DT)
INSERT INTO #DateRanges VALUES(@START_DT, dateadd(hour, 72, @START_DT))
PRINT @START_DT
--PRINT @END
SET @START_DT = dateadd(hour, 1, @START_DT)
END
SELECT *
FROM #DateRanges;
WITH ALL_WINDOWS AS (
SELECT W.WindowID, MB.ZoneMovement, COUNT(MB.DepartDateTime) AS MoveCount
FROM #DateRanges AS W
INNER JOIN FOSKPIData.dbo.MigrationBreakdown AS MB
ON MB.DepartDateTime BETWEEN W.StartDate AND W.EndDate
GROUP BY W.WindowID, MB.ZoneMovement
HAVING COUNT(MB.DepartDateTime) > 3
)
SELECT AW.ZoneMovement, MAX(AW.MoveCount) AS MoveCount
FROM ALL_WINDOWS AS AW
GROUP BY AW.ZoneMovement
DROP TABLE #DateRanges;
With this result:
ZoneMovementMoveCount
10 to 7 5
9 to 7 9
4 to 1 4
7 to 6 4
6 to 4 5
3 to 7 5
6 to 9 5
7 to 9 7
7 to 4 6
9 to 6 4
4 to 6 5
1 to 4 4
6 to 5 4
It is SO close. Is there any way to add in the actual date window in which the MoveCount's occur. I have tried to manipulate, but I can't seem to get it to work.

As most of the occurrences will take place across multiple windows, the latest window would seem to be the correct one to get. Not sure if this is going to perform well, but take a look:

CREATE TABLE #DateRanges (

WindowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

StartDate datetime NOT NULL,

EndDate datetime NOT NULL

);

CREATE INDEX IX_StartDate_EndDate ON #DateRanges

(

StartDate ASC

)

INCLUDE

(

WindowID,

EndDate

);

DECLARE @START_DT AS datetime = DATEADD(minute, 0 - DATEPART(minute, (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MIN(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));

DECLARE @END_DT AS datetime = DATEADD(minute, 60 - DATEPART(minute, (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown)), (SELECT MAX(DepartDateTime) FROM FOSKPIData.dbo.MigrationBreakdown));

PRINT @START_DT;

PRINT @END_DT;

WHILE @START_DT < DATEADD(hour, -72, @END_DT)

BEGIN

INSERT INTO #DateRanges VALUES(@START_DT, dateadd(hour, 72, @START_DT))

PRINT @START_DT

SET @START_DT = dateadd(hour, 1, @START_DT)

END

--SELECT *

--FROM #DateRanges;

WITH ALL_WINDOWS AS (

SELECT W.WindowID, MB.ZoneMovement, COUNT(MB.DepartDateTime) AS MoveCount

FROM #DateRanges AS W

INNER JOIN FOSKPIData.dbo.MigrationBreakdown AS MB

ON MB.DepartDateTime BETWEEN W.StartDate AND W.EndDate

GROUP BY W.WindowID, MB.ZoneMovement

HAVING COUNT(MB.DepartDateTime) > 3

),

FINAL_CANDIDATES AS (

SELECT AW.ZoneMovement, MAX(AW.MoveCount) AS MoveCount

FROM ALL_WINDOWS AS AW

GROUP BY AW.ZoneMovement

)

SELECT (SELECT DR.StartDate AS WindowStart FROM #DateRanges AS DR WHERE DR.WindowID =

(SELECT MAX(AW.WindowID) FROM ALL_WINDOWS AS AW WHERE AW.MoveCount = FC.MoveCount AND AW.ZoneMovement = FC.ZoneMovement)),

FC.*

FROM FINAL_CANDIDATES AS FC

DROP TABLE #DateRanges;

There may well be optimizations that can be done or windowed aggregates that could be applied instead.

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

Help with Query - Counting item Occurrence over a rolling 72 hour period

Cookies on SQLServerCentral