Search code examples
sqlsql-servermaxwhere-clause

SQL return multiple columns based of there max date and the date is less than certain value

What I am trying to do is find the 'newest' Ad Rates based on its date. I need to return multiple columns which will be grouped by the store. I am using SSMS. and below is a screen shot of what the table looks like with all the values. Disregard the Id.

enter image description here

As you will see, there are multiple store numbers out there. There will also be a parameter passed into this select where the AdRateDate can not be larger than.

So if my parameter is Declare @toDate DateTime = '2022-11-30 00:00:00.000'

Then the data return should be

enter image description here

Here is the script I was trying, and I am lost!

DECLARE @ToDate DATETIME = '2022-11-30 00:00:00.000'  
SELECT StoreNumber, AdRate  
FROM StoreAdRate  
WHERE AdRateDate =  
 (Select Max(AdRateDate) From StoreAdRate Where AdRateDate <= @ToDate And StoreNumber = StoreNumber)
Solution

  • I think your statement is on the right track and just lack alias to differentiate between StoreNumber in sub-query and main query.

    Try the following:

    DECLARE @ToDate DATETIME = '2022-11-30 00:00:00.000';

SELECT StoreNumber, AdRate
FROM StoreAdRate sar
WHERE AdRateDate = (Select Max(AdRateDate) From StoreAdRate sub 
                    Where sub.AdRateDate <= @ToDate 
                    And sub.StoreNumber = sar.StoreNumber);