SHIPP said:
I have the following data for dates/times when the price changes of a
product....
3/19/2000 00:00 1.47
4/23/2000 15:00 1.485
5/2/2000 02:32 1.45
I then have a second table of dates/times.
3/18/2000 15:20
3/19/2000 01:00
4/23/2000 00:01
4/23/2000 15:01
5/2/2000 02:33
I have the following query which returns one date too high and consequently
the wrong price.
SELECT tblPrices.PriceDate, (SELECT top 1 DateTimeField FROM tblDateTimes
WHERE PriceDate<=DateTimeField) AS dteDate
FROM tblPrices;
What should be returned is...
3/18/2000 15:20 0 (because there is no 2nd top date)
3/19/2000 01:00 1.47
4/23/2000 00:01 1.47
4/23/2000 15:01 1.485
5/2/2000 02:33 1.45
In essence what I need is to return the 2nd top date in order to obtain the
correct price. Any help would be appreciated. Thanks in advance.
Mr. Shipp,
Ok, this gets a bit complicated. :\
In places, it gets ugly.
But it *appears* to work.
Sample Tables
CREATE TABLE Products_01302005_1
(ProductID AUTOINCREMENT
,UPC DOUBLE
,ProductName TEXT(72)
,CONSTRAINT pk_Products_01302005_1 PRIMARY KEY (ProductID)
)
Sample Data
UPC NameProduct
12345678910 Chewing Gum
12345678911 Breath Mints
CREATE TABLE ProductPrices_01302005_1
(ProductID LONG
,DatePriceChange DATETIME
,Price CURRENCY
,CONSTRAINT pk_ProductPrices_01302005_1
PRIMARY KEY (ProductID, DatePriceChange)
,CONSTRAINT fk_ProductPrices_Products_01302005_1 FOREIGN KEY
(ProductID)
REFERENCES Products_01302005_1 (ProductID)
)
Sample Data
ProductID DatePriceChanged Price
1 3/19/2000 00:00 1.47
1 4/23/2000 15:00 1.485
1 5/2/2000 02:32 1.45
Note: Technically speaking, there is missing information in
ProductPrices. The period of time a product is at a price is an
Interval, and that period has a Start and an End. But the above only
records the starting time of a price in any one particular row, and so
the ending time data will have to be recreated in a trio of queries
(which is where it gets ugly).
CREATE TABLE ProductSales_01302005_1
(ProductID LONG
,DateSale DATETIME
,CONSTRAINT pk_ProductPrices_01302005_1
PRIMARY KEY (ProductID, DateSale)
,CONSTRAINT fk_ProductSales_Products_01302005_1 FOREIGN KEY
(ProductID)
REFERENCES Products_01302005_1 (ProductID)
)
Sample Data
ProductID DateSale
1 3/18/2000 15:20
1 3/19/2000 01:00
1 4/23/2000 00:01
1 4/23/2000 15:01
1 5/02/2000 02:33
Query Name: ProductPrices_01302005_1_NextDate
SELECT PP1.ProductID
,PP1.DatePriceChange
,Nz((SELECT MIN(PP2.DatePriceChange)
FROM ProductPrices_01302005_1 AS PP2
WHERE (PP2.ProductID = PP1.ProductID
AND PP2.DatePriceChange > PP1.DatePriceChange))
,DateAdd("yyyy", 99, PP1.DatePriceChange)) AS
DatePriceEnded
,PP1.Price
FROM ProductPrices_01302005_1 AS PP1
Query Name: ProductPrices_01302005_1_PrevDate
SELECT PP1.ProductID
,#01/01/1900# AS DatePriceChange
,MIN(PP1.DatePriceChange) AS DatePriceEnded
,0.0 AS Price
FROM ProductPrices_01302005_1 AS PP1
GROUP BY PP1.ProductID
,#01/01/1900#
,0.0
Query Name: ProductPrices_01302005_1_UnionDate
SELECT ProductID
,DatePriceChange
,DatePriceEnded
,Price
FROM ProductPrices_01302005_1_PrevDate
UNION ALL
SELECT ProductID
,DatePriceChange
,DatePriceEnded
,Price
FROM ProductPrices_01302005_1_NextDate
Query Name: ProductPriceWhenSold_01302005_1
SELECT PS1.ProductID
,PS1.DateSale
,PP1.Price
FROM ProductPrices_01302005_1_UnionDate AS PP1
INNER JOIN
ProductSales_01302005_1 AS PS1
ON (PP1.ProductID = PS1.ProductID)
WHERE (PS1.DateSale BETWEEN
PP1.DatePriceChange AND PP1.DatePriceEnded)
Ok, everybody together now: "All I want for Christmas is a Full Outer
Join, A Full Outer Join!". No, no, never mind me . . .
03/18/2000 15:20 0.00
03/19/2000 01:00 1.47
04/23/2000 00:01 1.47
04/23/2000 15:01 1.485
05/02/2000 02:33 1.45
Maybe someone else knows a better way. (Hint, hint . . .)
Sincerely,
Chris O.
