Condition in query - 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 2000 Forums

Transact-SQL (2000)

Condition in query

Author

Topic

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-17 : 02:50:57

Hi,

Following query was working but when i used condition of Total>0, getting error message.

I want to give condition of Total>0 in the following script but getting error. How can i give the condition of Total>0 in the following script.

SELECT empid,effdate,

MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) as CASH_Vol,
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as HSAF_Vol,
MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as Total
from #backupcurben where Total>0 --getting error message
group by empid,effdate

Please help.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 02:52:23

[code]SELECT *
FROM
(
SELECT empid,effdate,
MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) as CASH_Vol,
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as HSAF_Vol,
MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as Total
from #backupcurben
group by empid, effdate
) t
where Total > 0[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 02:55:07

SELECT	empid, effdate, CASH_Vol, HSAF_Vol, CASH_Vol + HSAF_Vol AS Total
FROM
(
	SELECT	empid,
    		effdate,
    		MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) AS CASH_Vol,
    		MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) AS HSAF_Vol
	FROM 	#backupcurben 
    	GROUP BY empid, effdate
) t
WHERE 	CASH_Vol + HSAF_Vol > 0

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:46:44

or

SELECT empid,effdate,

MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) as CASH_Vol,
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as HSAF_Vol,
MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as Total
from #backupcurben group by empid,effdate
Having MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) >0

Madhivanan

Failing to plan is Planning to fail

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-17 : 07:49:40

Hi,
This query is working. Thanks so much.

quote:
Originally posted by madhivanan

or

SELECT empid,effdate,

MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) as CASH_Vol,
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as HSAF_Vol,
MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) as Total
from #backupcurben group by empid,effdate
Having MAX(CASE type WHEN 'CASH' THEN vol ELSE 0 END) +
MAX(CASE type WHEN 'HSAF' THEN vol ELSE 0 END) >0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources