Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Location
    Southern CA
    Posts
    2

    Lightbulb Unanswered: Need help with Max(Date) aggregate

    Hi there. My goal is to retrieve a set of user_id's who are visible in my system, have a positive balance in the "transactions" table (like a checkbook register, basically), and whom have NOT made a credit in the "transactions" table within the past 10 months. I'd like to warn them because their funds are going to expire after 1 year. Site users need to maintain a positive balance in order to continue to interact with the system.

    So to be clear... I'm trying to come up with a list of user id's who:

    A.) are active / visible in the system ( users.visible = 1 )
    B.) have a positive balance ( sum(transactions.amount) > 0 )
    C.) whose last "credit" transaction was inserted more than 10 months ago ( transactions.tx_type = 1 & transactions.date_created ? )

    I've got A and B in the bag... I just can't figure out how to further filter for C. The following query gives me a list of visible users with a positive account balance.

    Code:
    SELECT
    	user_id,
    	sum(amount)
    FROM
    	transactions
    WHERE
    	user_id in (select id from users where visible = 1)
    GROUP BY
    	user_id
    HAVING
    	sum(amount) > 0
    Can anyone provide hints for how I can take this further and only find users whose MOST RECENT credit transaction (i.e. transactions.tx_type = 1) date was over 10 months ago? I presume it has something to do with including MAX(transactions.date_created) <= dateadd(mmmm, 10, getdate()) or some such thing... but I can't nail it.

    The schema for the transactions table is basically:

    Code:
    id int
    date_created datetime
    user_id int (FK for users table)
    tx_type tinyint (a credit = 1)
    amount smallmoney

    Thanks!
    Last edited by socalscorpio; 11-04-08 at 00:34.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT users.id
         , SUM(transactions.amount)
      FROM users
    INNER
      JOIN transactions
        ON transactions.user_id = users.id
     WHERE users.visible = 1
       AND ( SELECT MAX(date_created)
               FROM transactions 
              WHERE user_id = users.id
                AND tx_type = 1 ) <= 
           DATEADD(MM,-10,GETDATE()) 
    GROUP 
        BY users.id
    HAVING SUM(transactions.amount) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Location
    Southern CA
    Posts
    2
    That is a beautiful thing. Thanks so much!

Posting Permissions

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