Knowledgebase: Data & Table
[QODBC-Desktop] Troubleshooting - How can I get Last Items sold to customer details
Posted by Jack - QODBC Support on 30 March 2016 10:22 AM

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



(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).