Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    5

    Unanswered: Newbie SQL Problem

    Hi,
    I'm new to sql and wonder if anyone can help me.

    i have a table whose structure (and values) is as follows -

    txndt acnum accode
    ------ -------- ---------
    3/1/2003 0001 01
    5/1/2003 0001 01
    5/5/2003 0796 04
    7/5/2003 0415 07
    9/5/2003 0796 04
    11/5/2003 0691 06

    the table's primary key is on acnum,accode and txndt

    i need to display the dates which are closest
    to the current date (sysdate)
    ie. in case of acnum 0001 the record where txndt is
    5/1/2003 should be shown (the 3/1/2003 dated one should not be returned)
    similarly for acnum of 0796 the 9/5/2003 record should be returned.

    any idea on how i can get this ? your help would be greatly appreciated.


    thx,
    spike

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Spike,

    I think this will do what you want. Where there are two entries with equal intervals, one before and one after, the earliest entry is fetched.

    select t.acnum, min( t.txndt )
    from test t,
    (
    select acnum, min( abs( txndt-trunc( sysdate )) ) as closet
    from test
    group by acnum
    ) d
    where abs( t.txndt-trunc( sysdate )) = d.closet
    group by t.acnum

    HTH
    Bill

  3. #3
    Join Date
    May 2003
    Posts
    5

    Thumbs up

    Hi Bill,
    It worked
    thanks so much for your help.

    regards,
    Spike

Posting Permissions

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