Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    17

    Unanswered: Struggling with DATEDIFF()

    Greetings!!

    Am having a bit of a nightmare trying to get DATEDIFF() to work.
    Im new to Oracle syntax as i'm transitioning from MSAccess

    Have searched around the forum but not found anything which fixes it

    Here is the code:

    Code:
    CREATE OR REPLACE VIEW
      TRADES_PER_BROKER_LAST7DAYS 
    AS
      SELECT
        B.FIRST_NAME AS FIRST_NAME,
        B.LAST_NAME AS LAST_NAME,
        NVL(COUNT(T.TRADE_ID),0) AS NUMBER_OF_TRADES_LAST_7_DAYS
      FROM
          BROKERS b  
      LEFT OUTER JOIN
          TRADES t
      ON
          B.BROKER_ID=T.BROKER_ID  
      WHERE  
          DATEDIFF(DD,SYSDATE -7,SYSDATE ) = '7' 
      GROUP BY B.FIRST_NAME, B.LAST_NAME
    ;
    SELECT
      FIRST_NAME,
      LAST_NAME,
      NUMBER_OF_TRADES
    FROM
      TRADES_PER_BROKER_LAST7DAYS
    ORDER BY NUMBER_OF_TRADES_LAST_7_DAYS DESC
    ;
    If you need more information or a schema of the database just let me know.

    Any help is much appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    Jun 2011
    Posts
    17


    Thanks for the response... found a rather simple solution from another source as well as working out there is no DATEFIFF() in Oracle... duh.

    *slaps forehead


Posting Permissions

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