Skip to Content
author's profile photo Former Member
Former Member

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

Hello Folks,

Need your help to build logic for the below requirement.

We have a requirement where we need to fetch the record posted after every 24 hours from the first record time stamp.

details about the requirement:

1) Take the first record for each order_number and corresponding time stamp.

2) take the record posted for the same order number after 24 hours.

3) take the record posted after after 24 hours based on the time stamp from 2nd record.

Below is the sample data set.

RECORD_NUMBERCREATED_DATE5675462015-07-28 10:45:245675462015-07-28 14:13:455675462015-07-28 22:53:195675462015-07-29 08:34:395675462015-07-29 17:13:265675972015-07-26 11:34:295675972015-07-27 10:45:325675972015-07-28 13:12:435675972015-07-29 18:39:51

The result should be like below.

RECORD_NUMBERCREATED_DATE5675462015-07-28 10:45:245675462015-07-29 17:13:265675972015-07-26 11:34:295675972015-07-28 13:12:435675972015-07-29 18:39:51

The records should be grouped based on record_number.

Regards,

Venkat N.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Aug 03, 2015 at 12:08 PM

    Window functions are your friends. :-)

    SELECT RECORD_NUMBER,MIN(CREATE_DATE) CREATE_DATE FROM (
    SELECT *, MIN(TO_DATE) OVER (PARTITION BY RECORD_NUMBER ORDER BY CREATE_DATE DESC) TO_DATE2 FROM (
    SELECT *, CASE WHEN DIFF > 24 THEN CREATE_DATE WHEN NEXT_DATE IS NULL THEN '2100-01-01' ELSE NULL END TO_DATE FROM (
    SELECT *, SUM(SECONDS_BETWEEN(CREATE_DATE,NEXT_DATE)/3600) OVER (PARTITION BY RECORD_NUMBER ORDER BY CREATE_DATE) DIFF FROM (
    SELECT RECORD_NUMBER, CREATE_DATE, LEAD(CREATE_DATE) OVER (PARTITION BY RECORD_NUMBER ORDER BY CREATE_DATE) NEXT_DATE
    FROM "TEST".TEST_TABLE)
    GROUP BY RECORD_NUMBER, CREATE_DATE,NEXT_DATE)))
    GROUP BY RECORD_NUMBER, TO_DATE2
    ORDER BY RECORD_NUMBER, CREATE_DATE;
    

     ;RECORD_NUMBER;CREATE_DATE               
    1;567,546      ;Jul 28, 2015 10:45:24.0 AM
    2;567,546      ;Jul 29, 2015 5:13:26.0 PM 
    3;567,597      ;Jul 26, 2015 11:34:29.0 AM
    4;567,597      ;Jul 28, 2015 1:12:43.0 PM 
    5;567,597      ;Jul 29, 2015 6:39:51.0 PM 
    
    Add a comment
    10|10000 characters needed characters exceeded

    • No no no no no! 😊

      I didn't mean to say (or imply) that you're wrong - your solution is very fast and produces, as far as I could see, the correct result.

      The fault is on my side, since I simply struggle to understand _why_ your solution is correct.

      But that's really on me and surely caused by my ridiculously short attention span, my lack of abstract thinking and my general fear of everything too technical!

      @All: guys, this is one of the most interesting and entertaining threads in a long time here on SCN.

      I'd hope there will be something like that in the future 1DX version of discussions, too.

      Thanks for that and cheers!

      - Lars

  • Posted on Jul 30, 2015 at 07:57 AM

    Please post the create table and insert statements so that we can work with your data.

    - Lars

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jul 30, 2015 at 06:23 PM

    Hello Venkat,

    Just an idea from my side..it may need data type casting and with slight adjustment in code it may work..

    DROP PROCEDURE <procedure>;

    CREATE PROCEDURE <procedure> LANGUAGE SQLSCRIPT AS

    BEGIN

    DECLARE v_var1 TIMESTAMP;

    DECLARE v_temp BIGINT ;

    DECLARE c_operator NVARCHAR(1) := '+';

    DECLARE c_24hours TIME := '24:00:00';

    DECLARE CURSOR c_cursor FOR SELECT record_number , created_date FROM table1 ORDER BY record_number

    BEGIN

    FOR cur_row as c_cursor DO

    v_var1 = cur_row.CREATED_DATE|| c_operator || c_24hours ;

    IF cur_row.RECORD_NUMBER <> v_temp THEN

    execute immediate 'INSERT INTO SCHEMA.TABLE2 ( RECORD_NUMBER, CREATED_DATE ) -- this should pick the first record for each order_number and corresponding time stamp.

    SELECT RECORD_NUMBER , CREATED_DATE FROM TABLE1

    WHERE RECORD_NUMBER ='||cur_row.RECORD_NUMBER||'

    AND CREATED_DATE = '||cur_row.CREATED_DATE||' ;

    execute immediate 'INSERT INTO SCHEMA.TABLE2 ( RECORD_NUMBER, CREATED_DATE ) -- This should pick same record number but posted after after 24 hours based on the time stamp from 2nd record

    SELECT RECORD_NUMBER , CREATED_DATE FROM TABLE1

    WHERE RECORD_NUMBER ='||cur_row.RECORD_NUMBER||'

    AND CREATED_DATE > '||V_VAR1||' ;

    v_temp = cur_row.RECORD_NUMBER;

    END FOR

    END

    Thanks

    Siva

    Message was edited by: Sivakumar Palaniswamy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 04, 2015 at 04:25 PM

    What about this solution?

    select
      record_number,
      create_date 
    from ( select
      tbl.record_number,
      tbl.create_date,
      cat.datemin,
      to_integer((seconds_between(cat.datemin, tbl.create_date)/3600)/24) as days,
      mod((seconds_between(cat.datemin, tbl.create_date)/3600), 24) as dif 
      from test_table tbl 
      inner join (select
      distinct record_number,
      min(CREATE_DATE) as datemin 
      from test_table 
      group by record_number) cat
      on tbl.record_number = cat.record_number ) 
    where dif = 0 
    or days >= 1
    
    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.