Last Items sold to customer reporting |
Author |
Message |
John |
|
Group | : Members |
Posts | : 1 |
Joined | : 2007-04-03 |
|
Profile |
|
Posted : 2007-04-03 07:15:27 |
I was able to create a report using a customer set with 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 eleminate some items completly 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.
Any help would be greatly appreciated....Thanks |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-04-03 09:34:06 |
For items ordered by a customer, in part name order with last date ordered first:
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
|
|
|
|
|
Posted : 2007-09-03 01:40:54 |
is possible to execute one between in this query in question?
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-03 08:43:48 |
I'm not to sure what the question is?, but QODBC does support "BETWEEN", for example:-
SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName between 'Appliance' and 'Framing' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc
|
|
|
|
|
Posted : 2007-09-04 01:00:23 |
SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName between TxnDate='#Date_Start#' and TxnDate='#Date_End#' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc
Its is possible? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-04 08:44:32 |
Yes, but you need to get the syntax correct:
SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and TxnDate between {d'2007-11-01'} and {d'2007-12-01'} Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc
Or in your case:
SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and TxnDate between '#Date_Start#' and '#Date_End#' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc
|
|
|
|
|
Posted : 2007-09-05 07:39:38 |
cool.
I execute this query:
SELECT * FROM OPENQUERY(QODBC, 'SELECT InvoiceLineItemRefFullName,sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName=''Super 8 Motel -Orlando'' and TxnDate between {d''2007-01-04''} and {d''2007-09-04''} and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName,TxnDate Order by InvoiceLineItemRefFullName,TxnDate desc')
Receive:
Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 1.00000 Can Liners:40 x 48:62000 Trash Bag 3.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 3.00000 Can Liners:40 x 48:62000 Trash Bag 1.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 2.00000 Can Liners:40 x 48:62000 Trash Bag 1.00000 Can Liners:40 x 48:62000 Trash Bag 5.00000
Possible receive not duplicates items or execute statement sum(InvoiceLineQuantity) ?
Thank you for attention.
Regards,
MacTorvald |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-05 09:21:34 |
Just remove the TxnDate from the group by and order by conditions like this:
SELECT * FROM OPENQUERY(QODBC, 'SELECT InvoiceLineItemRefFullName,sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName=''Super 8 Motel -Orlando'' and TxnDate between {d''2007-01-04''} and {d''2007-09-04''} and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName Order by InvoiceLineItemRefFullName') |
|
|
|
|
Posted : 2007-09-05 09:44:45 |
Ok.. cool! Perfect!
Thank you very much
Regards
MacTorvald |
|
|
|
|
Posted : 2007-09-05 10:03:56 |
I want to order my result, ps: order by sum(InvoiceLineQuantity)
I need receive then from the smaller to the highest
--------------------------------------------------------- Product | Qty ---------------------------------------------------------- xxxxxxx | 3.00 yyyyyyy | 4.00 zzzzzzz | 5.00
its is possible man?
Thank you for the help.
Regards,
MacTorvald
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-05 10:58:15 |
Sorry, you cannot GROUP BY or ORDER BY sum(InvoiceLineQuantity). You need to import the returned results into a table or array and resort the results. |
|
|
|