Troubleshooting - Easiest way to make a report showing the average time between receipt and invoice of the item
Problem Description:
I want to make a report to show me which items are selling quickly and slowly. I figure I need to look at 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 guess I'll start 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 an everyday thing many people would want to know.
For the most part, each item I sell is unique and has a unique 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 a 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 tracking of each piece received and when it's sold. So it will 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 the 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.listed and BillItemLine.ItemLIneItemRefListID = InvoiceLine.InvoiceLineItemRefListID and BillItemLine.TxnDate >= {d'2007-01-01'} and InvoiceLine.TxnDate >= BillItemLine.TxnDate GROUP BY Item.ParentRefFullName
|