Knowledgebase: Syntax
[QODBC-Desktop] Troubleshooting - Easiest way to make report showing average time between receipt and invoice of item
Posted by Jack - QODBC Support on 10 November 2016 06:55 AM

Troubleshooting - Easiest way to make report showing average time between receipt and invoice of item

Problem Description:

I'd like to make a report to show me which items are selling quickly, and which are selling slowly. I figure the thing I need to look at them is the length of time between the item receipt date and the item invoice date. I made a report in crystal reports that do this, using the InvoiceLine and ItemReceiptItemLine tables, and I think it works, but it takes so long to run that I'm not sure, it's never finished it. I think I'll start it going when I leave tonight and see if it will complete overnight.

Is there another way to do this? Am I on the right track? Is there an existing report that will give me this information? It seems like it would be a pretty common thing a lot of people would want to know.

For the most part, each item that I sell is unique and has a unique item name. So for a given item, there is only one receipt and one invoice, then no more receipts of that item ever again. Does that make it any easier to track?

Mostly they are received with a bill through. Occasionally on just an item receipt w/o bill.

How do I filter results by account? (income, asset or COGS account) 
 

Solution:

This approach will only work if all stock parts are item receipted without a bill. There's no true tracking of each part received and when it's sold. So it's going to take a bit of work to make this work well.

if that's the case you can start with this:-

Select BillItemLine.ItemLineItemRefFullName, BillItemLIne.RefNumber as BillRef, BillItemLine.TxnDate as PurchaseDate, InvoiceLine.RefNumber as InvRef, InvoiceLine.TxnDate as SoldDate, {fn CONVERT(InvoiceLine.TxnDate, SQL_DATE)} - {fn CONVERT(BillItemLine.TxnDate, SQL_DATE)} as DaysHeld From BillItemLine NOSYNC, InvoiceLine NOSYNC where BillItemLine.ItemLIneItemRefListID = InvoiceLine.InvoiceLineItemRefListID and InvoiceLine.TxnDate >= BillItemLine.TxnDate

 

For filter results by account use below query.

SELECT Item.ParentRefFullName as ParentItem, AVG({fn CONVERT(InvoiceLine.TxnDate, SQL_DATE)} - {fn CONVERT(BillItemLine.TxnDate, SQL_DATE)}) as AvgDaysHeld FROM BillItemLine NOSYNC, Item NOSYNC, InvoiceLine NOSYNC WHERE BillItemLine.ItemLIneItemRefListID = Item.ListID and BillItemLine.ItemLIneItemRefListID = InvoiceLine.InvoiceLineItemRefListID and BillItemLine.TxnDate >= {d'2007-01-01'} and InvoiceLine.TxnDate >= BillItemLine.TxnDate GROUP BY Item.ParentRefFullName

 

 

(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).