The First_Value/Last_Value sounds really promising, but I'm not quite getting it yet.

-- Method 1.5a

SELECT DISTINCT

Product.Name

,ReportDate = @ReportingDate

,MostRecentOrderDetailId = (SELECT LAST_VALUE(SalesOrderDetailId) OVER (PARTITION BY Product.ProductId order by SalesOrderDetail.ModifiedDate)

FROM sales.SalesOrderDetail SalesOrderDetail WHERE SalesOrderDetail.ProductID = product.ProductID

AND SalesOrderDetail.ModifiedDate <= @ReportingDate)

FROM Production.Product Product

-- Method 1.5b

SELECT DISTINCT

Product.Name

,ReportDate = @ReportingDate

,LAST_VALUE(SalesOrderDetailId) OVER (PARTITION BY Product.ProductId order by SalesOrderDetail.ModifiedDate)

FROM Production.Product Product

LEFT JOIN sales.SalesOrderDetail SalesOrderDetail ON SalesOrderDetail.ProductID = product.ProductID

1.5a returns the fun error:

Msg 512, Level 16, State 1, Line 11

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

When I try to add grouping to either, the window function I get this error:

Column 'sales.SalesOrderDetail.ModifiedDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

For every product I'm trying to get a single value (the Last_Value before a date), but I seem to be doing rather poorly at using Last_Value and getting it to group or return just a single value.

Any advice on what I'm overlooking here?