CODEWITHSUNDEEP
sqlpostgresqlrange
v.montag
v.montag

Reputation: 91

override range with postgres

I have a problem with a table of KM ranges and a "override" table. The start and the end of the override can be between the ranges of Table T1. For example

T1
from    to      option
-1.4    1.7     A
1.7     4.2     B
4.2     4.6     A
4.6     5.3     B

Override
T2
1.2     4.5     C

The problem is the line 1.7 to 4.2 from T1, this line needs to be "deleted". My last version can only handle overrides between two rows, not over 3 rows and i have no idea how i can fix it.

my last version on dbfiddle: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=bc71d293c112729fe8d3b077b377ea92

but it should be:

Result

from    to      option
-1.4    1.2     A
1.2     4.5     C
4.5     4.6     A
4.6     5.3     B

Upvotes: 1

Views: 101

Answers (2)

v.montag
v.montag

Reputation: 91

so with the help from Vao Tsun the complete code must be

with help1 as (
select t1.* from t1
left outer join t2 on t2.fromkm < t1.fromkm and t2.tokm > t1.tokm
where t2.tokm is null
union all
select * from t2
order by fromkm)

,nummer as (
select row_number() over (order by fromkm) as lfdnr,fromkm,tokm,option,comment from help1 )

select 
case when a.fromkm<c.tokm and c.comment='override' then c.tokm else a.fromkm end as fromnew,
case when a.tokm>b.fromkm and a.comment!='override' then b.fromkm else a.tokm end as tonew,
a.option,a.comment from nummer a
left join nummer b on a.lfdnr+1=b.lfdnr
left join nummer c on a.lfdnr=c.lfdnr+1

order by a.fromkm;

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51519

I'm not sure I got the question right. Here I "remove" from t1 where t2 from and to overlaps t1 and then just add t2:

t=# select t1.* from t1
left outer join t2 on t2.fromkm < t1.fromkm and t2.tokm > t1.tokm
where t2.tokm is null
union all
select * from t2
t-# order by fromkm;
 fromkm | tokm | option |  comment
--------+------+--------+------------
   -1.4 |  1.7 | A      | normal
    1.2 |  4.5 | C      | override
    4.2 |  4.6 | A      | normal
    4.6 |  5.3 | B      | normal
(4 rows)

Upvotes: 1

Related Questions