I have a table which has startdatetime and enddatetime. how to find the the particuler datetime which is overlapping in other dates: see below example
create table #period (
id int,
starttime datetime,
endtime datetime
);
insert into #period values
(1,'2013-10-10 08:00:00' , '2013-10-10 10:00:00'),
(2,'2013-10-10 08:10:00' , '2013-10-10 08:20:00'),
(3,'2013-10-10 08:10:00' , '2013-10-10 08:30:00')
(4,'2013-10-10 08:15:00' , '2013-10-10 08:25:00')
select * from #period
required output is '2013-10-10 08:15:00' , '2013-10-10 08:20:00' is getting overlapped in all the dates.
expected output: '2013-10-10 08:15:00' '2013-10-10 08:20:00' 5 Min
EDIT:
I apologies for previous question Here it is the proper details with example
create table #period
(
Sitecode varchar(20),
svrname varchar(10),
StartTime datetime,
downtimeEnd datetime
)
go
insert into #period values
('A','S1','2013-10-10 10:00:00' , '2013-10-10 11:00:00'),
('A','S2','2013-10-10 10:00:00' , '2013-10-10 11:00:00'),
('A','S3','2013-10-10 10:00:00' , '2013-10-10 11:00:00'),
('A','S1','2013-10-10 03:00:00' , '2013-10-10 03:30:00'),
('A','S2','2013-10-10 06:30:00' , '2013-10-10 07:30:00')
select * from #period
Expected Output is:
Sitecode 'A' Total 2.5 hours (below is the details) '2013-10-10 10:00:00' , '2013-10-10 11:00:00' getting overlapp - 1 hour '2013-10-10 03:00:00' , '2013-10-10 03:30:00' - .5 hour '2013-10-10 06:30:00' , '2013-10-10 07:30:00'- 1 hour
The output is group by sitecode
. and the overlapping date considered only once.
The final output is required:
Sitecode 'A' = 2.5 hours
If you have a set of periods and want the overlaps of all of them, then the overlaps are going to be between the maximum starttime and the minimum end time.
This assumes that the start and end are on the same date. The first just extracts the date from the datetime values (and this may vary depending on the database). The version here is for SQL Server.
You have no overlaps when
min(endtime)
is less thanmax(starttime)
. So, to get dates with an overlap:EDIT:
If you want overlaps within a site code, just add that into the `group by:
Please, try this:
The output will be 9000 Seconds. Than you can convert Seconds into Days, Hours, minutes and seconds.
Hope this helps you.
This is one of few examples where it could make sense to use
HAVING
withoutGROUP BY
(yes it's possible):SQLFiddle DEMO