CODEWITHSUNDEEP
sqloracle-database
Henkiee20
Henkiee20

Reputation: 87

insert value between two dates

I have the following table made from with a query.

m_id s_s                    s_e                 a_s_t
101  2019-12-23 13:08:49  2019-12-23 13:13:12   NS
101  2019-12-23 13:14:35  2019-12-23 13:14:56   MS

Desired table

m_id s_s                    s_e                 a_s_t
101  2019-12-23 13:08:49  2019-12-23 13:13:12   NS
**101  2019-12-23 13:13:12  2019-12-23 13:14:35   UP**
101  2019-12-23 13:14:35  2019-12-23 13:14:56   MS

How can i insert the UP between NS and MS (a_s_t column).

The UP s_s should be the s_s of the previous row and the UP s_e the s_s of the next row

Upvotes: 0

Views: 431

Answers (3)

Popeye
Popeye

Reputation: 35930

It means you need to fill the gap.

Use following query:

Insert into your_table
Select m_id, s_e, lss, 'UP' from
(Select t.*, 
        lead(s_s) over (partition by m_id order by s_s) as lss
  From your_table t)
Where s_e <> lss

Cheers!!

Upvotes: 0

gsalem
gsalem

Reputation: 2028

Try this:

with firstq as (
    select m_id ,s_s,s_e,a_s_t, lead(s_s,1) over (partition by m_id order by s_s) next_s_s
    from table_x)
select m_id,s_s,s_e,a_s_t
from firstq
union all
select m_id, s_e,next_s_s,'UP'
from firstq
where next_s_s is not null
order by m_id, s_s

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can do this:

insert into t (m_id, s_s, s_e, a_s_t)
    select m_id, min(s_e), max(s_s), 'UP'
    from t
    group by m_id;

This does not insert the new row "between" the other two rows, because that does not make sense. SQL tables represent unordered sets.

It does insert a row with the values you want.

Upvotes: 0

Related Questions