SQL Script between two ranges

niraj_19_dgp · September 14, 2018, 10:37am

Below is two table for two projects.
STS is start time in Hour, ETS is End Time in Hour,

CREATE TABLE #V(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10))
CREATE TABLE #E(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10))

INSERT INTO #V VALUES('ABC',1,5,'Busy')
INSERT INTO #V VALUES('ABC',7,10,'Ready')

INSERT INTO #E VALUES('ABC',1,3,'Not Ready')
INSERT INTO #E VALUES('ABC',3,5,'Ready')
INSERT INTO #E VALUES('ABC',5,8,'Not Ready')
INSERT INTO #E VALUES('ABC',9,12,'Busy')

Eg: ABC work from 1 pm to 5 pm with status Busy.

As you see the different type of overlapping STS and ETS,
I want a combine result with breakup of STS and ETS
Output:
ABC, 1, 3, Busy, TableV
ABC, 1, 3, Not Ready, TableE
ABC, 3, 5, Busy, TableV
ABC, 3, 5, Ready, TableE
ABC, 5, 7, NULL, TableV
ABC, 5, 7, Not Ready, TableE
ABC, 7, 8, Ready, TableV
ABC, 7, 8, Not Ready, TableE
ABC, 8, 9, Ready, TableV
ABC, 8, 9, Busy, TableE
ABC, 9,10, Ready, TableV
ABC, 9,10, Busy, TableE
ABC, 10,12, NULL, TableV
ABC, 10,12, Busy, TableE

Thanks in Advance

gigawatt38 · September 14, 2018, 12:35pm

Hi niraj_19_dgp,

Pls try my solution (your Output sample is wrong between 8-9 on TableE, because it need to be NULL the status):

CREATE TABLE #V(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10));
CREATE TABLE #E(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10));

INSERT INTO #V VALUES('ABC',1,5,'Busy');
INSERT INTO #V VALUES('ABC',7,10,'Ready');

INSERT INTO #E VALUES('ABC',1,3,'Not Ready');
INSERT INTO #E VALUES('ABC',3,5,'Ready');
INSERT INTO #E VALUES('ABC',5,8,'Not Ready');
INSERT INTO #E VALUES('ABC',9,12,'Busy');

select * from #V;
select * from #E;

select sts as ts
into #intervals
from #V
union
select sts
from #E
union
select ets
from #V
union
select ets
from #E;

select * from #intervals;

select a.ts as sts, min(b.ts) as ets
into #valid_intervals
from #intervals a
    cross join #intervals b
where a.ts < b.ts
group by a.ts
order by 1;

select * from #valid_intervals;

select v.name, base.sts, base.ets, v.stat, 'TableV' as source_table
into #final_report
from #valid_intervals base
    inner join #V v on v.sts <= base.sts and base.sts < v.ets
union
select e.name, base.sts, base.ets, e.stat, 'TableE' as source_table
from #valid_intervals base
    inner join #E e on e.sts <= base.sts and base.sts < e.ets
order by 1, 2;

select * from #final_report;

insert into #final_report
select v.name, base.sts, base.ets, NULL, 'TableV'
from #valid_intervals base
    cross join #V v
    left join #final_report fin on fin.name = v.name
        and fin.source_table = 'TableV'
        and base.sts = fin.sts
        and base.ets = fin.ets
where fin.name is null
union
select e.name, base.sts, base.ets, NULL, 'TableE'
from #valid_intervals base
    cross join #E e
    left join #final_report fin on fin.name = e.name
        and fin.source_table = 'TableE'
        and base.sts = fin.sts
        and base.ets = fin.ets
where fin.name is null

select *
from #final_report
order by 2, 3, 1;

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler

niraj_19_dgp · September 16, 2018, 11:34am

Hello gigawatt38,

Thanks for your help. It is really appreciated.

But following part of your query is taking very long time:

select a.ts as sts, min(b.ts) as ets
into #valid_intervals
from #intervals a
cross join #intervals b
where a.ts < b.ts
group by a.ts
order by 1;

gigawatt38 · September 17, 2018, 8:12am

Hi niraj_19_dgp,

pls use this indexing expression before using that mentioned before select-into:

create index idxqq on #intervals(ts);

It will help I think.

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

niraj_19_dgp · September 17, 2018, 2:41pm

Hello Gigawatt,

Thanks for your suggestion.

But is there is any other way to do this, as I have very huge data.

gigawatt38 · September 17, 2018, 3:26pm

Hi niraj_19_dgp,

Ok, if you have huge amounts of records, you need more indexes, where you join the datasets together.
Pls check all of the new parts related to indexing (marked with: --new part, indexing):

CREATE TABLE #V(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10));
CREATE TABLE #E(NAME VARCHAR(100), STS INTEGER, ETS INTEGER,Stat VARCHAR(10));

INSERT INTO #V VALUES('ABC',1,5,'Busy');
INSERT INTO #V VALUES('ABC',7,10,'Ready');

INSERT INTO #E VALUES('ABC',1,3,'Not Ready');
INSERT INTO #E VALUES('ABC',3,5,'Ready');
INSERT INTO #E VALUES('ABC',5,8,'Not Ready');
INSERT INTO #E VALUES('ABC',9,12,'Busy');

select * from #V;
select * from #E;

--new part, indexing
create index idxqq on #V(sts, ets, name);

--new part, indexing
create index idxqq on #E(sts, ets, name);

select sts as ts
into #intervals
from #V
union
select sts
from #E
union
select ets
from #V
union
select ets
from #E;

select * from #intervals;

--new part, indexing
create index idxqq on #intervals(ts);

select a.ts as sts, min(b.ts) as ets
into #valid_intervals
from #intervals a
    cross join #intervals b
where a.ts < b.ts
group by a.ts
order by 1;

select * from #valid_intervals;

--new part, indexing
create index idxqq on #valid_intervals(sts, ets);

select v.name, base.sts, base.ets, v.stat, 'TableV' as source_table
into #final_report
from #valid_intervals base
    inner join #V v on v.sts <= base.sts and base.sts < v.ets
union
select e.name, base.sts, base.ets, e.stat, 'TableE' as source_table
from #valid_intervals base
    inner join #E e on e.sts <= base.sts and base.sts < e.ets
order by 1, 2;

select * from #final_report;

--new part, indexing
create index idxqq on #final_report(name, source_table, sts, ets);

insert into #final_report
select v.name, base.sts, base.ets, NULL, 'TableV'
from #valid_intervals base
    cross join #V v
    left join #final_report fin on fin.name = v.name
        and fin.source_table = 'TableV'
        and base.sts = fin.sts
        and base.ets = fin.ets
where fin.name is null
union
select e.name, base.sts, base.ets, NULL, 'TableE'
from #valid_intervals base
    cross join #E e
    left join #final_report fin on fin.name = e.name
        and fin.source_table = 'TableE'
        and base.sts = fin.sts
        and base.ets = fin.ets
where fin.name is null

select *
from #final_report
order by 2, 3, 1;

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

Tested by: http://rextester.com/l/sql_server_online_compiler