#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Balance before a specific time


    Hi Guys,

    I have an trans table:

    AccID Date Balance
    1 19/02/13 15
    1 18/01/13 25
    2 22/01/13 30
    2 25/12/12 22
    3 25/12/12 10

    I need to write a query to deliver the balance at the end of the month for all accounts, by selecting the last time they appear in the trans table before a specific date.

    In the example above, I would like to know each accounts balance at the end of January.


    The result would be - 65 euro.
    1 18/01/13 25 euro,
    2 22/01/13 30 euro,
    3 25/12/12 10 euro

    Many thanks for your help.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    hope this is what you looking for..

    select temp.* from (SELECT AccID,Date,Balance
    FROM table_name
    WHERE Date BETWEEN 'start date' and 'end date'
    order by Date desc) as temp
    GROUP BY AccID
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Techanalyst
    hope this is what you looking for..
    nope, it ain't

    this is --
    Code:
    SELECT t.AccID
         , t.Date
         , t.Balance
      FROM ( SELECT AccID
                  , MAX(Date) AS  max_date
               FROM trans 
              WHERE Date < '2013-02-01'
             GROUP 
                 BY AccID ) AS m
    INNER
      JOIN trans AS t
        ON t.AccID = m.AccID
       AND t.Date = m.max_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo