0

Hi stackoverflow community,

I would like to achieve the following result. I am doing incremental loading. Where i load the max & last values of the previous load grouped by M_ID into the query of the next load.

I would like to start the query of the new load with the last row of the previous load per M_ID, reason is because of continious data flow in the DB

Currently i have this query for the startvalues:

With startval as (
  select
    1 as is_start, 'M1' as M_id, 'Reas1' as R1, 'Reas2' as R2, 'Na2' as N2,
    to_date('2020-02-27 18:00:00') as date_start 
  from dual
 union all
  select
    1 as is_start, 'M2' as M_id, 'Reas2' as R1, 'Reas6' as R2, 'Na3' as N2,
    to_date('2020-02-27 14:00:00') as date_start 
  from dual
),

The output query i would like to achieve would look like this:

M_id  R1     R2    N2   date_start           date_end
1     Reas1  Reas2 Na2  2020-02-27 18:00:00  2020-02-27 18:04:00 (First record taken from startval)
1     Reas1  Reas2 Na3  2020-02-27 18:04:00  2020-02-27 18:05:00
2     Reas2  Reas6 Na3  2020-02-27 14:00:00  2020-02-27 14:03:00 (First record taken from startval)
2     Reas2  Reas6 Na3  2020-02-27 14:03:00  2020-02-27 14:06:00

What is the best way to join the startval row into the first row of the actual query table?

(The query is executed in Oracle DB)

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Henkiee20
  • 75
  • 8
  • Where are those `date_end` values coming from? Please elaborate. – The Impaler Feb 28 '20 at 12:29
  • Date_start is lagged from date_end on the actual query. Thats why i would like to start with the last value of the previous load – Henkiee20 Feb 28 '20 at 12:33
  • You haven't shown 'the actual query table' so it's quite hard to tell what you mean. Please include that, with matching sample data, and explain the relationships (in your question, not in comments). You might just be looking for `lead()` but at the moment it's impossible to tell. – Alex Poole Feb 28 '20 at 12:34

0 Answers0