June 12, 2012 at 11:39 am
Identifying rows of data that share a common point in time – How To do it with SQL 2005 compliant T-SQL
DDL:
CREATE TABLE dbo.REQQUEUE
( hID numeric(18, 0) NOT NULL IDENTITY (1, 1),
dtStart datetime NOT NULL,
dtEnd datetime NULL,
sObjectName varchar(256) NOT NULL,
sObjectCaller varchar(256) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.REQQUEUE ADD CONSTRAINT PK_REQQUEUE PRIMARY KEY CLUSTERED ( hID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Using the above table which stores requests including when they started processing (dtStart) and when they finished processing (dtEnd), how can I construct a query that will show me all rows that share a common point in time? In other words which requests were processing at the exact same time. It would be great to also identify the time frame (Start and End ) that is common to all requests that share a common point(s) in time but identifying which requests were executing at the same time is sufficient.
Example:
Row 1 dtStartDate = ‘2012-06-10 08:05:00.830’; dtEndDate = ‘2012-06-10 08:06:00.830’;
Row 2 dtStartDate = ‘2012-06-11 10:00:00.830’; dtEndDate = ‘2012-06-11 11:35:00.830’;
Row 3 dtStartDate = ‘2012-06-11 11:30:00.830’; dtEndDate = ‘2012-06-12 11:01:00.830’;
Row 4 dtStartDate = ‘2012-06-12 10:05:00.830’; dtEndDate = ‘2012-06-12 10:06:00.830’;
Row 5 dtStartDate = ‘2012-06-12 11:05:00.830’; dtEndDate = ‘2012-06-12 11:55:00.830’;
Using the above rows as an example…..
Row 1 has a Start and End that the other 4 rows do not intersect with. No other row has a start date or and End Date that falls between Row 1’s StartDate and EndDate.
Rows 2 & 3 share a common point in time because Row 3’s StartDate falls between Rows 2’s StartDate and End Date. Row 4 is not included with 2 & 3 because its Start Date and End Date do fall between Row 3’s StartDate and EndDate but not Row 2’s StartDate and EndDate.
Row 4 does share an intersecting point in Time with Row 3 but not with row 5. It also does not share a point in time with row 2 even though row 3 does share a point in time with row 2.
Row 5 shares no common point in time with any other row.
I hope this makes’ sense because it’s hard to articulate.
My end goal with this is to identify requests that are running at the same time whether that common duration was 1 second, 1 minute or 1 hour. This table is a very busy table for us and so with a few exceptions most rows share a common point in time with at least 1 other request (we have several servers processing requests and so multiple requests can be in process at the same time) .
Any help including links to articles on this would be appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!June 12, 2012 at 11:45 am
Makes sense, but without sample data, its very very very hard to start coding. Please provide sample data.
If you are just looking for links to other articles that share the same problem, i dont have any.
June 17, 2012 at 3:12 pm
One point of clarification on the requirement please, when you say "a common point in time" what determines that point? Will this be for a real-time dashboard in which case you want to see currently running processes, i.e. dtEnd IS NULL, or is this for after the fact analysis where all processes are complete, i.e. dtEnd is always populated? I may have answered my own question given that all your sample data has a non-NULL dtEnd.
This brings me to, if you're supplying the point in time then would this be as simple as using BETWEEN?
Sample data:
USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.REQQUEUE')
AND type IN ( N'U' ) )
DROP TABLE dbo.REQQUEUE;
GO
CREATE TABLE dbo.REQQUEUE
(
hID NUMERIC(18, 0) NOT NULL
IDENTITY(1, 1),
dtStart DATETIME NOT NULL,
dtEnd DATETIME NULL,
sObjectName VARCHAR(256) NOT NULL,
sObjectCaller VARCHAR(256) NOT NULL
)
GO
ALTER TABLE dbo.REQQUEUE ADD CONSTRAINT PK_REQQUEUE PRIMARY KEY CLUSTERED ( hID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT INTO dbo.REQQUEUE
(
dtStart,
dtEnd,
sObjectName,
sObjectCaller
)
SELECT dtStartDate = '2012-06-10 08:05:00.830',
dtEndDate = '2012-06-10 08:06:00.830',
sObjectName = 'obj1',
sObjectCaller = 'caller1'
UNION ALL
SELECT dtStartDate = '2012-06-11 10:00:00.830',
dtEndDate = '2012-06-11 11:35:00.830',
sObjectName = 'obj2',
sObjectCaller = 'caller2'
UNION ALL
SELECT dtStartDate = '2012-06-11 11:30:00.830',
dtEndDate = '2012-06-12 11:01:00.830',
sObjectName = 'obj3',
sObjectCaller = 'caller3'
UNION ALL
SELECT dtStartDate = '2012-06-12 10:05:00.830',
dtEndDate = '2012-06-12 10:06:00.830',
sObjectName = 'obj4',
sObjectCaller = 'caller4'
UNION ALL
SELECT dtStartDate = '2012-06-12 11:05:00.830',
dtEndDate = '2012-06-12 11:55:00.830',
sObjectName = 'obj5',
sObjectCaller = 'caller5';
GO
Is this kind of what you're looking for in terms of a result?
DECLARE @dt DATETIME = '2012-06-11 11:32:00.000';
WITH cte
AS (
SELECT dtStart,
dtEnd,
sObjectName,
sObjectCaller
FROM dbo.REQQUEUE
WHERE @dt BETWEEN dtStart AND dtEnd
)
SELECT *,
(
SELECT MIN(dtStart)
FROM cte
) AS minDtStart,
(
SELECT MAX(dtEnd)
FROM cte
) AS maxDtEnd
FROM cte;
Note: this code references the CTE three times so may not be as efficient as other solutions. I am just trying to nail down the requirement with this example.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 18, 2012 at 2:35 pm
Common Point In Time: A Time Of Day on a specific date in which all applicable events are active.
Example: At the movie theatre there are 30 screens, each scheduled to start a movie at a time that one or more other films may also be starting. Managemenet would like to idnetify the common point in time when all 30 screens are actively showing a film.
If every movie starts before 10PM and no movie finishes before 11PM then the common point in time that all 30 screens share is a duration of 1 hour from 10PM till 11PM. This is the time when management will schedule downtime projects for staff since that is the time when the least number of customers are out of their seats.
Not every scenario will have a common point in time that can be measured as a druation. It just depends on the scenario. In my case there is a possability of a duration because each requyest has a satret time and an end time.
Make sense?
Kindest Regards,
Just say No to Facebook!June 18, 2012 at 3:59 pm
I think so. If I have it, you can change my query to this to see the inner-section (maxDtStart and minDtEnd) where all processes overlap:
DECLARE @dt DATETIME = '2012-06-11 11:32:00.000';
WITH cte
AS (
SELECT dtStart,
dtEnd,
sObjectName,
sObjectCaller
FROM dbo.REQQUEUE
WHERE @dt BETWEEN dtStart AND dtEnd
)
SELECT *,
(
SELECT MAX(dtStart)
FROM cte
) AS maxDtStart,
(
SELECT MIN(dtEnd)
FROM cte
) AS minDtEnd
FROM cte;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply