Need Help with SQL statement
-
As you see, I have 2 tables: A product table, and a Monthly Inventory/Cost table.
Product table keeps product information while the monthly inventory/Cost table keeps monthly inventory and Cost information.
Each time our inventory or cost change, we inserted new QTY, Cost, UpdateDate into monthly inventory/Cost table. For example, if you look at
SKU # 1000 in the monthly inventory/Cost table, you will see that on 6/1/2001, the QTY is 1580 and the Cost is 2.1 . Then
on 7/19/2001, the QTY is 3000 and the cost is 5 . And on 8/19/2001, the QTY is 2500 and the Cost is 5.6 .
I want to see the QTY, and Cost as of '8/20/2001 2:26:57 AM' So I execute this SQL statement,
Select p.sku, name, Quantities price, QTY, Mi.Cost, UpdateDate from product p inner join MothlyInv Mi on p.sku = Mi.sku Where UpdateDate <= Cast('9/20/2001 2:26:57 AM' as datetime)
And the Result is:
SKUNAMEPriceQTYCostUpdateDate
1000Voxom Axle Pegs158015802.10002001-06-01 14:59:43.000
1000Voxom Axle Pegs158030005.00002001-07-19 14:59:43.000
1000Voxom Axle Pegs158025005.60002001-08-19 14:59:43.000
The result shows all 3 SKU which have UpdateDate <= '9/22/2001 2:26:57 AM', but I only want 1 SKu which is closest to '9/20/2001 2:26:57 AM'.
My Boss wants to be able to type in a date, and the System shows him the Current SKU,NAME,Price,QTY,Cost as of that date.
Would someone please help me to rewrite the above SQL statement. I really appreciate your help
-
Sorry, I forgot to explain. I want to be able to show all SKU, not just Sku + 1000 . So, Select TOP 1 wouldn't work
-
try this query
SELECT p.sku, p.name, p.Quantities, p.price, M2.QTY, M2.Cost, M2.UpdateDate
FROM product p
(
SELECT Sku ,
MAX(UpdateDate) As UpdateDate,
FROM MonthlyInv
WHERE UpdateDate <= CONVERT(datetime,'9/20/2001 2:26:57 AM')
GROUP BY Sku
) As MonthlyInventories
INNER JOIN MonthlyInventories As M1 on p.sku = M1.sku
INNER JOIN MonthlyInv As M2 on M1.sku = M2.sku AND M1.UpdateDate = M2.UpdateDate
-
Your link to your protected server keeps us from seeing your pretty picture.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply