SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query improvements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sur200
Starting Member

India
7 Posts

Posted - 02/27/2013 :  22:29:51  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/27/2013 :  22:43:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000