Knowledgebase: Tutorials
[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 Last Items sold to customer details

Problem Description:

I was able to create a report using a customer 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 items completely such as shipping.

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

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

Solution:

You can get items ordered by a customer, in part name order with the last date ordered first by using 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 a 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).