,(select top 1 s.[SalesPrice]
from [ThePriceList] s
where s.[Item] = a.[Item] AND
s.[ColorCode] = a.[ColorCode] AND
s.[TheDate] <= a.[TheDate]
order by s.[TheDate]) as SalesPrice
FROM [TheStockLedger] a
Thanks for the DDL, but it is all wrong. You have no keys and no way to have keys; no constraints, Declarative Referential Integrity, data types, etc. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats and not either of the dialects you used.
What you did post garbage. I see the magical NVARCHAR(50) that ACCESS programers use, why did you put the_ in the table and column names? Etc.
Let's try to clean it up first:
I have 2 test tables one for stock and one for prices. I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.
That is not how to build a history table. There are some DDL tricks to assure that there are no gaps in the dates, but I am not sure you are ready yet.
CREATE TABLE Price_History
(sku CHAR(10) NOT NULL
ON UPDATE CASCADE,
price_start_date DATE NOT NULL,
price_end_date DATE, --- null is current
CHECK (price_start_date <= price_end_date),
unit_price DECIMAL (12,2) NOT NULL
CHECK (unit_price > 0.00),
PRIMARY KEY (sku, price_start_date));
The items you are selling are entities and they get a table with their attributes.
CREATE TABLE Stock
(sku CHAR(10) NOT NULL,
item_name VARCHAR(50) NOT NULL,
color_code CHAR(5) NOT NULL, --- which industry standard?
I will assume that you do a store by store inventory on a daily basis.
CREATE TABLE Daily_Inventory
(store_nbr CHAR(5) NOT NULL,
sku CHAR(10) NOT NULL
ON UPDATE CASCADE,
inventory_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (store_nbr, sku, inventory_date),
onhand_qty INTEGER NOT NULL
CHECK (onhand_qty >= 0),
To find the value of the inventory on a particular date,
SELECT I.sku, @in_report_date, SUM(I.onhand_qty * S.unit_price) AS inventory_value
FROM Inventory AS I,
Price_History AS H
AND COALESCE (H.price_end_date, CURRENT_TIMESTAMP)
AND I.inventory_date = @in_report_date
AND H.sku = I.sku
GROUP BY I.sku;
His method of implementing historical records is fine. I've implemented many methods, and finally settled on the one he is using as the best.
I would, however, suggest that he consider storing the price at the date of the stock acquisition in the stock table itself. It is a snapshot of the item price at the time the stock was acquired, and is an attribute of the stock item.
If it's not practically useful, then it's practically useless.
Thanks for your post, I'll have to digest it a bit to see what it is doing but my tables above are only test tables to make the example easy to understand, they are not my live tables which contain many fields also with keys...