SQL Date Query

[Josh]

I need a query that will select the closest date.

I have to tables Pricing and InventoryItem.

tblInventoryIte m
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 tblInventoryIte m on
tlbPricing.Inve ntoryItemID = tblInventoryIte m.InventoryItem ID
WHERE
tblPricing.Effe ctiveDate <= 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....

[David Portas]

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

SELECT I.*, P.*
FROM tblInventoryIte m AS I
JOIN
(SELECT inventoryitemid , MAX(effectiveda te) AS effectivedate
FROM tblPricing
GROUP BY inventoryitemid ) AS M
ON I.inventoryitem id = M.inventoryitem id
JOIN tblPricing AS P
ON P.inventoryitem id = M.inventoryitem id
AND P.effectivedate = M.effectivedate

(untested)

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

[Christian Maslen]

Hi Josh,

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

SELECT *
FROM tblInventoryIte m i
, tblPricing p
,(SELECT InventoryItemID
, MAX(EffectiveDa te) as PriceDate
FROM tblPricing
WHERE EffectiveDate <= @YourDate
GROUP BY InventoryItemID
) cp
WHERE i.InventoryItem ID = cp.InventoryIte mID
AND cp.InventoryIte mID = p.InventoryItem ID
AND p.Effective_dat e = cp.PriceDate;

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

Christian.

[Shervin Shapourian]

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(EffectiveDa te) 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.Effecti veDate = 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(EffectiveDa te) as CurEffDate
from Pricing
where EffectiveDate <= GetDate()
group by ItemID) as EffectiveDates
where InvItem.ItemID = Pricing.ItemID
and Pricing.ItemID = EffectiveDates. ItemID
and Pricing.Effecti veDate = EffectiveDates. CurEffDate

which gives you the same result. I hope it helps you.

Shervin
jo**@musicsteps .com (Josh) wrote in message news:<37******* *************** ****@posting.go ogle.com>...

I need a query that will select the closest date.

I have to tables Pricing and InventoryItem.

tblInventoryIte m
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 tblInventoryIte m on
tlbPricing.Inve ntoryItemID = tblInventoryIte m.InventoryItem ID
WHERE
tblPricing.Effe ctiveDate <= 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....