CREATE TABLE #LineItem (
	ItemID	int NOT NULL,
	ItemDate	datetime NOT NULL,
	ProductID	int NOT NULL
)

CREATE TABLE #ProductHistory (
	ProductID int NOT NULL,
	UpdateDate datetime NOT NULL,
	Price	money
)
GO

INSERT INTO #LineItem (ItemID, ItemDate, ProductID) VALUES (1, '20050401', 1)
INSERT INTO #LineItem (ItemID, ItemDate, ProductID) VALUES (2, '20050401', 2)
INSERT INTO #LineItem (ItemID, ItemDate, ProductID) VALUES (3, '20050515', 1)

INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (1, '20050101', 7.99)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (1, '20050320', 10.00)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (1, '20050514', 15.00)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (1, '20050720', 22.22)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (2, '20050101', 18.00)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (2, '20050219', 19.50)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (2, '20050401', 25.50)
INSERT INTO #ProductHistory (ProductID, UpdateDate, Price) VALUES (2, '20050623', 37.77)
GO

SELECT
	L.ItemID,
	CONVERT(varchar(12), L.ItemDate, 101) AS ItemDate,
	P.Price
FROM
	#LineItem L
	JOIN #ProductHistory P ON L.ProductID = P.ProductID
		AND P.UpdateDate = 
			( SELECT MAX(UpdateDate)
			  FROM #ProductHistory innerP
			  WHERE innerP.ProductID = L.ProductID
			    AND innerP.UpdateDate <= L.ItemDate )

GO

DROP TABLE #LineItem
DROP TABLE #ProductHistory

ItemID      ItemDate     Price                 
----------- ------------ --------------------- 
1           04/01/2005   10.0000
2           04/01/2005   25.5000
3           05/15/2005   15.0000

select	b.ItemID
	,convert(varchar,b.ItemDate,101) ItemDate
	,Price
from	#ProductHistory a
join	(
	select	ItemID
		,ItemDate
		,a.ProductID
		,max(updateDate) UpdateDate
	from	#lineitem a
	join	#producthistory b
			on a.productid = b.productid
	where	UpdateDate <= ItemDate
	group by ItemID
		,ItemDate
		,a.ProductID
	) b
		on a.ProductID = b.ProductID
		and a.UpdateDate = b.UpdateDate

SELECT
	L.ItemID,
	CONVERT(varchar(12), L.ItemDate, 101) AS ItemDate,
	priceInterval.Price
FROM
	#LineItem L
	JOIN
	(
		SELECT
			frmd.ProductId
			,frmd.UpdateDate
			,(	SELECT MIN(tod.UpdateDate)-1
				FROM #ProductHistory tod
				WHERE frmd.ProductId = tod.ProductId
				AND frmd.UpdateDate < tod.UpdateDate ) AS ValidTo
			,frmd.Price
		FROM
			#ProductHistory frmd
	) AS priceInterval
	ON L.ProductId = priceInterval.ProductId
	AND L.ItemDate BETWEEN priceInterval.UpdateDate AND ISNULL(priceInterval.ValidTo,L.ItemDate))

quote:

---

Originally posted by byrmol

>> Therefore, I have no option to restructure the database
Can you create views or TVF's or indexes if needed?


DavidM

A front-end is something that tries to violate a back-end.

---

quote:

---

Originally posted by Kristen

Any way to create [even by adding another table etc.] a "Flag" column on #ProductHistory for the row, for a given product, which is the most recently dated (and enforce it with, say, a trigger?)

Kristen

---

quote:

---

Originally posted by TG

Don't forget about the idea of generating a few million rows for testing in your testbed or sandbox or whatever we call it these days.

good luck

Be One with the Optimizer
TG

---

create view LineItem_Historical
as
SELECT L.ItemID, L.ItemDate, L.ProductID
	, ( SELECT MAX(UpdateDate)
  FROM ProductHistory innerP
  WHERE innerP.ProductID = L.ProductID
    AND innerP.UpdateDate <= L.ItemDate ) as PriceDate --Repeat other historical values
FROM LineItem L
go

SELECT L.ItemID, L.ItemDate, L.ProductID, P.Price
from LineItem_Historical AS L
INNER join ProductHistory P ON L.ProductID = P.ProductID AND P.UpdateDate = L.PriceDate