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
 General SQL Server Forums
 New to SQL Server Programming
 Query improvements

Author  Topic 

sur200
Starting Member

7 Posts

Posted - 2013-02-27 : 22:29:51
Hi,

Can someone help me in optimizing below query, is there any other way where I can improve this query

SELECT A.Col1, COUNT(B.POST_DT) AS OD_NO
FROM (SELECT Col1, col3, Col2,
SUM(CASE WHEN Col2> 0 THEN GROSS_AMOUNT ELSE DUEAMNT END) AS DUEAMNT,
SUM(CRAMNT) AS CRAMNT FROM myTbl
WHERE POST_DT< someDate AND Col2=0
GROUP BY Col1, col3, Col2
HAVING SUM(DUEAMNT) > SUM(CRAMNT)
ORDER BY Col1) A,
myTbl B
WHERE A.col3=B.col3
AND (CASE WHEN B.Col2> 0 THEN B.GROSS_AMOUNT ELSE B.DUEAMNT END)>0
GROUP BY A.Col1


TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 22:43:46
first of all you dont need ORDER BY inside subquery.
whats the purpose of CASE WHEN in WHERE?

If you could explain your requirement with some sample data and required output we might be able to provide better alternatives. As of now we cant get much idea on what you're trying to do here

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

Go to Top of Page
   

- Advertisement -