Checking for time range overlap, the watchman problem [SQL]
Here is a way to flatten date range like this
Start | End
2009-1-1 06:00 | 2009-1-1 18:00
2009-2-1 20:00 | 2009-2-2 04:00
2009-2-2 06:00 | 2009-2-2 14:00
You have to compare previous and next dates in each row and see whether
- Current row's Start date falls between previous row's date range.
- Current row's End date falls between next row's date range.
Using above code, implementing UDF is as simple as followed.
create function fnThereIsWatchmenBetween(@from datetime, @to datetime)
returns bit
as
begin
declare @_Result bit
declare @FlattenedDateRange table (
Start datetime,
[End] datetime
)
insert @FlattenedDateRange(Start, [End])
select distinct
Start =
case
when Pv.Start is null then Curr.Start
when Curr.Start between Pv.Start and Pv.[End] then Pv.Start
else Curr.Start
end,
[End] =
case
when Curr.[End] between Nx.Start and Nx.[End] then Nx.[End]
else Curr.[End]
end
from shift Curr
left join shift Pv on Pv.ID = Curr.ID - 1 --; prev
left join shift Nx on Nx.ID = Curr.ID + 1 --; next
if exists( select 1
from FlattenedDateRange R
where @from between R.Start and R.[End]
and @to between R.Start and R.[End]) begin
set @_Result = 1 --; There is/are watchman/men during specified date range
end
else begin
set @_Result = 0 --; There is NO watchman
end
return @_Result
end
SQL Time Overlap/Conflict Query
use computed dates using a conversion:
cast(cast(mydatecolumn as varchar(20)) + ' ' +
cast(mytimecolumn as varchar(20)) as datetime)
yields e.g. start_datetime and end_datetime
assume variables: @inserted_start_datetime, @inserted_end_datetime, @name
Insert Into MyTable(<values>)
Where not exists (
select * from table t2
where name = @name
and (@inserted_start_datetime between start_datetime and end_datetime
or @inserted_end_datetime between start_datetime and end_datetime
or start_datetime between @inserted_start_datetime and @inserted_end_datetime
or end_datetime between @inserted_start_datetime and @inserted_end_datetime ))
EDITED
SQL - Creating a Grouped 'range' set
As you mentioned, if you try to GROUP BY the openTimeRange alias column in your original query, you will get an error. The reason for this is that the GROUP BY clause is evaluated before the alias is assigned to the result set, and hence you cannot use it. Using an inline view should do the trick:
SELECT T.TicketCount, T.OpenTime, T.openTimeRange
FROM
(
SELECT COUNT([tblTickets].*) AS TicketCount,
DATEDIFF(hh,[dateOpened],[closeDate]) AS OpenTime,
CASE WHEN DATEDIFF(hh,[dateOpened],[closeDate]) BETWEEN 0 AND 2 THEN '0-2'
WHEN DATEDIFF(hh,[dateOpened],[closeDate]) BETWEEN 3 AND 4 THEN '3-4'
WHEN DATEDIFF(hh,[dateOpened],[closeDate]) BETWEEN 5 AND 6 THEN '4-6'
END AS openTimeRange
FROM [tblTickets]
WHERE closeDate IS NOT NULL
GROUP BY [dateOpened],[closeDate]
) T
GROUP BY T.openTimeRange
Analyzing time-dependent, event log from SQL
Before you use my query examples, you need to set up a "helper" table, you only need to do this one time per database:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
This basically creates a table containing a single column containing values from 1 to 8000. You can use a CTE to do the same, but since you son't say the SQL Server version, this will work for all, and is better if you will run this many times.
try this:
DECLARE @Calls table (rowID int not null primary key identity(1,1)
,EvId int not null
,CallId varchar(36)
,rowDateTime datetime
)
SET NOCOUNT ON
INSERT INTO @Calls VALUES ( 0,'df1cbc93-5cf3-402a-940b-4441f6a7ec5c',' 7/9/2008 8:12:56 PM')
INSERT INTO @Calls VALUES ( 1,'df1cbc93-5cf3-402a-940b-4441f6a7ec5c',' 7/9/2008 8:13:07 PM')
INSERT INTO @Calls VALUES ( 0,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:10 PM')
INSERT INTO @Calls VALUES (10,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES ( 1,'ec1c2078-1765-4377-9126-6f26fe33e4a9','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES ( 0,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:13 PM')
INSERT INTO @Calls VALUES (10,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES ( 1,'a3c3b9a0-a23b-4dda-b4e4-e82f0209c94d','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES ( 0,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:15 PM')
INSERT INTO @Calls VALUES (10,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:17 PM')
INSERT INTO @Calls VALUES ( 1,'d23f393d-0272-445a-8670-3f71b016174e','7/10/2008 4:33:17 PM')
--I added more test data, to hit more cases
INSERT INTO @Calls VALUES ( 0,'111111111111111111111111111111111111','7/10/2008 4:10:00 PM')
INSERT INTO @Calls VALUES (10,'111111111111111111111111111111111111','7/10/2008 4:11:00 PM')
INSERT INTO @Calls VALUES ( 1,'111111111111111111111111111111111111','7/10/2008 4:11:00 PM')
INSERT INTO @Calls VALUES ( 0,'222222222222222222222222222222222222','7/10/2008 4:15:00 PM')
INSERT INTO @Calls VALUES (10,'222222222222222222222222222222222222','7/10/2008 4:16:00 PM')
INSERT INTO @Calls VALUES ( 1,'222222222222222222222222222222222222','7/10/2008 4:16:00 PM')
INSERT INTO @Calls VALUES ( 0,'333333333333333333333333333333333333','7/10/2008 4:09:00 PM')
INSERT INTO @Calls VALUES (10,'333333333333333333333333333333333333','7/10/2008 4:18:00 PM')
INSERT INTO @Calls VALUES ( 1,'333333333333333333333333333333333333','7/10/2008 4:18:00 PM')
INSERT INTO @Calls VALUES ( 0,'444444444444444444444444444444444444','7/10/2008 4:13:00 PM')
INSERT INTO @Calls VALUES (10,'444444444444444444444444444444444444','7/10/2008 4:14:00 PM')
INSERT INTO @Calls VALUES ( 1,'444444444444444444444444444444444444','7/10/2008 4:14:00 PM')
INSERT INTO @Calls VALUES ( 0,'555555555555555555555555555555555555','7/10/2008 4:13:00 PM')
SET NOCOUNT OFF
DECLARE @StartRange datetime
DECLARE @EndRange datetime
SET @StartRange='7/10/2008 4:12:00 PM'
SET @EndRange ='7/10/2008 4:15:00 PM'
SET @EndRange=DATEADD(mi,1,@EndRange)
--this lists the match time and each calls details in progress at that time
SELECT
DATEADD(mi,n.Number-1,c.StartTime) AS 'TimeOfMatch'
,c.CallID
,c.StartTime,c.EndTime
FROM (SELECT --this derived table joins together the start and end dates into a single row, filtering out rows more than 90 minutes before the start range (if calls are longer than 90 minutes, increase this) and filters out any rows after the end date (will consider call done at end date then)
CallID, MIN(rowDateTime) AS StartTime, CASE WHEN MAX(rowDateTime)=MIN(rowDateTime) THEN @EndRange ELSE MAX(rowDateTime) END AS EndTime
FROM @Calls
WHERE rowDateTime>=DATEADD(mi,-90,@StartRange) --AND rowDateTime<=@EndRange
GROUP BY CallID
) c
INNER JOIN Numbers n ON DATEDIFF(mi,c.StartTime,c.EndTime)+1>=n.Number
WHERE DATEADD(mi,n.Number-1,c.StartTime)>=@StartRange AND DATEADD(mi,n.Number-1,c.StartTime)<@EndRange
ORDER BY 1
--this lists just the match time and the call count
SELECT
DATEADD(mi,n.Number-1,c.StartTime) AS 'TimeOfMatch'
,c.CallID
,c.StartTime,c.EndTime
FROM (SELECT --this derived table joins together the start and end dates into a single row, filtering out rows more than 90 minutes before the start range (if calls are longer than 90 minutes, increase this) and filters out any rows after the end date (will consider call done at end date then)
CallID, MIN(rowDateTime) AS StartTime, CASE WHEN MAX(rowDateTime)=MIN(rowDateTime) THEN @EndRange ELSE MAX(rowDateTime) END AS EndTime
FROM @Calls
WHERE rowDateTime>=DATEADD(mi,-90,@StartRange) --AND rowDateTime<=@EndRange
GROUP BY CallID
) c
INNER JOIN Numbers n ON DATEDIFF(mi,c.StartTime,c.EndTime)+1>=n.Number
WHERE DATEADD(mi,n.Number-1,c.StartTime)>=@StartRange AND DATEADD(mi,n.Number-1,c.StartTime)<@EndRange
ORDER BY 1
here's the output:
TimeOfMatch CallID StartTime EndTime
----------------------- ------------------------------------ ----------------------- -----------------------
2008-07-10 16:12:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:13:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:13:00.000 444444444444444444444444444444444444 2008-07-10 16:13:00.000 2008-07-10 16:14:00.000
2008-07-10 16:13:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:14:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:14:00.000 444444444444444444444444444444444444 2008-07-10 16:13:00.000 2008-07-10 16:14:00.000
2008-07-10 16:14:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:15:00.000 333333333333333333333333333333333333 2008-07-10 16:09:00.000 2008-07-10 16:18:00.000
2008-07-10 16:15:00.000 555555555555555555555555555555555555 2008-07-10 16:13:00.000 2008-07-10 16:16:00.000
2008-07-10 16:15:00.000 222222222222222222222222222222222222 2008-07-10 16:15:00.000 2008-07-10 16:16:00.000
(10 row(s) affected)
TimeOfMatch
----------------------- -----------
2008-07-10 16:12:00.000 1
2008-07-10 16:13:00.000 3
2008-07-10 16:14:00.000 3
2008-07-10 16:15:00.000 3
(4 row(s) affected)
You will need a composite index on rowDateTime+CallId. However for the best performance, if you created a new table (clustered index on startdate+CallId) that contained both the start and end dates of a single call (possibly using a trigger when the EvId=0 insert with start date, and when EvId=1 update end date) then the derived table could be removed with this new table.
How to group ranged values using SQL Server
From the article that Josh posted, here's my take (tested and working):
SELECT
MAX(t1.gapID) as gapID,
t2.gapID-MAX(t1.gapID)+t2.gapSize as gapSize
-- max(t1) is the specific lower bound of t2 because of the group by.
FROM
( -- t1 is the lower boundary of an island.
SELECT gapID
FROM gaps tbl1
WHERE
NOT EXISTS(
SELECT *
FROM gaps tbl2
WHERE tbl1.gapID = tbl2.gapID + tbl2.gapSize + 1
)
) t1
INNER JOIN ( -- t2 is the upper boundary of an island.
SELECT gapID, gapSize
FROM gaps tbl1
WHERE
NOT EXISTS(
SELECT * FROM gaps tbl2
WHERE tbl2.gapID = tbl1.gapID + tbl1.gapSize + 1
)
) t2 ON t1.gapID <= t2.gapID -- For all t1, we get all bigger t2 and opposite.
GROUP BY t2.gapID, t2.gapSize
t-sql: return range in which a given number falls
Another way;
select case when value / 1000 < 1
then '0000-0999'
else cast(value / 1000 * 1000 as varchar(16)) + '-' + cast(value / 1000 * 1000 + 999 as varchar(16))
end
Validating UPDATE and INSERT statements against an entire table
Use an after trigger to check that the overlap constraint has not been violated:
create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
begin
if exists (select *
from inserted i
inner join Patrol p
on i.GuardId = p.GuardId
and i.PatrolId <> p.PatrolId
where (i.Starts between p.starts and p.Ends)
or (i.Ends between p.Starts and p.Ends))
rollback transaction
end
NOTE: Rolling back a transaction within a trigger will terminate the batch. Unlike a normal contraint violation, you will not be able to catch the error.
You may want a different where clause depending on how you define the time range and overlap. For instance if you want to be able to say Guard #1 is at X from 6:00 to 7:00 then Y 7:00 to 8:00 the above would not allow. You would want instead:
create trigger Patrol_NoOverlap_AIU on Patrol for insert, update as
begin
if exists (select *
from inserted i
inner join Patrol p
on i.GuardId = p.GuardId
and i.PatrolId <> p.PatrolId
where (p.Starts <= i.Starts and i.Starts < p.Ends)
or (p.Starts <= i.Ends and i.Ends < p.Ends))
rollback transaction
end
Where Starts is the time the guarding starts and Ends is the infinitesimal moment after guarding ends.
Related Topics
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
Using Input from a Text File for Where Clause
MySQL Question - How to Handle Multiple Types of Users - One Table or Multiple
Will a SQL Server Job Skip a Scheduled Run If It Is Already Running
What Is the Data Type for Unix_Timestamp (Mysql)
How to Catch a Query Exception in Laravel to See If It Fails
Key/Value Pairs in a Database Table
How to Write a Conditional in a MySQL Select Statement
Select Columnvalue If the Column Exists Otherwise Null
Differencebetween a Primary Key and a Unique Constraint
Oracle SQL Where Clause to Find Date Records Older Than 30 Days
Join Two Spreadsheets on a Common Column in Excel or Openoffice
Regular Expressions Inside SQL Server
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
Sqlite Multi-Primary Key on a Table, One of Them Is Auto Increment