How to get the difference (Urgent)?
Posted on 2005-04-07
Set @Receivables1 =(SELECT SUM((Total-dbo.Priv_GetInvoiceAmountPaid(InvoiceID, @RefDate)))
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate <= @RefDate)
IF @Receivables1 IS NULL Set @Receivables1=0
Set @Receivables2 =(SELECT SUM((Total-dbo.Priv_GetInvoiceAmountPaid(InvoiceID, @RefDate)))
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate < @RefDate)
IF @Receivables2 IS NULL Set @Receivables2=0
I want to get the value @ReceivablesIncease which is the receivable increase in one day(on @RefDate). If I calculate from the above script, it take me very long time. However, as @Receivables1 and @Receivables2 repeat most calculation and I want only the difference on the last day. How can I change the script to make it run much faster?
Please note that using InvoiceDate = @RefDate will not work out the correct answer as dbo.Priv_GetInvoiceAmountPaid(InvoiceID, @RefDate) will always equal zero(invoices will not be settled on the same date) which is different from InvoiceDate <= @RefDate.
Any suggestion will be appreciated.