September 21, 2001 at 12:55 pm
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
September 21, 2001 at 2:19 pm
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
September 22, 2001 at 6:17 am
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
December 8, 2002 at 5:39 pm
Your link to your protected server keeps us from seeing your pretty picture.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy