Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    10

    Unanswered: Selection between two times

    Hi folks,

    Just a quick question - I want to know how I can select records that are between two certain times and dates.

    For instance, say I have a load of records with all sorts of dates and times in ATD and ATA fields. The ATD and ATA fields are stored as a DATE datatype in Oracle (9i by the way). These fields represent a departure time and an arrival time.

    I want to select all of the records that fall outside of 14:00 and 17:30 on 11/12/2006. Somehow I need to determine if this specific date and timerange fall OUTSIDE a given ETD and ATD combination, but I am lost as to how.


    Eg Sample data

    #1 -- ATD: 11/12/06 09:30 -- ATA: 11/12/06 13:30 (OK)
    #2 -- ATD: 11/12/06 14:30 -- ATA: 11/12/06 16:30 (Falls between time range, omit from results)
    #3 -- ATD: 11/12/06 13:00 -- ATA: 11/12/06 15:30 (Falls between time range, omit from results)
    #4 -- ATD: 11/12/06 07:15 -- ATA: 11/12/06 11:00 (OK)

    How would I go about this?

    Thanks
    Last edited by chartster; 09-26-06 at 22:46.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    SELECT * FROM AIRPORT
    WHERE atd  BETWEEN TO_DATE('11.12.2006. 14:00', 'dd.mm.yyyy. hh24:mi')
                   AND TO_DATE('11.12.2006. 17:30', 'dd.mm.yyyy. hh24:mi')
       OR ata  BETWEEN TO_DATE('11.12.2006. 14:00', 'dd.mm.yyyy. hh24:mi')
                   AND TO_DATE('11.12.2006. 17:30', 'dd.mm.yyyy. hh24:mi')

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Litlefoot, woulnd't that OR be an AND instead ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, my mistake ... this query returns those that should be omited

    Correctly written code should have AND with NOT BETWEEN:
    Code:
    SQL> SELECT * FROM AIRPORT
      2  WHERE atd NOT BETWEEN TO_DATE('11.12.2006. 14:00', 'dd.mm.yyyy. hh24:mi')
      3                    AND TO_DATE('11.12.2006. 17:30', 'dd.mm.yyyy. hh24:mi')
      4    AND ata NOT BETWEEN TO_DATE('11.12.2006. 14:00', 'dd.mm.yyyy. hh24:mi')
      5                    AND TO_DATE('11.12.2006. 17:30', 'dd.mm.yyyy. hh24:mi');
    
            ID ATD      ATA
    ---------- -------- --------
             1 11.12.06 11.12.06
             4 11.12.06 11.12.06
    
    SQL>
    Thank you, JMartinez; sorry for the mistake, Chartster.

  5. #5
    Join Date
    Aug 2006
    Posts
    10
    Not a problem, I will give it a shot and see how I travel.

    Thanks v. much for the help Littlefoot

  6. #6
    Join Date
    Aug 2006
    Posts
    10
    Ok here is my query as it stands (Please note I have changed the dates searched so I can return some test data to illustrate):

    Code:
    SELECT  bs.e_lastname, h.callsign
    FROM 	bs_employee bs,
    	helicopter h,
    	charter c,
    	(SELECT c.pilot as PILOT, h.callsign as CALLSIGN
    	FROM Charter_Leg l, Charter c, helicopter h
    	WHERE l.atd BETWEEN TO_DATE('25/SEP/2006 14:00', 'dd/mon/yyyy hh24:mi')
    	AND TO_DATE('25/SEP/2006 17:30', 'dd/mon/yyyy hh24:mi')
    	AND l.ata BETWEEN TO_DATE('25/SEP/2006 14:00', 'dd/mon/yyyy hh24:mi')
    	AND TO_DATE('25/SEP/2006 17:30', 'dd/mon/yyyy hh24:mi')
    	AND c.charter_nbr = l.charter_nbr
    	AND c.helicopter = h.callsign
    	GROUP BY c.pilot, h.callsign) Q1	
    WHERE	bs.employee_nbr NOT IN Q1.PILOT
    AND	h.callsign NOT IN Q1.CALLSIGN
    AND	c.pilot = bs.employee_nbr
    AND	c.helicopter = h.callsign
    GROUP BY bs.e_lastname, h.callsign;
    Soooo close, but some bizarre behaviour

    Code:
    E_LASTNAME      CALLSI
    --------------- ------
    Gamgee          NXD205
    O'Brien         NXD205
    O'Brien         RGX240
    Goldberry       AQX49J
    Brandybuck      YRA766
    Note my silly test data

    I have tried doing "SELECT DISTINCT" and "SELECT UNIQUE" to stop things from reduplicating, do no avail. I am so close by just cannot fix this issue: I just need each name and callsign *once* - LOL

    Sorry for being the idiot, as I am new to SQL, but I am so close to doing it!
    Last edited by chartster; 09-28-06 at 07:18.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I modified your query; removed the huge subquery from the FROM clause (there's no need to keep it there as "q1" doesn't have any columns among selectable columns of the SELECT statement), as well as GROUP BY's:
    Code:
    SELECT DISTINCT bs.e_lastname, h.callsign
      FROM bs_employee bs, helicopter h, charter c
     WHERE c.pilot = bs.employee_nbr
       AND c.helicopter = h.callsign
       AND (bs.employee_nbr, h.callsign) NOT IN (
              SELECT c.pilot, h.callsign
                FROM charter_leg l, charter c, helicopter h
               WHERE l.atd BETWEEN TO_DATE ('25/SEP/2006 14:00',
                                            'dd/mon/yyyy hh24:mi'
                                           )
                               AND TO_DATE ('25/SEP/2006 17:30',
                                            'dd/mon/yyyy hh24:mi'
                                           )
                 AND l.ata BETWEEN TO_DATE ('25/SEP/2006 14:00',
                                            'dd/mon/yyyy hh24:mi'
                                           )
                               AND TO_DATE ('25/SEP/2006 17:30',
                                            'dd/mon/yyyy hh24:mi'
                                           )
                 AND c.charter_nbr = l.charter_nbr
                 AND c.helicopter = h.callsign)
    Your result set contains distinct pairs of pilot's name and call sign. As you said that this isn't correct, which combination of those IS correct? For example, did O'Brien fly on MXD205 or RGX240?
    Code:
    Gamgee          NXD205
    O'Brien         NXD205
    O'Brien         RGX240

  8. #8
    Join Date
    Aug 2006
    Posts
    10
    Littlefoot, I am trying to find out not the distinct combinations, but basically a list of the pilots and the helicopters.

    Your improved query shows this

    Code:
    E_LASTNAME      CALLSI
    --------------- ------
    Brandybuck      YRA766
    Gamgee          NXD205
    Goldberry       AQX49J
    O'Brien         NXD205
    O'Brien         RGX240
    Urukhai         YRA766
    I.e. O'Brien should only appear once (even if he flies different helicopters) and helicopter NXD205 should only appear once, too.

    Would creating a VIEW solve this problem?
    Last edited by chartster; 09-28-06 at 23:11.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, such a query will return distinct pilots and helicopters, but - is this what you want?
    Code:
    SELECT DISTINCT name, NULL call FROM PILOT
    UNION
    SELECT DISTINCT NULL name, call FROM PILOT

  10. #10
    Join Date
    Aug 2006
    Posts
    10
    Sorry Littlefoot,

    I know what you mean by distinct pilots and helicopters, but maybe I should have said that the results shown should only show a pilot's name once, and a helicopter once. That is the crux of the problem.

    Something like (taking the results from above)
    Code:
    E_LASTNAME      CALLSI
    --------------- ------
    Brandybuck      YRA766
    Gamgee          NXD205
    Goldberry       AQX49J
    O'Brien         RGX240
    Urukhai         -
    Would an outer join achieve this?

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; unfortunately, I'm too dumb to know how to write such a query. PL/SQL is another option (I guess I'd know how to do that), but pure SQL - I'm sorry, but I can't help you.

  12. #12
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    chartster, I'll be glad to help if you provide us with some CREATE TABLE and INSERTS statements for the data. Or at least, show us the data you have and the result you want.

    At a glance, I would say that you can, from sqlplus, break on e_lastname. If you are not on sqlplus, you could..
    Code:
    case when nvl( lag( E_LASTNAME ) over( order by rownum ), ' ' ) <> E_LASTNAME
         then E_LASTNAME
     end E_LASTNAME

  13. #13
    Join Date
    Aug 2006
    Posts
    10
    Quote Originally Posted by JMartinez
    chartster, I'll be glad to help if you provide us with some CREATE TABLE and INSERTS statements for the data. Or at least, show us the data you have and the result you want.

    At a glance, I would say that you can, from sqlplus, break on e_lastname. If you are not on sqlplus, you could..
    Code:
    case when nvl( lag( E_LASTNAME ) over( order by rownum ), ' ' ) <> E_LASTNAME
         then E_LASTNAME
     end E_LASTNAME
    Thank you JMartinez.

    I played around with the code you supplied, and I have satisfactory results now. Talk about a challenge!

    Littlefoot - Thanks again for all of your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •