Solved

# How to get the difference (Urgent)?

Posted on 2005-04-07
179 Views
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
Question by:kennon2000

Author Comment

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

LVL 68

Expert Comment

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

LVL 68

Expert Comment

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 Comment

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

LVL 68

Expert Comment

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 Comment

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

Accepted Solution

Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
0

## Featured Post

I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.