declare @LT table (Loc char(3),[Sample_date] datetime)
declare @VRT table (Loc char(3), Value decimal(3,2),[Starting_date] datetime)

insert into @LT (Loc,Sample_date)
select
	'K01','2001-01-01' union all select
	'K01','2002-01-01' union all select
	'K01','2003-01-01' 

insert into @VRT (Loc,Value,Starting_date)
select
	'K01',1.23,'1995-01-01' union all select 
	'K01',1.67,'2001-06-01' union all select 
	'K01',2.7,'2004-01-01' 



select 
	l.loc,l.Sample_date,max(v.value),max(v.starting_date)
from
	@LT l
inner join
	@VRT v
ON
	l.loc = v.loc
and
	 v.starting_date <= l.sample_date  
group by
	l.loc,l.Sample_date

DECLARE @Location table (Loc VARCHAR(3), sample_date DATETIME)
INSERT @Location
SELECT 'K01','2001-01-01'
UNION ALL SELECT 'K01','2002-01-01'
UNION ALL SELECT 'K01','2003-01-01'

DECLARE @Value table (Loc VARCHAR(3), Value NUMERIC(10,2), StartingDate DATETIME)
INSERT @Value
SELECT 'K01',1.23,'1995-01-01'
UNION ALL SELECT 'K01',1.67,'2001-06-01'
UNION ALL SELECT 'K01',2.7,'2004-01-01';


WITH MyValue AS
(
	SELECT *,ROW_NUMBER() OVER (ORDER BY StartingDate) AS RowNum
	FROM @Value 
)

SELECT
	L.Loc,
	L.sample_date,
	T.Value
FROM
	@Location AS L
INNER JOIN
	(	
		SELECT 
			A.StartingDate AS StartDate,
			COALESCE(B.StartingDate, '99991231') AS EndDate,
			A.Value
		FROM 
			MyValue AS A
		LEFT OUTER JOIN
			MyValue AS B
			ON A.RowNum + 1 = B.RowNum 
	) AS T
	ON L.sample_date >= StartDate
	AND L.sample_date < EndDate

quote:

---

Originally posted by Lamprey

Here is another way to do this. I tried to break the problem out a little bit so that it *might* be easier to understand (although probably not as effecient as Don's). The first part is a CTE with just orders the Value table by StartingDate. This is then used to LEFT join to itself to get the date ranges. Fianlly, this derived table of date ranges is joined to the Location table to get the Value for that location. Hopefully, it makes sense:

DECLARE @Location table (Loc VARCHAR(3), sample_date DATETIME)
INSERT @Location
SELECT 'K01','2001-01-01'
UNION ALL SELECT 'K01','2002-01-01'
UNION ALL SELECT 'K01','2003-01-01'

DECLARE @Value table (Loc VARCHAR(3), Value NUMERIC(10,2), StartingDate DATETIME)
INSERT @Value
SELECT 'K01',1.23,'1995-01-01'
UNION ALL SELECT 'K01',1.67,'2001-06-01'
UNION ALL SELECT 'K01',2.7,'2004-01-01';


WITH MyValue AS
(
	SELECT *,ROW_NUMBER() OVER (ORDER BY StartingDate) AS RowNum
	FROM @Value 
)

SELECT
	L.Loc,
	L.sample_date,
	T.Value
FROM
	@Location AS L
INNER JOIN
	(	
		SELECT 
			A.StartingDate AS StartDate,
			COALESCE(B.StartingDate, '99991231') AS EndDate,
			A.Value
		FROM 
			MyValue AS A
		LEFT OUTER JOIN
			MyValue AS B
			ON A.RowNum + 1 = B.RowNum 
	) AS T
	ON L.sample_date >= StartDate
	AND L.sample_date < EndDate

---