1 2 Previous Next 15 Replies Latest reply: Feb 9, 2014 4:47 AM by ChrisOracle101 RSS

Max amount of planes in the air simultaniously - giving arrival and departuetime

ChrisOracle101 Newbie
Currently Being Moderated

hi!

I use oracle 11g and I have a SQL Server table with a flight field (flightNo) and  two datetime fields (Departuredatetime, Arrivaldatetime).

How is it possible to query the max amout of planes being in the air simultaniously in a certain timeperiode? result should provide the datetime of this maximum also.


Example:

flight55:         |------|

flight67:              |--------------------|

flight69:                  |--|

flight88:                              |-----------|


result:

timestamp     no of planes

09:34             3

 

sorry I can't provide an insert because I am beginner and have only read access to the database.

 

thank you so much!

  • 1. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Hoek Guru
    Currently Being Moderated

    Ofcourse you can provide create table+ inert into statements.

    Just create your own testcase.

    Read: Re: 2. How do I ask a question on the forums?

  • 2. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Karthick_Arp Guru
    Currently Being Moderated

    Something like this

     

    SQL> create table flight_time_table
      2  (
      3    flight_no  integer
      4  , start_time date
      5  , end_time   date
      6  );

     

    Table created.

     

    SQL> alter session set nls_date_format = 'DD-MON-YYYY HH:MI:SS AM';

     

    Session altered.

     

    SQL> insert into flight_time_table(flight_no, start_time, end_time)
      2  values (1, '14-JAN-2013 12:00:00 AM', '14-JAN-2013 04:00:00 AM');

     

    1 row created.

     

    SQL> insert into flight_time_table(flight_no, start_time, end_time)
      2  values (2, '14-JAN-2013 02:00:00 AM', '14-JAN-2013 10:00:00 AM');

     

    1 row created.

     

    SQL> insert into flight_time_table(flight_no, start_time, end_time)
      2  values (3, '14-JAN-2013 05:00:00 AM', '14-JAN-2013 06:00:00 AM');

     

    1 row created.

     

    SQL> insert into flight_time_table(flight_no, start_time, end_time)
      2  values (4, '14-JAN-2013 08:00:00 AM', '14-JAN-2013 12:00:00 PM');

     

    1 row created.

     

    SQL> commit;

     

    Commit complete.

     

    SQL> select * from flight_time_table;

     

    FLIGHT_NO START_TIME              END_TIME
    ---------- ----------------------- -----------------------
             1 14-JAN-2013 12:00:00 AM 14-JAN-2013 04:00:00 AM
             2 14-JAN-2013 02:00:00 AM 14-JAN-2013 10:00:00 AM
             3 14-JAN-2013 05:00:00 AM 14-JAN-2013 06:00:00 AM
             4 14-JAN-2013 08:00:00 AM 14-JAN-2013 12:00:00 PM

     

    SQL> with input_data
      2  as
      3  (
      4    select to_date('14-JAN-2013 09:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM') input_time
      5      from dual
      6  )
      7  select input_time
      8       , count(*) flight_count
      9    from flight_time_table
    10       , input_data
    11   where input_time between start_time and end_time
    12   group
    13      by input_time;

     

    INPUT_TIME              FLIGHT_COUNT
    ----------------------- ------------
    14-JAN-2013 09:00:00 AM            2

     

    SQL>

     

    > sorry I can't provide an insert because I am beginner and have only read access to the database.

     

    I have a personal computer which has Oracle in it. Guess what, I am the BOSS there!!

  • 3. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Etbin Guru
    Currently Being Moderated

    with

    flights as

    (select 'flight55' filght_no,

            to_date('20140117 10:20','yyyymmdd hh24:mi') departure,

            to_date('20140117 12:45','yyyymmdd hh24:mi') arrival

       from dual union all

    select 'flight67',to_date('20140117 06:20','yyyymmdd hh24:mi'),to_date('20140117 18:50','yyyymmdd hh24:mi') from dual union all

    select 'flight69',to_date('20140117 11:30','yyyymmdd hh24:mi'),to_date('20140117 12:25','yyyymmdd hh24:mi') from dual union all

    select 'flight88',to_date('20140117 12:20','yyyymmdd hh24:mi'),to_date('20140117 16:35','yyyymmdd hh24:mi') from dual

    ),

    periods as

    (select 1 rid,to_date('20140117 08:20','yyyymmdd hh24:mi') p_start,to_date('20140117 13:15','yyyymmdd hh24:mi') p_end from dual union all

    select 2 ,to_date('20140117 13:00','yyyymmdd hh24:mi'),to_date('20140117 17:00','yyyymmdd hh24:mi') from dual

    )

    select p.rid,count(*) in_air

      from periods p,

           flights f

    where greatest(p.p_start,f.departure) <= least(p.p_end,f.arrival)

    group by p.rid

     

    RIDIN_AIR
    14
    22

     

    Regards

     

    Etbin

  • 4. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Insufficient data... as it also depends whether these are IFR or VFR flights, which J or V routes are flown, SIDs and STARs used, separation rules, and so on.

  • 5. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    The solutions above tell you how many planes were in the air at given times.

    If you want to know the maximum number of planes were in the air, and when that occurred, without giving any times, here's one way to do it:

     

    WITH  got_in_air_cnt   AS

    (

        SELECT    start_time

        ,         LEAD (start_time) OVER (ORDER BY start_time)  AS end_time

        ,         SUM (SUM (net_change))

                                    OVER (ORDER BY start_time)  AS in_air_cnt

        FROM      flights

        UNPIVOT   (    start_time

                  FOR  net_change  IN  ( departure  AS  1

                                       , arrival    AS -1

                                       )

                  )

        GROUP BY  start_time

    )

    ,    got_rnk    AS

    (

        SELECT  a.*

        ,       RANK () OVER (ORDER BY  in_air_cnt  DESC)  AS rnk

        FROM    got_in_air_cnt  a

    )

    SELECT    start_time, end_time, in_air_cnt

    FROM      got_rnk

    WHERE     rnk  = 1

    ORDER BY  start_time

    ;

  • 6. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Marwim Expert
    Currently Being Moderated

    Hello Karthik,

     

    your query might be improved :-)

    1111111                -- flights

        2222222222222222

            333

                  444444444

    12  2 4 5 6 7 8 9 10 12 -- hours


    Your sample data shows 3 maxima

     

    We can extract the time slots we have and then count the airplanes

     

    WITH time_slots AS (

        SELECT  DISTINCT (a.start_time)

              ,(SELECT MIN(b.end_time)

                FROM    flight_time_table b

                WHERE  a.start_time < b.end_time

                ) end_time

        FROM    flight_time_table a

        )

    SELECT  time_slots.start_time

          ,time_slots.end_time

          ,COUNT(*) flight_count

    FROM    flight_time_table

            CROSS JOIN time_slots

    WHERE  time_slots.start_time

            BETWEEN flight_time_table.start_time

            AND flight_time_table.end_time

    GROUP BY time_slots.start_time

            ,time_slots.end_time;

     

    START_TIME              END_TIME                FLIGHT_COUNT

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

    14-JAN-2013 05:00:00 AM  14-JAN-2013 06:00:00 AM            2

    14-JAN-2013 08:00:00 AM  14-JAN-2013 10:00:00 AM            2

    14-JAN-2013 02:00:00 AM  14-JAN-2013 04:00:00 AM            2

    14-JAN-2013 12:00:00 AM  14-JAN-2013 04:00:00 AM            1

     

    So we have three timeslots with 2 airplanes in the air.

     

    Regards

    Marcus

  • 7. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Marwim Expert
    Currently Being Moderated

    Maybe for a complete solution some Tabibitosan is needed :-)

    Otherwise consecutive time slots will be shown as distinct rows.

     

    Let's modify Karthik's example data (flight 1 arrives one our later)

     

    DROP TABLE flight_time_table;
    CREATE TABLE flight_time_table
    (
      flight_no  INTEGER
    , start_time DATE
    , end_time   DATE
    );

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS AM';
    INSERT INTO flight_time_table(flight_no, start_time, end_time)
    VALUES (1, '14-JAN-2013 12:00:00 AM', '14-JAN-2013 05:00:00 AM');
    INSERT INTO flight_time_table(flight_no, start_time, end_time)
    VALUES (2, '14-JAN-2013 02:00:00 AM', '14-JAN-2013 10:00:00 AM');
    INSERT INTO flight_time_table(flight_no, start_time, end_time)
    VALUES (3, '14-JAN-2013 05:00:00 AM', '14-JAN-2013 06:00:00 AM');
    INSERT INTO flight_time_table(flight_no, start_time, end_time)
    VALUES (4, '14-JAN-2013 08:00:00 AM', '14-JAN-2013 12:00:00 PM');
    COMMIT;

     

    WITH  got_in_air_cnt  AS (

        SELECT    start_time

                 ,LEAD (start_time) OVER (ORDER BY start_time)  AS end_time

                 ,SUM (SUM (net_change))

                     OVER (ORDER BY start_time)  AS in_air_cnt

        FROM      flight_time_table

        UNPIVOT   (    start_time

                  FOR  net_change  IN  ( start_time  AS  1

                                       , end_time    AS -1

                                       )

                  )

        GROUP BY  start_time

        )

    ,numbered_count AS(

        SELECT  start_time

               ,end_time

               ,in_air_cnt

               ,CASE WHEN start_time <> LAG(end_time,1,DATE '0001-01-01') OVER (ORDER BY start_time)

                  OR in_air_cnt <> NVL(LAG(in_air_cnt,1,NULL) OVER (ORDER BY start_time),in_air_cnt)

                THEN

                    ROW_NUMBER() OVER (ORDER BY start_time)

                END rn

        FROM    got_in_air_cnt

        )

    ,grouped_count AS(

        SELECT  start_time

               ,end_time

               ,in_air_cnt

               ,MAX(rn) OVER (ORDER BY start_time) grp

        FROM    numbered_count

        )

    ,got_rnk AS (

        SELECT  MIN(start_time) start_time

               ,MAX(end_time) end_time

               ,in_air_cnt

               ,RANK () OVER (ORDER BY  in_air_cnt  DESC)  AS rnk

        FROM    grouped_count

        GROUP BY grp

                ,in_air_cnt

        )

    SELECT  start_time

           ,end_time

           ,in_air_cnt

    FROM    got_rnk

    WHERE   rnk  = 1

    ORDER BY  start_time

     

    START_TIME               END_TIME                 IN_AIR_CNT

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

    14-JAN-2013 02:00:00 AM  14-JAN-2013 06:00:00 AM           2

    14-JAN-2013 08:00:00 AM  14-JAN-2013 10:00:00 AM           2

     

    I use the max-on-case-row-number version of Tabibitosan from About Oracle: Tabibitosan

     

    Regards

    Marcus

  • 8. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Solomon Yakobson Guru
    Currently Being Moderated

    Marwim wrote:

     

    Maybe for a complete solution some Tabibitosan is needed :-)

     

    You got 2 rows back, while you should get back 3 rows:

     

    with t1 as (

                 select  start_time dt,

                         1 weight

                   from  flight_time_table

                union all

                 select  end_time dt,

                         -1 weight

                   from  flight_time_table

               ),

         t2 as (

                select  dt,

                        sum(weight) over(order by dt) plane_count

                  from  t1

               ),

         t3 as (

                select  dt,

                        plane_count,

                        max(plane_count) over() max_plane_count

                  from  t2

               )

    select  dt,

            plane_count

      from  t3

      where plane_count = max_plane_count

    /


    DT                      PLANE_COUNT
    ----------------------- -----------
    14-JAN-2013 02:00:00 AM           2
    14-JAN-2013 05:00:00 AM           2
    14-JAN-2013 08:00:00 AM           2

    SQL>

     

    SY.

  • 9. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Frank Kulash Guru
    Currently Being Moderated

    Hi, Marcus,

     

    Good point!  If N planes happen to take off at the exact same time as another N planes land, then you'd probably want to consider that time as the middle of a period, not the end of one period and the beginning of another.

    Using fixed differences, you can do that without any additional sub-queries, just the 2 I used earlier:

    WITH  got_in_air_cnt   AS

    (

        SELECT    start_time

        ,         LEAD (start_time) OVER (ORDER BY start_time)  AS end_time

        ,         SUM (SUM (net_change))

                                    OVER (ORDER BY start_time)  AS in_air_cnt

        ,         ROW_NUMBER ()     OVER (ORDER BY start_time)  AS r_overall

        FROM      flight_time_table

        UNPIVOT   (    start_time

                  FOR  net_change  IN  ( start_time  AS  1

                                       , end_time    AS -1

                                       )

                  )

        GROUP BY  start_time

    )

    ,    got_rnk    AS

    (

        SELECT  a.*

        ,       RANK () OVER (ORDER BY  in_air_cnt  DESC)  AS rnk

        ,       r_overall - ROW_NUMBER () OVER ( PARTITION BY  in_air_cnt

                                                 ORDER BY      start_time

                                               )   AS grp_num

        FROM    got_in_air_cnt  a

    )

    SELECT    MIN (start_time)    AS period_start

    ,         MAX (end_time)      AS period_end

    ,         in_air_cnt

    FROM      got_rnk

    WHERE     rnk  = 1

    GROUP BY  in_air_cnt, grp_num

    ORDER BY  period_start

    ;

    Output from the sample data you posted (where one plane lands and another takes off at 05:00:00 AM):

    PERIOD_START            PERIOD_END              IN_AIR_CNT

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

    14-JAN-2013 02:00:00 AM 14-JAN-2013 06:00:00 AM          2

    14-JAN-2013 08:00:00 AM 14-JAN-2013 10:00:00 AM          2

  • 10. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    ChrisOracle101 Newbie
    Currently Being Moderated

    hi!

    thank you for the many responses. I think the solution of Frank is that one which delivers exactly that what I need. I will go through your recommandations and will try to understand them (the tabibitosan stuff sounds very interesring) and will tell you about my progress.

     

    I wish you a nice weekend

    thanks!

    christian

  • 11. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Marwim Expert
    Currently Being Moderated

    Thanks for the question, an ideal break from monotonous testing :-)

  • 12. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Marwim Expert
    Currently Being Moderated

    Solomon Yakobson wrote:

     

    Marwim wrote:

     

    Maybe for a complete solution some Tabibitosan is needed :-)

     

    You got 2 rows back, while you should get back 3 rows:

     

     

    Did you use the modified data? I extended flight 1 until 5 AM

     

    11111111              -- flights

        2222222222222222

            333

                  444444444

    12  2 4 5 6 7 8 9 10 12 -- hours

    Then my solution tries to merge the intervals [2 - 5] and [5 - 6] into a single interval [2 - 6]

  • 13. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    ChrisOracle101 Newbie
    Currently Being Moderated

    Hi guys,

     

    Thank you - work perfect for me.

     

    But why is the need for sum(sum(netchange)) and sum(netchange) is not enough?

     

    How would it be possible to define equal-sized timeperiods independent from the timeperiods from the flights-table?

    I would like to have result with the in_air_cnt for every 5 min of the day, even the next landing of a plane is f.e. 30min later?

     

    f.e.

    01.01.2014 00:05 Am --  1

    01.01.2014 00:10 Am --  1

    01.01.2014 00:15 AM --  0

    01.01.2014 00:20 AM --  0

    01.01.2014 00:25 AM --  1

     

    I´ve tried already the answer to this but then the result is not valid anymore..

     

    Thank you, nice Weekend

    Chris

  • 14. Re: Max amount of planes in the air simultaniously - giving arrival and departuetime
    Frank Kulash Guru
    Currently Being Moderated

    Hi, Chris,

    ChrisOracle101 wrote:

     

    Hi guys,

     

    Thank you - work perfect for me.

     

    But why is the need for sum(sum(netchange)) and sum(netchange) is not enough?

     

    How would it be possible to define equal-sized timeperiods independent from the timeperiods from the flights-table?

    I would like to have result with the in_air_cnt for every 5 min of the day, even the next landing of a plane is f.e. 30min later?

     

    f.e.

    01.01.2014 00:05 Am --  1

    01.01.2014 00:10 Am --  1

    01.01.2014 00:15 AM --  0

    01.01.2014 00:20 AM --  0

    01.01.2014 00:25 AM --  1

     

    I´ve tried already the answer to this but then the result is not valid anymore..

     

    Thank you, nice Weekend

    Chris

    In this problem, we want to know how many planes are in the air at any point in time.

    SUM (netchange) tells us the incremental difference in that number. For example, it may say that, at 00:15, the number decreased by 1.  That, by itself, is not what we're interested in.  We want to know the cumulative effect of all those incremental changes over time, which is what  SUM (SUM (netchange)) tells us.

    (Both you and I are speaking a little loosely here.  When we talk about  SUM (netchange), it can only be understood in the context of the corresponding GROUP BY clause, and SUM (SUM (netchange)) can only be understood in the context of the outer SUM's analytic clause, as well.)

     

    As for the 5-minute problem, it's unclear what you're asking.  A number of different things fit your description equally well.

    I think it would be best if you started a new thread for your new problem.   Include a little sample data (either CREATE TABLE and INSERT statements, or a WITH clause), and the exact results you want from that sample data.  Include at least 1 5-minute period with a lot of activity, e.g, 1 plane taking off at 00:06, 1 taking off plus one landing at 00:07, 1 landing at 00:08.

1 2 Previous Next