CODEWITHSUNDEEP
sqlpostgresql
No_name
No_name

Reputation: 2810

How to add a row to result if a condition based on two adjacent rows is true

Given a table like this

       system_time_start       |        system_time_end        | price 
-------------------------------+-------------------------------+-------
 2020-11-26 00:59:05.079162-05 | 2020-11-26 23:48:45.030761-05 |  4.35 
 2020-11-26 23:48:45.030761-05 | 2020-11-27 00:26:02.198766-05 | 13.37 
 2020-11-27 00:29:26.116951-05 | 2020-11-27 00:36:22.072045-05 |  4.35 
 2020-11-27 00:36:22.072045-05 | infinity                      | 10.35

Is it possible to get this?

       system_time_start       | price 
-------------------------------+-------
 2020-11-26 00:59:05.079162-05 |  4.35 
 2020-11-26 23:48:45.030761-05 | 13.37 
 2020-11-27 00:26:02.198766-05 |  null 
 2020-11-27 00:29:26.116951-05 |  4.35 
 2020-11-27 00:36:22.072045-05 | 10.35

Right now, I'm post processing the table with a for loop checking if system_time_start is not equal to the previous row's system_time_end, then insert another row to the result with a null price, but I'm wondering if there's a way to do this in SQL. I was thinking to use the lag function, which can do the check that I want, but inserting a row in between is what I can't figure out how to do.

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Hmmm . . . You can use not exists:

select
    system_time_start,
    price
from t
union all
select
    system_time_end,
    null
from t
where
    not exists (
        select
        from t as t2
        where t.system_time_end = t2.system_time_start
    ) and
    system_time_end != 'infinity'
order by system_time_start;

Upvotes: 1

Related Questions