• Status: Solved
• Priority: Medium
• Security: Public
• Views: 183

# How to get the difference (Urgent)?

Set @Receivables1 =(SELECT SUM((Total-dbo.Priv_GetInvoiceAmountPaid(InvoiceID, @RefDate)))
FROM     Invoice
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)))
FROM     Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate < @RefDate)
IF @Receivables2 IS NULL Set @Receivables2=0

Set @ReceivablesIncrease=@Receivables1-@Receivables2

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.
0
kennon2000
• 3
• 3
1 Solution

Author Commented:
Here is the UDF:

CREATE FUNCTION Priv_GetInvoiceAmountPaid (@InvoiceID int, @RefDate DateTime)
RETURNS money AS
BEGIN
DECLARE      @AmountPaid [money]
Set @AmountPaid=(Select SUM(Item_Receipt.AmountPay)
From Receipt inner join Item_Receipt on Receipt.ReceiptID=Item_Receipt.ReceiptID
Where Receipt.ReceiptDate <=@RefDate AND Item_Receipt.InvoiceID=@InvoiceID )

IF @AmountPaid IS NULL
Set @AmountPaid=0

RETURN @AmountPaid

END
0

Senior DBACommented:
But the *only* difference between the two queries is
"InvoiceDate < @RefDate"
vs.
"InvoiceDate <= @RefDate"

So the difference should always be returned by a similar function with this query:

Select SUM(Item_Receipt.AmountPay)
From Receipt inner join Item_Receipt on Receipt.ReceiptID=Item_Receipt.ReceiptID
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID = @InvoiceID
0

Senior DBACommented:
So maybe:

CREATE FUNCTION Priv_GetInvoiceAmountPaidDiff (@InvoiceID int, @RefDate DateTime)
RETURNS money AS
BEGIN
RETURN ISNULL(( Select SUM(Item_Receipt.AmountPay)
From Receipt inner join Item_Receipt on Receipt.ReceiptID=Item_Receipt.ReceiptID
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID=@InvoiceID ), 0)
END
0

Author Commented:
It seem not that simple.
How about the difference of Total on @RefDate?
Also, pls note that only invoices satisifying criteria CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate <= @RefDate will be sum up.  But the criteria is not considered in the Priv_GetInvoiceAmountPaidDiff
0

Senior DBACommented:
True,  it's just a different version of the function.  You would still need the call to the function:

SELECT SUM(Total - dbo.Priv_GetInvoiceAmountPaidDiff(InvoiceID, @RefDate))
FROM     Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate = @RefDate
0

Author Commented:
The given answer is not correct as Priv_GetInvoiceAmountPaidDiff(InvoiceID, @RefDate) is always equal zero with criteria InvoiceDate=@RefDate and the corresponding InvoiceID.

I find the correct one by myself finally:

Set @InvoiceTotalDiff =(SELECT SUM(Total*Invoice.ExchangeRate)
FROM     Invoice
WHERE    CustomerID=@CustomerID AND (InvoiceDate = @RefDate) AND DueDate<@DueDate )
Set @InvoiceTotalDiff =ISNULL(@InvoiceTotal, 0)

Set @InvoiceAmountPaidDiff=(Select SUM(Item_ReceiptMultiple.AmountPay*Receipt.ExchangeRate)
From Receipt inner join Item_ReceiptMultiple on Receipt.ReceiptID=Item_ReceiptMultiple.ReceiptID
Inner join Invoice on Item_Receipt.InvoiceID=Invoice.InvoiceID
Where (Receipt.ReceiptDate =@RefDate) AND Receipt.CustomerID=@CustomerID AND  DueDate<@DueDate )
Set @InvoiceAmountPaidDiff =ISNULL(@InvoiceAmountPaid, 0)

Set @ReceivablesDiff=@InvoiceTotalDiff -@InvoiceAmountPaidDiff

Sorry that I will request for refund.  Thanks anyway.
0

Commented: