query performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query performance

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-09-10 : 07:06:13

Is this query correctly? How do I change the.
I want to give the same result.i want to modify this query for performance? Maybe im using case when operator.but how ?

SELECT
(QUANTITY) AS STOCK_ARTIR,
0 AS STOCK_AZALT,
STOCK_ID
FROM
TEST
WHERE
RESERVED = 1 AND
DEMONTAJ=0 AND
MAIN_ID IS NOT NULL
UNION ALL
SELECT
0 AS STOCK_ARTIR,
(QUANTITY) AS STOCK_AZALT,
STOCK_ID
FROM
TEST
WHERE
RESERVED = 1 AND
DEMONTAJ=1 AND
MAIN_ID IS NOT NULL
UNION ALL
SELECT
0 AS STOCK_ARTIR,
(TEST_EX.AMOUNT * PO.QUANTITY) AS STOCK_AZALT,
TEST_EX.STOCK_ID
FROM
TEST PO,
TEST_EX
WHERE
PO.IS_STOCK_RESERVED = 1 AND
TEST_EX.MAIN_ID = PO.MAIN_ID AND
PO.DEMONTAJ=0 AND
PO.MAIN_ID IS NOT NULL AND
ISNULL(TEST_EX.STOCK_ID,0)>0 AND
TEST_EX.SEVK <> 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 10:32:59

[code]
SELECT
SUM(CASE WHEN DEMONTAJ=0 THEN QUANTITY ELSE 0 END) AS STOCK_ARTIR,
SUM(CASE WHEN DEMONTAJ=1 THEN QUANTITY ELSE 0 END) AS STOCK_AZALT,
STOCK_ID
FROM
TEST
WHERE
RESERVED = 1 AND
MAIN_ID IS NOT NULL
GROUP BY STOCK_ID

UNION ALL

SELECT
0 AS STOCK_ARTIR,
SUM(TEST_EX.AMOUNT * PO.QUANTITY) AS STOCK_AZALT,
TEST_EX.STOCK_ID
FROM
TEST PO,
TEST_EX
WHERE
PO.IS_STOCK_RESERVED = 1 AND
TEST_EX.MAIN_ID = PO.MAIN_ID AND
PO.DEMONTAJ=0 AND
PO.MAIN_ID IS NOT NULL AND
TEST_EX.STOCK_ID >0 AND
TEST_EX.SEVK <> 1
GROUP BY TEST_EX.STOCK_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources