April 25, 2015 at 11:59 pm
Hi
How can I rewrite this in better performance :
select *, ( SELECT TOP 1 Code FROM T2 WHERE T2.Id=T1.Id ORDER BY Id DESC ) AS T2_Code
from T1
(Top 1 is problem for me)
April 26, 2015 at 3:57 am
You could try something like this (untested because you did not post DDL, sample data etc):
with OrderedSet
as (
select ro = row_number() over (partition by code order by code asc, id desc)
,t2Id = t2.Id
,t2Code = t2.Code
from t2
)
select *
from t1
join OrderedSet on t1.Id = OrderedSet.Id
where OrderedSet.ro = 1;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2015 at 4:35 am
MotivateMan1394 (4/25/2015)
HiHow can I rewrite this in better performance :
select *, ( SELECT TOP 1 Code FROM T2 WHERE T2.Id=T1.Id ORDER BY Id DESC ) AS T2_Code
from T1
(Top 1 is problem for me)
Top 1 doesn't have to be a problem or rather all depends, consider these samples
😎
USE tempdb;
GO
SET NOCOUNT ON;
/********************************************************************
Sample data set
********************************************************************/
DECLARE @SAMPLE_SIZE INT = 10000;
DECLARE @CODE_COUNT INT = 100;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_T2') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T2;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_T1') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T1;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
ISNULL(CONVERT(INT,NM.N,0),0) AS T1_ID
,REPLACE(NEWID(),'-','') AS T1_TEXT
INTO dbo.TBL_SAMPLE_T1
FROM NUMS NM;
ALTER TABLE dbo.TBL_SAMPLE_T1 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T1_T1_ID PRIMARY KEY CLUSTERED (T1_ID ASC);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE * @CODE_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
ISNULL(NM.N,0) AS T2_ID
,ISNULL(CONVERT(INT,(ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE) + 1,0),1) AS T1_ID
,SUBSTRING(REPLACE(NEWID(),'-',''),1,8) AS T2_CODE
INTO dbo.TBL_SAMPLE_T2
FROM NUMS NM;
ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T2_T2_ID PRIMARY KEY CLUSTERED (T2_ID ASC);
ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT FK_DBO_TBL_SAMPLE_T2_T1_ID_DBO_TBL_SAMPLE_T1_T1_ID FOREIGN KEY (T1_ID) REFERENCES dbo.TBL_SAMPLE_T1( T1_ID );
/********************************************************************
Test harness
********************************************************************/
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET CHAR(10) = '';
RAISERROR(N'---------------------------------------------------------------------
SUBQUERY METHOD NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = (SELECT
TOP(1) T2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 T2
WHERE T1.T1_ID = T2.T1_ID
ORDER BY T2.T2_ID DESC) --AS T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
CROSS APPLY METHOD NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = TX.T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1
CROSS APPLY (
SELECT
T2.T1_ID
,MAX(T2.T2_ID) AS T2_ID
FROM dbo.TBL_SAMPLE_T2 T2
GROUP BY T2.T1_ID
) AS X
CROSS APPLY dbo.TBL_SAMPLE_T2 TX
WHERE T1.T1_ID = X.T1_ID
AND X.T2_ID = TX.T2_ID;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
PP WINDOW SET NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH OrderedSet AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY t2.T1_ID
ORDER BY t2.T2_CODE asc
,t2.T2_ID desc
) AS T2_RID
,T2.T1_ID
,t2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 t2
)
SELECT
@INT_BUCKET = t1.T1_ID
,@CHR_BUCKET = OS.T2_CODE
FROM dbo.TBL_SAMPLE_T1 t1
INNER JOIN OrderedSet OS
ON t1.T1_ID = OS.T1_ID
WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;
CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_TBL_SAMPLE_T2_T1_ID_T2_ID_INCL_T2_CODE ON dbo.TBL_SAMPLE_T2
( T1_ID ASC, T2_ID DESC )
INCLUDE
( T2_CODE);
RAISERROR(N'---------------------------------------------------------------------
SUBQUERY METHOD WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = (SELECT
TOP(1) T2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 T2
WHERE T1.T1_ID = T2.T1_ID
ORDER BY T2.T2_ID DESC) --AS T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
CROSS APPLY METHOD WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = TX.T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1
CROSS APPLY (
SELECT
T2.T1_ID
,MAX(T2.T2_ID) AS T2_ID
FROM dbo.TBL_SAMPLE_T2 T2
GROUP BY T2.T1_ID
) AS X
CROSS APPLY dbo.TBL_SAMPLE_T2 TX
WHERE T1.T1_ID = X.T1_ID
AND X.T2_ID = TX.T2_ID;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
PP WINDOW SET WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH OrderedSet AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY t2.T1_ID
ORDER BY t2.T2_CODE asc
,t2.T2_ID desc
) AS T2_RID
,T2.T1_ID
,t2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 t2
)
SELECT
@INT_BUCKET = t1.T1_ID
,@CHR_BUCKET = OS.T2_CODE
FROM dbo.TBL_SAMPLE_T1 t1
INNER JOIN OrderedSet OS
ON t1.T1_ID = OS.T1_ID
WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;
Output
---------------------------------------------------------------------
SUBQUERY METHOD NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 546391, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6614 ms, elapsed time = 6642 ms.
---------------------------------------------------------------------
CROSS APPLY METHOD NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 9 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34731, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 250 ms.
---------------------------------------------------------------------
PP WINDOW SET NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 3 ms, elapsed time = 3 ms.
Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 4139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3806 ms, elapsed time = 1016 ms.
---------------------------------------------------------------------
SUBQUERY METHOD WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TBL_SAMPLE_T2'. Scan count 10000, logical reads 31945, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 36 ms.
---------------------------------------------------------------------
CROSS APPLY METHOD WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 12 ms, elapsed time = 12 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34367, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 196 ms.
---------------------------------------------------------------------
PP WINDOW SET WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 3779, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3168 ms, elapsed time = 857 ms.
Edit: added Phil's method
April 26, 2015 at 5:49 am
You could try something like this ...
Your Query Return Only One record
And My Query Return records equal the T1 Records Number.
April 26, 2015 at 6:17 am
MotivateMan1394 (4/26/2015)
You could try something like this ...
Your Query Return Only One record
And My Query Return records equal the T1 Records Number.
First of all, Phil's query does not only return the correct results but it's also around 6 times more efficient than the original one, given there is no index satisfying the query.
😎
Secondly, do post the DDL (create tables), sample data in the form of an insert statement and the expected results. The absence of these seriously affect the accuracy of the responses as everyone has to guess what it could look like.
April 27, 2015 at 3:09 am
Have you looked at the execution plan for the query? That will tell you why it's running slow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2015 at 4:56 am
MotivateMan1394 (4/26/2015)
You could try something like this ...
Your Query Return Only One record
And My Query Return records equal the T1 Records Number.
First a minor terminology note. RDBs operates on sets of rows not records.The result of table expressions is a set of rows. Records are parts of recordsets returned by DB to applications.
LEFT JOIN i suppose.
with OrderedSet
as (
select ro = row_number() over (partition by code order by code asc, id desc)
,t2Id = t2.Id
,t2Code = t2.Code
from t2
)
select *
from t1
left join OrderedSet on t1.Id = OrderedSet.Id and OrderedSet.ro = 1;
April 29, 2015 at 4:20 am
First a minor terminology note. RDBs operates on sets of rows not records.The result of table expressions is a set of rows. Records are parts of recordsets returned by DB to applications.
Thank you for your hints .
But in continue :
I think it was my fault
That it did not clearly explain
This is my DDL's And some data sample
CREATE TABLE [dbo].[T1](
[Id] [int] NULL,
[Desc] [nchar](100) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[T2](
[T1Id] [int] NULL,
[Id] [int] NULL,
[Code] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[T1] ([Id], [Desc]) VALUES (1, N'Desc1-T1')
INSERT [dbo].[T1] ([Id], [Desc]) VALUES (2, N'Desc2-T1')
INSERT [dbo].[T1] ([Id], [Desc]) VALUES (3, N'Desc3-T1')
INSERT [dbo].[T1] ([Id], [Desc]) VALUES (4, N'Desc3-T1')
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (1, 1, 11)
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (2, 2, 12)
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (2, 3, 13)
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (2, 4, 14)
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (3, 5, 15)
INSERT [dbo].[T2] ([T1Id], [Id], [Code]) VALUES (3, 6, 16)
And this is my Query :
select *,(Select Top 1 Code From T2 where T1.id=T2.T1id Order By code) from T1
which result of it is different from "With ..." Suggestions.
April 29, 2015 at 5:54 am
select *
from dbo.T1 t1
cross apply (select Code = min(Code)
from T2
where t2.T1Id = t1.Id
) t2;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 3, 2015 at 12:08 am
Ok
The results are equal
and your query has 24 cost in comparition 76 cost of my query.
more over :
the Eirikur Eiriksson's queries are very complete.
Thank you All
May 3, 2015 at 1:02 am
MotivateMan1394 (5/3/2015)
OkThe results are equal
and your query has 24 cost in comparition 76 cost of my query.
more over :
the Eirikur Eiriksson's queries are very complete.
Thank you All
You are very welcome, and thanks for the feedback.
😎
May 3, 2015 at 6:23 am
Hi again
I have 2 Functions in my Query. (then 2 columns in my query's output)
Function 1 : calculate a value on check a state field. (get values from 2 tables)
Function 2 : is a case that choose a Value from Filed a or field b . (based on ... )
Then Sometimes I need to sort my query on base of one of these funcs' Value. ... Order By ... And that time Performance Really Dead .:crazy:
(The funcs are on subquery and I sort the upper query on function's output in subquery)
Is there any methodologhy that I Avoid functions, Or I should Rewrite the query And Replace Functions with joins ...And what about order fields?
Thank you
May 3, 2015 at 6:44 am
MotivateMan1394 (5/3/2015)
Hi againI have 2 Functions in my Query. (then 2 columns in my query's output)
Function 1 : calculate a value on check a state field. (get values from 2 tables)
Function 2 : is a case that choose a Value from Filed a or field b . (based on ... )
Then Sometimes I need to sort my query on base of one of these funcs' Value. ... Order By ... And that time Performance Really Dead .:crazy:
(The funcs are on subquery and I sort the upper query on function's output in subquery)
Is there any methodologhy that I Avoid functions, Or I should Rewrite the query And Replace Functions with joins ...And what about order fields?
Thank you
Can you post the functions' code, preferably with full DDL and sample data in the form of an insert statement? Also it is probably better to start a new thread on this to catch wider audience.
😎
May 3, 2015 at 7:23 am
MotivateMan1394 (5/3/2015)
Hi againI have 2 Functions in my Query. (then 2 columns in my query's output)
Function 1 : calculate a value on check a state field. (get values from 2 tables)
Function 2 : is a case that choose a Value from Filed a or field b . (based on ... )
Then Sometimes I need to sort my query on base of one of these funcs' Value. ... Order By ... And that time Performance Really Dead .:crazy:
(The funcs are on subquery and I sort the upper query on function's output in subquery)
Is there any methodologhy that I Avoid functions, Or I should Rewrite the query And Replace Functions with joins ...And what about order fields?
Thank you
It all goes back to how the functions are written, how they're being called and how the optimizer resolves them. Just talking about functions, without context, it's impossible to give advice that has any meaning. If your functions are in-line functions, depending on how they're written, they may be OK. If your functions are multi-statement table valued functions, yes, you should replace them with anything else.
ORDER BY processing is fine as long as the indexes support it, otherwise, it could lead to scans, or it means additional load in tempdb, memory and the processor as the information is reordered. But, again, as with all things inside t-sql, when talking about it out of context, you can only make vague suggestions.
It's the details on all this that makes the difference. What's perfectly OK in one situation could be very problematic in the next. Except for a clear need to avoid multi-statement table valued functions everywhere, there really are few hard and fast rules within T-SQL that are universally applicable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2015 at 11:43 pm
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply