Yeah, you definitely need to add more details here. For example, you state that you are trying to find the last "validity" situation for each date in the specified range. However, you don't quite state exactly what that validity really means in an easily understood way. You provide an example that says that for a particular date, that July 20 1994 was the last validity for that date, but then provide example data that provides values of 100, 200, and 300 as the desired output values, with no obvious connection between the 7/20/1994 date and any of the actual output values. Thus, without table structures and some kind of sample data to work with, along with an accurate set of the exact rules that need to be observed, providing assistance would be akin to trying to measure the distance to the moon or the Sun with only the technology available to early humans from a million years ago, and without any knowledge they didn't have.

However, as you asked about passing a date range, I suspect you may have had difficulty figuring out how to get that date range expanded into all the individual dates. Here's a way to do that:

DECLARE @START_DATE AS date = '01/01/2013',

@END_DATE AS date = '01/31/2013'

;WITH cteDateValues AS (

SELECT 0 AS N, @START_DATE AS DATE_VALUE

UNION ALL

SELECT N + 1, CAST(DATEADD(dd, N + 1, @START_DATE) AS date)

FROM cteDateValues

WHERE N < DATEDIFF(dd, @START_DATE, @END_DATE)

)

SELECT DATE_VALUE

FROM cteDateValues

OPTION (MAXRECURSION 1000)

This code, if run, will produce a table containing all the dates in the range in table form, such that additional common table expressions could then make use of that table. You could pass the two dates as parameters to a stored procedure, and use the procedures variables instead of explicitly declaring them. Of course, going from having the list of dates to your final end result is still a bit of a question mark, as we don't have enough information to go on. FYI, that OPTION (MAXRECURSION 1000) is just a way to prevent the recursion taking place in the CTE from going into an infinite loop, by limiting the number of values to 1000. You can change that value to any integer less than 32,768, or to 0 to remove any limitation on the number of records coming out (not generally recommended). In this case, 1000 would give you the ability to specify a date range almost 3 years long.

Does that help get you started?