Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Maximum Date and thereafter

    Hello

    I was wondering if someone could kindly advise me on how to write some syntax

    I have an audit trail where someone may have numerous entries by date

    I want to write the correct sytnax that if the maximum date is greater than the hard coded dates, then I should not be able to return any results

    SELECT
    MAX(DATE)
    FROM AUDIT_TABLE
    WHERE DATE IN ('30/07/2012', '31/07/2012')

    I.e. if this person had a DATE that was beyond 31/07/2012 then I will not return any results.

    I only want to show results where the absolute maximum is either '30/07/2012or 31/07/2012

    Can anyone advise?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is bad thing to use RESERVED WORD "DATE" as column name


    SELECT
    MAX(DATE)
    FROM AUDIT_TABLE
    WHERE DATE IN ('30/07/2012', '31/07/2012')
    and DATE > '31/07/1012'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    1. someone may have numerous entries by date
      So you would either be providing a filter by user, or be grouping by user.
    2. if the maximum date is greater than the hard coded dates, then I should not be able to return any results
      So, if user A has an events on July 5th 2012 but also has events on August 3rd 2012, then the query should return null.
      On the other hand, if user B has events on July 10th 2012 but no events after July 31st 2012, then the query should return July 10th 2012.
    3. show results where the absolute maximum is either '30/07/2012or 31/07/2012
      I don't understand this logic. I would assume then that the limiting date is simply July 31st 2012?

    According to my understanding above, I would say your solution looks like so:
    Code:
    dayneo@RMSD> create table audit_table (
      2    aud_event  varchar2(32),
      3    event_user varchar2(32),
      4    event_date date
      5  )
      6  /
    
    Table created.
    
    dayneo@RMSD> create index audit_table_idx01 on audit_table(
      2  	 trunc(event_date)
      3  )
      4  /
    
    Index created.
    
    dayneo@RMSD> begin
      2    insert into audit_table
      3    values('EVT1', 'A',
      4  	      to_date('05/07/2012 07:30', 'dd/mm/yyyy hh24:mi'));
      5    insert into audit_table
      6    values('EVT2', 'A',
      7  	      to_date('05/07/2012 13:00', 'dd/mm/yyyy hh24:mi'));
      8    insert into audit_table
      9    values('EVT3', 'A',
     10  	      to_date('03/08/2012 08:45', 'dd/mm/yyyy hh24:mi'));
     11    insert into audit_table
     12    values('EVT4', 'B',
     13  	      to_date('10/07/2012 00:00', 'dd/mm/yyyy hh24:mi'));
     14    insert into audit_table
     15    values('EVT5', 'B',
     16  	      to_date('10/07/2012 03:10', 'dd/mm/yyyy hh24:mi'));
     17    commit;
     18  end;
     19  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@RMSD> SELECT MAX(EVENT_DATE) FROM AUDIT_TABLE
      2  WHERE EVENT_USER = 'A'
      3  HAVING MAX(TRUNC(EVENT_DATE)) <= TO_DATE('31/07/2012', 'DD/MM/YYYY')
      4  /
    
    no rows selected
    
    dayneo@RMSD> SELECT MAX(EVENT_DATE) FROM AUDIT_TABLE
      2  WHERE EVENT_USER = 'B'
      3  HAVING MAX(TRUNC(EVENT_DATE)) <= TO_DATE('31/07/2012', 'DD/MM/YYYY')
      4  /
    
    MAX(EVENT
    ---------
    10-JUL-12
    
    1 row selected.
    
    dayneo@RMSD>

Posting Permissions

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