Hi
I dont want that you rewrite these queries.Only For you Inform :
I Tried 2 Times but the result was not very efficient .
I need an idea or ... I dont Know.
Perhaps some queries in an office are very important and user must wait for the result . ...
Do you have any Idea :
----------------------------------------------------------------------------------------------------
-- Main Query :
Select * From(
SELECT * , dbo.Func_CheckStatus(PAmount, DiffAmount, RealAmount) AS CheckoutStatus
FROM
( SELECT * , (PAmount - RAmount - ISNULL(RealAmount,0)) AS DiffAmount
FROM
( SELECT dbo.View_FullPaymentInfo.* ,SHC.ShipmentCode,
dbo.Func_CalcAmount(CStatus,OStatus,Amount) AS RealAmount ,
dbo.Func_GetPAmount(CId, 0) AS PAmount ,
dbo.Func_GetRAmount(CId, 0) AS RAmount
FROM dbo.View_FullPaymentInfo WITH (READUNCOMMITTED)
Cross Apply
(SELECT Max(Code) ShipmentCode
FROM Table_OrderSh WITH (READUNCOMMITTED)
WHERE OId = View_FullPaymentInfo.OId
AND dbo.Table_OrderSh.IsActive <> 0 ) SHC
) AS Paymenttmp1
) AS Paymenttmp2
) AS OrderPayment
ORDER BY CId DESC
--ORDER BY CheckoutStatus DESC --- This Ordering Kill Perfomrnace,me and user :crying:
--ORDER BY PAmount DESC --- This Ordering Kill Perfomrnace
--ORDER BY RAmount DESC --- This Ordering Kill Perfomrnace
----------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[Func_GetPAmount]
(
@CId AS INT ,
@IncludeUnderReview AS BIT
)
RETURNS BIGINT
AS
BEGIN
DECLARE @PAmount AS BIGINT;
IF ( @IncludeUnderReview = 1 )
BEGIN
SELECT @PAmount = SUM(ISNULL(Amount, 0))
FROM dbo.Table1
INNER JOIN dbo.Table2 ON ....
WHERE CId = @CId
AND ActionType = 1 --UserPay
AND [Status] IN ( 2--Confirmed
, 1 --UnderReview
)
END
ELSE
BEGIN
SELECT @PAmount = SUM(ISNULL(Amount, 0))
FROM dbo.Table1
INNER JOIN dbo.Table2 ON ....
WHERE CId = @CId
AND ActionType = 1 --UserPay
AND [Status] IN ( 2 --Confirmed
)
END
RETURN ISNULL(@PAmount,0)
END
------------------------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[Func_CheckStatus]
(
@PAmount AS BIGINT,
@DiffAmount AS BIGINT,
@RealAmount AS BIGINT
)
RETURNS TINYINT
AS
BEGIN
DECLARE @CheckoutStatus TINYINT=0
IF @DiffAmount=0
SET @CheckoutStatus=1
ELSE
IF @PAmount=0 AND (@RealAmount=ABS(@DiffAmount) AND @DiffAmount<0)
SET @CheckoutStatus=2
ELSE
IF @DiffAmount>0
SET @CheckoutStatus=3
ELSE
IF @DiffAmount<0
SET @CheckoutStatus=4
RETURN @CheckoutStatus;
END
-------------------------------------------------------------
-------------------------------------------------------------
ALTER FUNCTION [dbo].[FN_CalcuAmount]
(
@CartStatus AS TINYINT,
@OrderStatus AS TINYINT,
@Amount AS BIGINT
)
RETURNS BIGINT
AS
BEGIN
IF @OrderStatus=3 OR @CartStatus Between 8 And 11
SET @Amount=0;
RETURN @Amount;
END
-------------------------------------------------------------
-------------------------------------------------------------
ALTER FUNCTION [dbo].[FN_GetRAmount]
(
@CId AS INT ,
@IncludeUnderReview AS BIT
)
RETURNS BIGINT
AS
BEGIN
IF ( @IncludeUnderReview = 1 )
BEGIN
SELECT @RAmount = SUM(ISNULL(Amount, 0))
FROM dbo.Table1
INNER JOIN dbo.Table2 ON ....
WHERE CId = @CId
AND ActionType = 2 --UserReceived
AND [Status] IN ( 2--Confirmed
, 1 --UnderReview
)
END
ELSE
BEGIN
SELECT @RAmount = SUM(ISNULL(Amount, 0))
FROM dbo.Table1
INNER JOIN dbo.Table2 ON ....
WHERE CId = @CId
AND ActionType = 2 --UserReceived
AND [Status] IN ( 2 --Confirmed
)
END
RETURN ISNULL(@RAmount, 0)
END