Query to determine start and end dates based on time overlap
Due to the lack of clarity in the question, I came up with four different solutions. The solutions differ on:
- Whether you are to "cascade" per Chris's answer
- When you have a closed date, whether you use the earliest date for that group or the started date for the record that is closed.
Please note this is done in SQL Server, not MySQL. Other than some very minor syntax changes, it should work the same.
Common setup and sample data for all four methods
CREATE TABLE #example
(
id int NOT NULL DEFAULT '0',
borrower_id int NOT NULL,
started datetime NULL DEFAULT NULL,
closed datetime NULL DEFAULT NULL,
dead datetime NULL DEFAULT '0000-00-00 00:00:00'
);
CREATE TABLE #result
(
borrower_id int NOT NULL DEFAULT '0',
started datetime NULL DEFAULT NULL,
ended datetime NULL DEFAULT NULL
);
INSERT INTO #example
(id, borrower_id, started, closed, dead)
VALUES
(7714,238846,'2015-01-27 15:14:50','2015-02-02 14:14:13',NULL),
(7882,238846,'2015-01-28 13:25:58',NULL,'2015-05-15 12:16:07'),
(13190,259140,'2015-03-17 10:11:44',NULL,'2015-03-18 07:31:57'),
(13192,259140,'2015-03-17 10:12:17',NULL,'2015-03-18 11:46:46'),
(13194,259140,'2015-03-17 10:12:53',NULL,'2015-03-18 11:46:36'),
(14020,259140,'2015-03-23 14:32:16','2015-03-24 15:57:32',NULL),
(17124,242650,'2015-04-16 16:19:08','2015-04-16 16:21:06',NULL),
(19690,238846,'2015-05-15 13:17:31',NULL,'2015-05-27 13:56:43'),
(20038,242650,'2015-05-19 15:38:17',NULL,NULL),
(20040,242650,'2015-05-19 15:39:58',NULL,'2015-05-21 12:01:02'),
(20302,242650,'2015-05-21 13:09:06',NULL,NULL),
(20304,242650,'2015-05-21 13:09:54',NULL,NULL),
(20306,242650,'2015-05-21 13:10:19',NULL,NULL),
(20308,242650,'2015-05-21 13:12:20',NULL,NULL),
(21202,238846,'2015-05-29 16:47:29',NULL,NULL),
(21204,238846,'2015-05-29 16:47:56',NULL,NULL),
(21208,238846,'2015-05-29 17:05:15',NULL,NULL),
(21210,238846,'2015-05-29 17:05:55',NULL,NULL),
(21918,242650,'2015-06-04 17:04:29',NULL,'2015-06-12 15:47:23');
1. CASCADING - USING CLOSED RECORD solution
This is the solution I believe the asker is looking for & matches his results.
select *
into #temp1
from #example
while (select count(1) from #temp1)>0
begin
--Grab only one user's records and place into a temp table to work with
declare @curUser int
set @curUser=(select min(borrower_id) from #temp1)
select *
into #temp2
from #temp1 t1
where t1.borrower_id=@curUser
while(select count(1) from #temp2)>0
begin
--Grab earliest start date and use as basis for 15 day window (#2 rule)
--Use the record as basis for rules 3 and 4
declare @minTime datetime
set @minTime=(select min(started) from #temp2)
declare @maxTime datetime
set @maxTime=@minTime
declare @curId int
set @curId=(select min(id) from #temp2 where started=@minTime)
select *
into #temp3
from #temp2 t2
where t2.id=@curId
--Remove earliest record from pool of potential records to check rules against
delete
from #temp2
where id=@curId
--Insert all records within 15 days of start date, then remove record from pool
while (select count(1)
from #temp2 t2
where t2.started<=DATEADD(day,15,@maxTime)
or t2.closed<=DATEADD(day,15,@maxTime)
or t2.dead<=DATEADD(day,15,@maxTime) )>0
begin
insert into #temp3
select *
from #temp2 t2
where t2.started<=DATEADD(day,15,@maxTime) or t2.closed<=DATEADD(day,15,@maxTime) or t2.dead<=DATEADD(day,15,@maxTime)
delete
from #temp2
where started<=DATEADD(day,15,@maxTime) or closed<=DATEADD(day,15,@maxTime) or dead<=DATEADD(day,15,@maxTime)
--set new max time from any column
if (select max(started) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
if (select max(closed) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
if (select max(dead) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
end
--Calculate end time according to rule #3
declare @end datetime
set @end = null
set @end=(select min(closed) from #temp3)
if @end is not null
begin
set @minTime=(select started from #temp3 where closed=@end)
end
if @end is null
begin
if(select count(1) from #temp3 where dead is null)=0
set @end= (select max(dead) from #temp3)
end
insert into #result (borrower_id,started,ended)
values (@curUser,@minTime,@end)
drop table #temp3
end
--Done with the one user, remove him from temp table and iterate thru to the next user
delete
from #temp1
where borrower_id=@curUser
drop table #temp2
end
drop table #temp1
drop table #example
select * from #result order by started
drop table #result
2. NON-CASCADING - USING CLOSED RECORD solution
Start calculated by first closed date when available, then by earliest start date.
select *
into #temp1
from #example
while (select count(1) from #temp1)>0
begin
--Grab only one user's records and place into a temp table to work with
declare @curUser int
set @curUser=(select min(borrower_id) from #temp1)
select *
into #temp2
from #temp1 t1
where t1.borrower_id=@curUser
while(select count(1) from #temp2)>0
begin
--Grab earliest start date and use as basis for 15 day window (#2 rule)
--Use the record as basis for rules 3 and 4
declare @minTime datetime
set @minTime=(select min(started) from #temp2)
declare @curId int
set @curId=(select min(id) from #temp2 where started=@minTime)
select *
into #temp3
from #temp2 t2
where t2.id=@curId
--Remove earliest record from pool of potential records to check rules against
delete
from #temp2
where id=@curId
--Insert all records within 15 days of start date, then remove record from pool
insert into #temp3
select *
from #temp2 t2
where t2.started<=DATEADD(day,15,@minTime)
delete
from #temp2
where started<=DATEADD(day,15,@minTime)
--Insert all records within 15 days of closed, then remove record from pool
insert into #temp3
select *
from #temp2 t2
where t2.closed<=DATEADD(day,15,@minTime)
delete
from #temp2
where closed<=DATEADD(day,15,@minTime)
--Insert all records within 15 days of dead, then remove record from pool
insert into #temp3
select *
from #temp2 t2
where t2.dead<=DATEADD(day,15,@minTime)
delete
from #temp2
where dead<=DATEADD(day,15,@minTime)
--Calculate end time according to rule #3
declare @end datetime
set @end = null
set @end=(select min(closed) from #temp3)
if @end is not null
begin
set @minTime=(select started from #temp3 where closed=@end)
end
if @end is null
begin
if(select count(1) from #temp3 where dead is null)=0
set @end= (select max(dead) from #temp3)
end
insert into #result (borrower_id,started,ended)
values (@curUser,@minTime,@end)
drop table #temp3
end
--Done with the one user, remove him from temp table and iterate thru to the next user
delete
from #temp1
where borrower_id=@curUser
drop table #temp2
end
drop table #temp1
drop table #example
select * from #result
drop table #result
3. NON-CASCADING - USING EARLIEST DATE solution
Start calculated by earliest date only.
select *
into #temp1
from #example
while (select count(1) from #temp1)>0
begin
--Grab only one user's records and place into a temp table to work with
declare @curUser int
set @curUser=(select min(borrower_id) from #temp1)
select *
into #temp2
from #temp1 t1
where t1.borrower_id=@curUser
while(select count(1) from #temp2)>0
begin
--Grab earliest start date and use as basis for 15 day window (#2 rule)
--Use the record as basis for rules 3 and 4
declare @minTime datetime
set @minTime=(select min(started) from #temp2)
declare @curId int
set @curId=(select min(id) from #temp2 where started=@minTime)
select *
into #temp3
from #temp2 t2
where t2.id=@curId
--Remove earliest record from pool of potential records to check rules against
delete
from #temp2
where id=@curId
--Insert all records within 15 days of start date, then remove record from pool
insert into #temp3
select *
from #temp2 t2
where t2.started<=DATEADD(day,15,@minTime) or t2.closed<=DATEADD(day,15,@minTime) or t2.dead<=DATEADD(day,15,@minTime)
delete
from #temp2
where started<=DATEADD(day,15,@minTime) or closed<=DATEADD(day,15,@minTime) or dead<=DATEADD(day,15,@minTime)
--Calculate end time according to rule #3
declare @end datetime
set @end = null
set @end=(select min(closed) from #temp3)
if @end is null
begin
if(select count(1) from #temp3 where dead is null)=0
set @end= (select max(dead) from #temp3)
end
insert into #result (borrower_id,started,ended)
values (@curUser,@minTime,@end)
drop table #temp3
end
--Done with the one user, remove him from temp table and itterate thru to the next user
delete
from #temp1
where borrower_id=@curUser
drop table #temp2
end
drop table #temp1
drop table #example
select * from #result
drop table #result
4. CASCADING - USING EARLIEST DATE solution
Start calculated by earliest date only.
select *
into #temp1
from #example
while (select count(1) from #temp1)>0
begin
--Grab only one user's records and place into a temp table to work with
declare @curUser int
set @curUser=(select min(borrower_id) from #temp1)
select *
into #temp2
from #temp1 t1
where t1.borrower_id=@curUser
while(select count(1) from #temp2)>0
begin
--Grab earliest start date and use as basis for 15 day window (#2 rule)
--Use the record as basis for rules 3 and 4
declare @minTime datetime
set @minTime=(select min(started) from #temp2)
declare @maxTime datetime
set @maxTime=@minTime
declare @curId int
set @curId=(select min(id) from #temp2 where started=@minTime)
select *
into #temp3
from #temp2 t2
where t2.id=@curId
--Remove earliest record from pool of potential records to check rules against
delete
from #temp2
where id=@curId
--Insert all records within 15 days of start date, then remove record from pool
while (select count(1)
from #temp2 t2
where t2.started<=DATEADD(day,15,@maxTime)
or t2.closed<=DATEADD(day,15,@maxTime)
or t2.dead<=DATEADD(day,15,@maxTime) )>0
begin
insert into #temp3
select *
from #temp2 t2
where t2.started<=DATEADD(day,15,@maxTime) or t2.closed<=DATEADD(day,15,@maxTime) or t2.dead<=DATEADD(day,15,@maxTime)
delete
from #temp2
where started<=DATEADD(day,15,@maxTime) or closed<=DATEADD(day,15,@maxTime) or dead<=DATEADD(day,15,@maxTime)
--set new max time from any column
if (select max(started) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
if (select max(closed) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
if (select max(dead) from #temp3)>@maxTime
set @maxTime=(select max(started) from #temp3)
end
--Calculate end time according to rule #3
declare @end datetime
set @end = null
set @end=(select min(closed) from #temp3)
if @end is null
begin
if(select count(1) from #temp3 where dead is null)=0
set @end= (select max(dead) from #temp3)
end
insert into #result (borrower_id,started,ended)
values (@curUser,@minTime,@end)
drop table #temp3
end
--Done with the one user, remove him from temp table and iterate thru to the next user
delete
from #temp1
where borrower_id=@curUser
drop table #temp2
end
drop table #temp1
drop table #example
select * from #result order by started
drop table #result