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

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

    ChrisOracle101

      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

          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

            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

              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
                Billy~Verreynne

                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

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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