I need to get quantity from record selected with MAX()
Posted on 2007-08-09
I have a large table with four fields (ProductSK, StoreSK, ReceiptDate, Units). I need to find the record for each ProductSK and StoreSKwith the mose recent ReceiptDate (if there is a tie, I need to choose the one with the greatest number of units). The results will be stored in a table with a primary key of ProductSK, StoreSK.
My current query is:
TRUNCATE Table dbo.Last_Receipt
INSERT INTO dbo.Last_Receipt (ProductSK, StoreSK, ReceiptDate, DaysSince, Units)
tf.ReceiptDate as ReceiptDate,
DATEDIFF(dd, tf.ReceiptDate, GetDate()) as DaysSince,
tf.Units as Units
(SELECT ProductSK, StoreSK, MAX(ReceiptDate) as RecptDate
GROUP BY ProductSK, StoreSK) as aa INNER JOIN
InventoryAllocationCube.dbo.TFRPF90_Sum as tf ON
aa.ProductSK = tf.ProductSK AND aa.StoreSK = tf.StoreSK
This is not generating unique rows and the insert fails due to duplicate keyed records. I am quessing that ties are the problem. How can I make this work?