January 24, 2005 at 10:20 pm
Currently, the following code gets the
"last six orders shipped" shipped for one
customer. How can I loop or modify this to get
the "last six orders shipped" per customer ?
SELECT TOP 6 customers.custno, [order-header].entrydate
FROM customers INNER JOIN [order-header]
ON customers.custedp = [order-header].custedp
GROUP BY customers.custno,
[order-header].entrydate, [order-header].orderno
ORDER BY customers.custno, [order-header].entrydate DESC
January 25, 2005 at 5:59 am
Quick and dirty way is to loop through a distinct set of customer records and bring into a cursor which you can use in a where clause to bring back a normal top 6 statement.
SELECT TOP 6 customers.custno, [order-header].entrydate
FROM customers INNER JOIN [order-header]
ON customers.custedp = [order-header].custedp
WHERE CUSTOMERS.UNIQUIEID = cursorCUSTOMERSUNIQUEID
GROUP BY customers.custno,
[order-header].entrydate, [order-header].orderno
ORDER BY customers.custno, [order-header].entrydate DESC
I'm sure someone out there can come up with something dynamic, but if you want it asap this method will work.
January 25, 2005 at 6:44 am
Assuming orderno is unique
SELECT c.custno, h.entrydate, h.orderno
FROM customers c
INNER JOIN [order-header] h
ON h.custedp = c.custedp
WHERE h.orderno IN (SELECT TOP 6 h2.orderno
FROM [order-header] h2
WHERE h2.custedp = c.custedp
ORDER BY h2.entrydate DESC)
ORDER BY c.custno, h.entrydate DESC
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply