Link to home
Start Free TrialLog in
Avatar of crompnk
crompnkFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Merge SQL

Hi,
I have two tables with interval (range) data in them, I would like to merge the intervals in both tables so they are consecutive. I'm not sure how best to do this and I would appreciate some help.
I've attached the code to create the tables and show the required result.
Thank you

DECLARE @tempinformation1 TABLE (
	NBLINE INT identity
	,ID VARCHAR(20)
	,INVFROM FLOAT
	,INVTO FLOAT
	)

DECLARE @tempinformation2 TABLE (
	NBLINE INT identity
	,ID VARCHAR(20)
	,INVFROM FLOAT
	,INVTO FLOAT
	)

INSERT INTO @tempinformation1 VALUES ('A',24.5,250.2)

INSERT INTO @tempinformation2 VALUES ('A',64,87.3)
INSERT INTO @tempinformation2 VALUES ('A',104.3,122)

SELECT * FROM @tempinformation1
SELECT * FROM @tempinformation2

--Required result
SELECT 'A'[ID],24.5[INVFROM],64[INVTO],'TABLE1'[TABLE]
UNION
SELECT 'A',64,87.3,'TABLE2'
UNION
SELECT 'A',87.3,104.3,'TABLE1'
UNION
SELECT 'A',104.3,122,'TABLE2'
UNION
SELECT 'A',122,250.2,'TABLE1'

Open in new window

Avatar of sventhan
sventhan
Flag of United States of America image

select o.* from
(
SELECT * FROM @tempinformation1
union all
SELECT * FROM @tempinformation2
) 0
order by NBLINE,ID
Avatar of knightEknight
This is close, but I'm uncertain from your data above about the values in the [TABLE] column:

DECLARE @tempinformation1 TABLE (
	NBLINE INT identity
	,ID VARCHAR(20)
	,INVFROM FLOAT
	,INVTO FLOAT
	)

DECLARE @tempinformation2 TABLE (
	NBLINE INT identity
	,ID VARCHAR(20)
	,INVFROM FLOAT
	,INVTO FLOAT
	)

INSERT INTO @tempinformation1 VALUES ('A',24.5,250.2)

INSERT INTO @tempinformation2 VALUES ('A',64,87.3)
INSERT INTO @tempinformation2 VALUES ('A',104.3,122)

--SELECT * FROM @tempinformation1
--SELECT * FROM @tempinformation2

;with cte_data as (
select ID, INVFROM as data, 'TABLE1' as [TABLE] from @tempinformation1
union
select ID, INVFROM, 'TABLE2' from @tempinformation2
union
select ID, INVTO, 'TABLE1' from @tempinformation1
union
select ID, INVTO, 'TABLE2' from @tempinformation2
--order by 1
)

select /*row_number() over (order by A.data) as rownum,*/ A.ID, A.data as INVFROM, min(B.data) as INVTO, A.[TABLE]
from cte_data A
join cte_data B
  on B.data > A.data
group by A.ID, A.data, A.[TABLE]



--Required result
SELECT 'A'[ID],24.5[INVFROM],64[INVTO],'TABLE1'[TABLE]
UNION
SELECT 'A',64,87.3,'TABLE2'
UNION
SELECT 'A',87.3,104.3,'TABLE1'
UNION
SELECT 'A',104.3,122,'TABLE2'
UNION
SELECT 'A',122,250.2,'TABLE1'

Open in new window

with cte as
(select id, invfrom as inv from tmp1
 union all
 select id, invto from tmp1
 union all
 select id, invfrom from tmp2
 union all
 select id, invto from tmp2
 order by 2)
select id, inv as invfrom, nxtinv as invto from
(select id, inv,lead(inv) over (partition by id order by inv) nxtinv
 from cte)
where nxtinv is not null;
with cte as
(select id, invfrom as inv from tmp1
 union all
 select id, invto from tmp1
 union all
 select id, invfrom from tmp2
 union all
 select id, invto from tmp2
 order by 2)
select id, inv as invfrom, nxtinv as invto from
(select id, inv,lead(inv) over (partition by id order by inv) nxtinv
 from cte)
where nxtinv is not null;
with cte as
(select id, invfrom as inv from tmp1
 union all
 select id, invto from tmp1
 union all
 select id, invfrom from tmp2
 union all
 select id, invto from tmp2
 order by 2)
select id, inv as invfrom, nxtinv as invto from
(select id, inv,lead(inv) over (partition by id order by inv) nxtinv
 from cte)
where nxtinv is not null;
Sorry for the duplication, I got an error message when I first submitted, but it must have gone through anyway.
Note, if you want this for more than one id, just order the cte by 1, 2.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial