Thursday, 19 September 2013

Calculate the qty of items that were purchased more than once by a customer - but need to exclude the first order qty

Calculate the qty of items that were purchased more than once by a
customer - but need to exclude the first order qty

SELECT TOP 100 PERCENT soheader.custid,SOHeader.OrdNbr,
SOLine.InvtID, SOLine.Descr,SOLine.QtyOrd
FROM SOHeader INNER JOIN
SOLine ON SOHeader.OrdNbr = SOLine.OrdNbr
WHERE (SOHeader.OrdDate >= CONVERT(DATETIME, '2013-06-01 00:00:00',
102)) AND (SOHeader.OrdDate <= GETDATE()) AND (SOHeader.CustID = '69065')
ORDER BY SOLine.InvtID, SOHeader.OrdNbr
here is my sample data
69065 WO0175279 69407 Jazzy Laces White 3
69065 WO0175393 69407 Jazzy Laces White 6
69065 WO0175393 69407 Jazzy Laces White 9
Now I want to know how to get the total qty of this item ordered after the
first order. I do not want to include the qty of 3 in the first record
above. I just want to include the qty of 6 in the first reorder and the
qty 9 of the second reorder which equals the qty of 15.
69065 is the customer ID
WO##### is the order ID
69407 is the inventory ID

No comments:

Post a Comment