Hi Josh,
You need to divide your problem into two smaller and easier problems.
First, find the current effective date for each inventory item in
Pricing table. Then find the Price of each item in Pricing table where
its effective date matches the current effective date.
This view gives you current effective date for all items:
create view EffectiveDates as
select ItemID, max(EffectiveDate) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID
Now you can write a query using your tables and this view, like this:
select InvItem.ItemID, [Desc], Price as CurrentPrice, EffectiveDate
from InvItem, Pricing, EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates.ItemID
and Pricing.EffectiveDate = EffectiveDates.CurEffDate
Or if you don't want to have a separate view, you can just incorporate
the body of view into your query like this:
select InvItem.ItemID,
[Desc],
Price,
EffectiveDate
from InvItem,
Pricing,
(select ItemID, max(EffectiveDate) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID) as EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates.ItemID
and Pricing.EffectiveDate = EffectiveDates.CurEffDate
which gives you the same result. I hope it helps you.
Shervin
jo**@musicsteps.com (Josh) wrote in message news:<37**************************@posting.google. com>...
I need a query that will select the closest date.
I have to tables Pricing and InventoryItem.
tblInventoryItem
InventoryItemID <- Pk
Description
tblPricing
PricingID <- Pk
InventoryItemID
Price
EffectiveDate
I need to select all the current "prices" for each InventoryItem based
on the Pricing's effective date.
select top 1 * from tblPricing join tblInventoryItem on
tlbPricing.InventoryItemID = tblInventoryItem.InventoryItemID
WHERE
tblPricing.EffectiveDate <= GetDate()
This does grab the correct price for a single InventoryItem. But I
need this query run for all InventoryItem's. I probably need some
sort of subquery but I can't figure it out....
Thanks....