Fetch the first record with 24 hr interval based on first record timestamp

Hi Lars,

First take a look on attached sheet with the target designed to achive (on left side the behavior of second WITH posted, and the plan to reach the third one). I've also attached the script to run the sample of sheet:

The SQL below is didatic in 4 steps trying to be readable (at least I expect that)

with

lt_starter_per_record as

(select *,seconds_between(to_date(to_char(min_dt,'yyyy-mm-dd')),min_dt) as sec_starter from

(select record_number,min(create_date) as min_dt

from test_table

group by record_number)),

lt_day_range as

(select record_number,create_date,min_dt,sec_starter,dt,

case when seconds_between(dt,create_date) >= sec_starter then 0 else 1 end as ind_day_range

from

(select a.record_number,a.create_date,b.sec_starter,b.min_dt,to_date(to_char(create_date,'yyyy-mm-dd')) as dt

from test_table as a,lt_starter_per_record as b

where b.record_number = a.record_number)),

lt_compute_range as

(select record_number,create_date,

add_seconds(add_days(dt,-ind_day_range),sec_starter) as dt_range_ini

from lt_day_range),

lt_pick_the_ones as

(select record_number,create_date from

(select record_number,min(create_date) as create_date,dt_range_ini

from lt_compute_range

group by record_number,dt_range_ini))

select * from lt_pick_the_ones order by 1,2

;

After functionally work, tested also performance adding 10 mi rows in a one year range with script below:

select top 10000000

round(rand()*1000)+1000 as record_number,

add_seconds(add_days(to_date('2000-01-01'),rand()*365),round(rand()*86400,0)) as create_date

from objects,objects

into test_table;

The execution with all data with no filter was 2,5 seconds.

Statement 'with lt_starter_per_record as (select ...'

successfully executed in 2.641 seconds (server processing time: 2.407 seconds)

Fetched 1000 row(s) in 491 ms 283 µs (server processing time: 0 ms 734 µs)

This was what I said earlier, but reading your comments, and the blog and John's also I understood that there's also a need for range different than this thread (24 hours).

I guess it could be done changing the way group is calculated on lt_day_range / lt_compute_range using a formula like:

- use interval in seconds (example 1209600 seconds = 14 days)

- calculate first_date_time like this one

- compute each date subtracting first_date_time in seconds. Divide it by internal (1209600)... take the integer part

- use this integer for grouping

If it's not clear I can give a try on it later.

Best regards, Fernando Da Rós

Hi José,

Need some adjust. It produces same result from the SQL from Henrique but also doesn't work on new data. The new data is here but I'll reproduce it again here:

The inserts:

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-26 09:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-26 20:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-27 07:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-27 18:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-28 05:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-28 16:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-07-29 03:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-01 10:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-02 05:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-03 00:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-03 19:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-04 14:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-05 09:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-06 04:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-10 05:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-11 06:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-12 07:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-13 08:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-14 09:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-15 10:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-16 11:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-17 12:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-20 08:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-20 09:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-20 10:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-20 11:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-21 08:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-21 09:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-21 10:00:00');

INSERT INTO TEST_TABLE VALUES(3112,'2015-09-21 11:00:00');

Attached the excel explaining midnights:

Current SQL statement:

select 
  a.record_number, 
  min(a.create_date) as create_date 
from 
  test_table as a inner join 
  (select 
    -- get first create_date per record_number to make the grouping of series 
    record_number, 
    min(create_date) as min_dt 
  from 
    test_table 
  group by 
    record_number) as b 
  on  b.record_number = a.record_number 
group by 
  a.record_number, 
  -- compute grouping key by interval from first date with desired interval (here 24 hours = 1 day = 86400 seconds) 
  round(seconds_between(a.create_date,b.min_dt)/(1*86400),0,ROUND_DOWN) 
order by 1,2 
;

Here the expected output:

BTW: Where is the owner of thread? Former Member jump in and provide feedback, the question is yours, we are just playiing it around but your silent doesn't tell if it's help with your initial question.

Best regards, Fernando Da Rós