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

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
Asked:
kennon2000
  • 3
  • 3
1 Solution
 
kennon2000Author 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
 
Scott PletcherSenior 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
 
Scott PletcherSenior 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
kennon2000Author 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
 
Scott PletcherSenior 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
 
kennon2000Author 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
 
ee_ai_constructCommented:
Question answered by asker or dialog deemed valuable.
Closed, 250 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now