Troubleshooting - How can I get the Last Items sold to customer details

Problem Description:

I created a report using a custom set with the customer as the parent and InvoiceLine as the child giving me items ordered in the past.

My problem is I only want the last time an item was ordered and want to eliminate some things, such as shipping.

When I try to filter out items like shipping, it also takes out any invoice line item.

I'm at a complete loss when it comes to getting only the last time an item was ordered.  

Solution:

You can get items ordered by a customer, in part name order with the last date ordered first by using the below query:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian: Kitchen' and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc



Or for the last time an item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc