Results 1 to 2 of 2

Thread: TSQL problem - please help!!!!

  1. #1
    Jason Fitch Guest

    TSQL problem - please help!!!!


    I have a problem that I can't solve. Any help would be greatly appreciated.

    I need to return one row with the following information.(see eg. table below)

    Matter#(group by), timekeeper, most recent rate ( based on date field)



    Matter Timekeeper Rate Date
    -------------------------------------------------
    23 0045 120.00 01/01/1999
    23 0045 110.00 01/01/1998
    23 0045 100.00 01/01/1997
    25 0023 150.00 01/01/1998
    25 0072 130.00 01/01/1999
    23 0052 90.00 01/01/1996


    I am looking for a return like this:

    Matter Timekeeper Rate Date
    -------------------------------------------------
    23 0045 120.00 01/01/1999
    23 0052 90.00 01/01/1996
    25 0023 150.00 01/01/1998
    25 0072 130.00 01/01/1999


  2. #2
    Jonathan Guest

    TSQL problem - please help!!!! (reply)

    Suppose your table name is [test]
    try this:

    select * from test tt
    where ( rate =(select top 1 t.rate from test t where tt.matter= t.matter and tt.timekeeper=t.timekeeper)
    and date =(select top 1 t.date from test t where tt.matter= t.matter and tt.timekeeper=t.timekeeper) )
    order by tt.matter, tt.timekeeper

    hope this helps

    Jonathan

    ------------
    Jason Fitch at 7/12/99 3:13:51 PM


    I have a problem that I can't solve. Any help would be greatly appreciated.

    I need to return one row with the following information.(see eg. table below)

    Matter#(group by), timekeeper, most recent rate ( based on date field)



    Matter Timekeeper Rate Date
    -------------------------------------------------
    23 0045 120.00 01/01/1999
    23 0045 110.00 01/01/1998
    23 0045 100.00 01/01/1997
    25 0023 150.00 01/01/1998
    25 0072 130.00 01/01/1999
    23 0052 90.00 01/01/1996


    I am looking for a return like this:

    Matter Timekeeper Rate Date
    -------------------------------------------------
    23 0045 120.00 01/01/1999
    23 0052 90.00 01/01/1996
    25 0023 150.00 01/01/1998
    25 0072 130.00 01/01/1999


Posting Permissions

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