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

    Join Date
    Oct 2005
    Rep Power

    Maximum date prior to given date

    Hello Everyone -

    I am working with two tables... The first one I am creating a temp table getting distinct dates based on some criteria from another table, the columns in the temp table would be:

    a.EDate, a.PolNumber, a.CType

    What I am trying to do is get the maximum date from another table that is not greater than EDate from the temp table.

    Columns in that other table are:

    b.VDate, b.PolNumber, b.CType, b.ChargeBack

    I want the query to return:

    a.EDate, a.PolNumber, a.CType, b.VDate (the last available date not greater than a.EDate), b.ChargeBack

    a.PolNumber = b.PolNumber and a.CType = b.CType

    Hope I explained it ok, thanks for the help!!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    SELECT a.EDate
         , a.PolNumber
         , a.CType
         , b.VDate
         , b.ChargeBack
      FROM first_table AS a
      JOIN second_table AS b
        ON b.PolNumber = a.PolNumber 
       AND b.CType = a.CType
       AND b.VDate =
           ( SELECT MAX(VDate)
               FROM second_table
              WHERE PolNumber = a.PolNumber
                AND CType = a.CType
                AND VDate <= a.EDate )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo