Current Price Query

[Guest]

I have a table with the following structure.
SupplierID
SupplyPtId
ProductID
EffDate
EffTime
Price

I need to be able to enter a date and time as parameters and then have the
query return the price in effect as of that date and time for all
combinations of supplier, supply point and product. I have tried a few
different group by queries can't seem to find the right combination that
works.

 

[Tom Ellison]

Dear CAW:

I take it you have stored the Effective Date and Effective Time in two
separate columns. This is a single value (philosophically) and needs to be
addressable as a single entity.

Assuming both are date/time datatypes, and that the EffDate has a zero time
component, while the EffTime has a zero date component, you may be able to
just add the two together. If so, we can proceed.

Assuming you also want to see the price, you will need this:

Select SupplierID, SupplyPtId, ProductID, EffDate+EffTime, Price
FROM ATable T
WHERE EffDate+EffTime =
(SELECT MAX(EffDate+EffTime)
From ATable T1
WHERE T1.SupplierID = T.SupplierID
AND T1.SupplyPtId = T.SupplyPtId
AND T1.ProductID = T.ProductID
AND EffDate+EffTime <= CDate([Enter Date/Time: ])

If you don't need the Price it is simpler:

SELECT SupplierID, SupplyPtId, ProductID, MAX(EffDate+EffTime)
FROM ATable
GROUP BY SupplierID, SupplyPtId, ProductID

You should try to combine the date and time into a single column as soon as
it is convenient to do so.

Tom Ellison