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

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.

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

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)

CodePudding user response：

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);

CodePudding user response：

So for each store you want to get the most recently given AdRate (and the date it was given) before some specified date parameter?

SELECT StoreNumber AdRate, AdRateDate
FROM (
  SELECT StoreNumber, AdRate, AdRateDate,
    ROW_NUMBER() OVER (PARTITION BY Store Number ORDER BY AdRateDate DESC) AS   RowNumber
  FROM Wherever
  WHERE AdRateData < @ToDate
) T
WHERE RowNumber = 1