I am trying to figure out how to create a report we call "DaysLate".

When we create a Sales Order we use the "Due Date" as a "Promise" date, the date we say it will ship to the customer.

The day the product ships, we create an invoice that goes out with the product and the customer is invoiced that day.

I'd like to create a report that says -

Show me all of the invoices in [date range June 01, 2008 to June 30, 2008] and the "DaysLate > 0". 

This will tell us how many days late we are shipping the product vs. when we said we would.  I cannot figure out where the relationship between the two are.

Thanks.

Ferrell

Try running:

SELECT Invoice.CustomerRefFullName, Invoice.RefNumber, InvoiceLinkedTxn.TxnID,
InvoiceLinkedTxn.LinkedTxnTxnType, InvoiceLinkedTxn.LinkedTxnTxnID,
InvoiceLinkedTxn.LinkedTxnTxnDate as Date, InvoiceLinkedTxn.LinkedTxnRefNumber as Number,
InvoiceLinkedTxn.LinkedTxnAmount as Amount, InvoiceLinkedTxn.BalanceRemaining as Balance,
SalesOrder.DueDate as PromisedDate, Invoice.TxnDate as Shipped,
{fn CONVERT(Invoice.TxnDate, SQL_DATE)} - {fn CONVERT(SalesOrder.DueDate, SQL_DATE)} as DaysLate
from Invoice NOSYNC, InvoiceLinkedTxn NOSYNC, SalesOrder NOSYNC
where Invoice.TxnID=InvoiceLinkedTxn.TxnID
and InvoiceLinkedTxn.LinkedTxnTxnID=SalesOrder.TxnID
and Invoice.TxnDate >= {d'2008-06-01'} and Invoice.TxnDate <= {d'2008-06-30'}
and (Invoice.TxnDate - SalesOrder.DueDate > 0)
order by Invoice.CustomerRefFullName

Note: If you don't see recent information, you can resync the optimized tables by running:

sp_optimizeupdatesync Invoice

sp_optimizeupdatesync InvoiceLinkedTxn

sp_optimizeupdatesync SalesOrder