kennon2000
asked on
How to get the difference (Urgent)?
Set @Receivables1 =(SELECT SUM((Total-dbo.Priv_GetInv
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_GetInv
FROM Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate < @RefDate)
IF @Receivables2 IS NULL Set @Receivables2=0
Set @ReceivablesIncrease=@Rece
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_GetInvoiceAmountP
Any suggestion will be appreciated.
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_GetInv
FROM Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate < @RefDate)
IF @Receivables2 IS NULL Set @Receivables2=0
Set @ReceivablesIncrease=@Rece
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_GetInvoiceAmountP
Any suggestion will be appreciated.
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_Rec
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID = @InvoiceID
"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_Rec
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID = @InvoiceID
So maybe:
CREATE FUNCTION Priv_GetInvoiceAmountPaidD
RETURNS money AS
BEGIN
RETURN ISNULL(( Select SUM(Item_Receipt.AmountPay
From Receipt inner join Item_Receipt on Receipt.ReceiptID=Item_Rec
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID=@In
END
CREATE FUNCTION Priv_GetInvoiceAmountPaidD
RETURNS money AS
BEGIN
RETURN ISNULL(( Select SUM(Item_Receipt.AmountPay
From Receipt inner join Item_Receipt on Receipt.ReceiptID=Item_Rec
Where Receipt.ReceiptDate = @RefDate AND Item_Receipt.InvoiceID=@In
END
ASKER
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_GetInvoiceAmountPaidD
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_GetInvoiceAmountPaidD
True, it's just a different version of the function. You would still need the call to the function:
SELECT SUM(Total - dbo.Priv_GetInvoiceAmountP
FROM Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate = @RefDate
SELECT SUM(Total - dbo.Priv_GetInvoiceAmountP
FROM Invoice
WHERE CustomerID=@CustomerID AND DueDate<@DueDate AND InvoiceDate = @RefDate
ASKER
The given answer is not correct as Priv_GetInvoiceAmountPaidD
I find the correct one by myself finally:
Set @InvoiceTotalDiff =(SELECT SUM(Total*Invoice.Exchange
FROM Invoice
WHERE CustomerID=@CustomerID AND (InvoiceDate = @RefDate) AND DueDate<@DueDate )
Set @InvoiceTotalDiff =ISNULL(@InvoiceTotal, 0)
Set @InvoiceAmountPaidDiff=(Se
From Receipt inner join Item_ReceiptMultiple on Receipt.ReceiptID=Item_Rec
Inner join Invoice on Item_Receipt.InvoiceID=Inv
Where (Receipt.ReceiptDate =@RefDate) AND Receipt.CustomerID=@Custom
Set @InvoiceAmountPaidDiff =ISNULL(@InvoiceAmountPaid
Set @ReceivablesDiff=@InvoiceT
Sorry that I will request for refund. Thanks anyway.
I find the correct one by myself finally:
Set @InvoiceTotalDiff =(SELECT SUM(Total*Invoice.Exchange
FROM Invoice
WHERE CustomerID=@CustomerID AND (InvoiceDate = @RefDate) AND DueDate<@DueDate )
Set @InvoiceTotalDiff =ISNULL(@InvoiceTotal, 0)
Set @InvoiceAmountPaidDiff=(Se
From Receipt inner join Item_ReceiptMultiple on Receipt.ReceiptID=Item_Rec
Inner join Invoice on Item_Receipt.InvoiceID=Inv
Where (Receipt.ReceiptDate =@RefDate) AND Receipt.CustomerID=@Custom
Set @InvoiceAmountPaidDiff =ISNULL(@InvoiceAmountPaid
Set @ReceivablesDiff=@InvoiceT
Sorry that I will request for refund. Thanks anyway.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_Rec
Where Receipt.ReceiptDate <=@RefDate AND Item_Receipt.InvoiceID=@In
IF @AmountPaid IS NULL
Set @AmountPaid=0
RETURN @AmountPaid
END