5 Replies Latest reply on Jun 17, 2019 5:01 PM by jaramill

    TIMES MERGE

    Jelly

        WITH A AS

          (

          SELECT TO_DATE('2019-06-16 0300','yyyy-MM-DD HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 0330','yyyy-MM-DD HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 0400','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 1600','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 1630','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 2300','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 2330','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0000','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0030','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0100','yyyy-MM-DD  HH24:MI') YMD from dual

          )

       

       

          SELECT * FROM A

        

       

      I WISH RESULT SELECT

       

      STARTDATE       STARTTIME     ENDDATE         ENDTIME

      2019-06-16             0300                2019-06-16          0400

      2019-06-16             1600                 2019-06-16         1630

      2019-06-16             2300                  2019-16-17         0000

      2019-06-17             0000                  2019-16-17         0100

        • 1. Re: TIMES MERGE
          Stew Ashton

          I am assuming you want to merge together datetimes that are all on the same day and that differ by exactly 30 minutes. One exception: if there is a datetime that is at 23:30 and the next datetime is at midnight, the end datetime should be midnight.

           

          WITH A AS (
            SELECT TO_DATE('2019-06-16 0300','yyyy-MM-DD HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 0330','yyyy-MM-DD HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 0400','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 1600','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 1630','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 2300','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-16 2330','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-17 0000','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-17 0030','yyyy-MM-DD  HH24:MI') YMD from dual
            UNION ALL
            SELECT TO_DATE('2019-06-17 0100','yyyy-MM-DD  HH24:MI') YMD from dual
          ) 
          SELECT * FROM A
          match_recognize(
            order by ymd
            measures first(ymd) start_date,
              case when next(ymd) = trunc(next(ymd)) then next(ymd)
                else last(ymd)
              end end_date
            pattern(a+)
            define a as count(*) = 1
              or (trunc(ymd) = trunc(prev(ymd)) and ymd = prev(ymd) + interval '30' minute)
          );
          
          START_DATE       END_DATE        
          ---------------- ----------------
          2019-06-16 03:00 2019-06-16 04:00
          2019-06-16 16:00 2019-06-16 16:30
          2019-06-16 23:00 2019-06-17 00:00
          2019-06-17 00:00 2019-06-17 01:00
          

           

          Best regards,

          Stew Ashton

           

          P.S. to format the output as you wish:

           

          SELECT to_char(start_date, 'yyyy-mm-dd') start_date,
            to_char(start_date, 'hh24:mi') start_time,
            to_char(end_date, 'yyyy-mm-dd') end_date,
            to_char(end_date, 'hh24:mi') end_time
          FROM A
          match_recognize(
            order by ymd
            measures first(ymd) start_date,
              case when next(ymd) = trunc(next(ymd)) then next(ymd)
                else last(ymd)
              end end_date
            pattern(a+)
            define a as count(*) = 1
              or (trunc(ymd) = trunc(prev(ymd)) and ymd = prev(ymd) + interval '30' minute)
          );
          
          • 2. Re: TIMES MERGE
            mathguy

            The solution isn't quite right. No matter what the last row for a given date is, if the next row is midnight of the following day, then that midnight will be the ENDDATE for the previous group. Indeed, in your code you only test to see if the next row has time of day equal to midnight, but you don't test to see also that the last row of the current match has time of day of 23:30.

            • 3. Re: TIMES MERGE
              jaramill

              Another post about TIME??  Yet you don't answer the other ones?? --> TIMES

              And AGAIN you fail to post your Oracle database version, that you're asked of.

               

              Do you plan on LEARNING about SQL ever??

               

              These are the threads you leave unanswered:

               

              • 4. Re: TIMES MERGE
                mathguy

                Here is a solution that will work in earlier versions of Oracle. (MATCH_RECOGNIZE requires Oracle 12.1 or higher.) It uses the Tabibitosan method to identify the groups.

                 

                I make the same assumption as Stew in Reply 1:  If on a given date you have a row with time-of-day 23:30, and the row with midnight (00:00) on the next day is also present, then the earlier group ends at midnight on the "next" day. Then a group exists for the row with time-of-day of midnight, separate from the previous group, EVEN IF NO OTHER ROWS exist on that date. Your test data didn't include an illustration of that case, and you didn't explain the problem in words, so we don't know the desired handling in that case. I added three more rows at the bottom of the test data; I am referring specifically to the last two rows in the input, and last two rows in the output.

                 

                One more thing: if you don't use : in the time-of-day component, don't use it in the format model either. The best way to match 0330 is hh24mi, rather than hh24:mi. Oracle is forgiving, it will understand what you mean, but in general it's not a good practice to rely on such "forgiveness".

                 

                with a as (

                    select to_date('2019-06-16 0300', 'yyyy-mm-dd hh24mi') ymd from dual union all

                    select to_date('2019-06-16 0330', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-16 0400', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-16 1600', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-16 1630', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-16 2300', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-16 2330', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-17 0000', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-17 0030', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-17 0100', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-17 0400', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-17 2330', 'yyyy-mm-dd hh24mi')     from dual union all

                    select to_date('2019-06-18 0000', 'yyyy-mm-dd hh24mi')     from dual

                  )

                select to_char(trunc(ymd), 'yyyy-mm-dd')           startdate,

                       to_char(min(ymd), 'hh24mi')                 starttime,

                       to_char(max(ymd) + max(flag), 'yyyy-mm-dd') enddate,

                       case max(flag) when 1 then '0000' else to_char(max(ymd), 'hh24mi') end endtime

                from   ( select ymd, ymd - row_number() over (order by ymd) * interval '30' minute grp,

                                case when ymd = trunc(ymd) + interval '23:30' hour to minute

                                      and lead(ymd) over (order by ymd) = ymd + interval '30' minute then 1 else 0 end flag

                         from   a

                       )

                group  by trunc(ymd), grp

                order  by trunc(ymd), grp

                ;

                 

                STARTDATE  STARTTIME ENDDATE    ENDTIME

                ---------- --------- ---------- ---------

                2019-06-16 0300      2019-06-16 0400  

                2019-06-16 1600      2019-06-16 1630  

                2019-06-16 2300      2019-06-17 0000  

                2019-06-17 0000      2019-06-17 0100  

                2019-06-17 0400      2019-06-17 0400  

                2019-06-17 2330      2019-06-18 0000

                2019-06-18 0000      2019-06-18 0000  

                • 5. Re: TIMES MERGE
                  jaramill

                  mathguy wrote:

                   

                  Here is a solution that will work in earlier versions of Oracle. (MATCH_RECOGNIZE requires Oracle 12.1 or higher.) It uses the Tabibitosan method to identify the groups.

                  Unfortunately the OP (Jelly) never posts his Oracle database version (as he refuses to follow the guidelines) but I remember in past posts that he's on Oracle 11g (not sure which version since he never answers that question).

                   

                  mathguy wrote:

                   

                  One more thing: if you don't use : in the time-of-day component, don't use it in the format model either. The best way to match 0330 is hh24mi, rather than hh24:mi. Oracle is forgiving, it will understand what you mean, but in general it's not a good practice to rely on such "forgiveness".G

                  Good point about that as I noticed it too