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

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0

    Complicated join


    I'm trying to write a view in SQL2000 (it's my client's environment, and they're not ready to upgrade).

    I have a table called "Customers" and another called "CreditCards." One customer may have many credit cards.

    The "CreditCards" table has a varchar field "ExpDate" and the values in this column are all four digits, mmyy.

    I am trying to figure out how to write a view that returns one line per customer, showing the credit card details with the latest expiration date.

    For example, Customer John Doe has two cards. One expires 0313 and the other expires 0514. I want my view to show one line for John Doe, with the details for the 0514 card.

    Can anyone think of a good way to do this?

    Thanks!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    A good place to start would be to flip the year and month around and take the max expiration date before joining to the Customers table. Once you have the expiration date formatted as yymm instead of mmyy, then a max() will return the proper record.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by joeflinthiggins
    A good place to start would be to flip the year and month around and take the max expiration date before joining to the Customers table. Once you have the expiration date formatted as yymm instead of mmyy, then a max() will return the proper record.
    Great idea, Joe, thanks!

IMN logo majestic logo threadwatch logo seochat tools logo