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....

In your Pricing table the natural key is presumably (inventoryitemid,
effectivedate). The following query assumes that (inventoryitemid,
effectivedate) is unique, so you should declare a unique constraint for it.

SELECT I.*, P.*
FROM tblInventoryItem AS I
JOIN
(SELECT inventoryitemid, MAX(effectivedate) AS effectivedate
FROM tblPricing
GROUP BY inventoryitemid) AS M
ON I.inventoryitemid = M.inventoryitemid
JOIN tblPricing AS P
ON P.inventoryitemid = M.inventoryitemid
AND P.effectivedate = M.effectivedate

(untested)

--
David Portas
------------
Please reply only to the newsgroup
--

Hi Josh,

Try the following (I've assumed the combo of InventoryItemID and
EffectiveDate are unique):

SELECT *
FROM tblInventoryItem i
, tblPricing p
,(SELECT InventoryItemID
, MAX(EffectiveDate) as PriceDate
FROM tblPricing
WHERE EffectiveDate <= @YourDate
GROUP BY InventoryItemID
) cp
WHERE i.InventoryItemID = cp.InventoryItemID
AND cp.InventoryItemID = p.InventoryItemID
AND p.Effective_date = cp.PriceDate;

You can achieve the same result using a correlated sub-query, but this
method works best for me across different DBs.

Christian.

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


josh@musicsteps.com (Josh) wrote in message news:<37b0705b.0309170951.54a3a983@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....