Why my count doesn't work with case stmt? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Why my count doesn't work with case stmt?

Author

Topic

Syima
Starting Member

13 Posts

Posted - 2007-11-16 : 06:26:44

hello

need help. why the following sql code doesn't return % of projects. assuming i have total projects = 400, and total CF project = 40, therefore my % of CF projects = 10% instead it return 0. Any idea? I need to use the following case stmt in my query.

SELECT
CASE 
	WHEN PRJ.BDGT_CST_TOTAL<2000000 then 'LessThan2K'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then 'Between2k-5k'
	WHEN PRJ.BDGT_CST_TOTAL>=5000000 then 'MoreThan5k'
END as Budget_Range, 
count(PRJ.PRID) as No_of_Prj,

SUM (CASE WHEN ODF.mbb_carryfwdfrprvyr=1 THEN 1 ELSE 0 END) CF,
sum (CASE WHEN ODF.mbb_carryfwdfrprvyr=1 THEN 1 ELSE 0 END)/
			(
			select count(prj3.prid) from 
			prj_projects prj3, 
			odf_ca_project odf3
			where  
			prj3.prid = odf3.id and
			ODF3.mbb_projecttype = 'lkp_val_budget'
			AND
			(PRJ3.bdgt_cst_start >= 2007-07-01 OR
			 PRJ3.bdgt_cst_finish < 2008-07-01)
             )
Percent_of_Prj

FROM 
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF

WHERE 
PRJ.PRID = SRM.id AND
SRM.id = ODF.id AND
SRM.Is_Active = 1 AND
ODF.mbb_projecttype = 'lkp_val_budget' AND
(PRJ.bdgt_cst_start >= 2007-07-01 OR
PRJ.bdgt_cst_finish < 2008-07-01)

GROUP BY
CASE 
	WHEN PRJ.BDGT_CST_TOTAL<2000000 then 'LessThan2K'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then 'Between2k-5k'
	WHEN PRJ.BDGT_CST_TOTAL>=5000000 then 'MoreThan5k'
end

. Tq

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-11-16 : 07:39:04

Your numbers are being treated as integers and it's close enough to 0 that a 0 is returned. Casting your SUM as a float should work.

(It's late and I don't have an install to test this on, but I think that should do it)

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Syima
Starting Member

13 Posts

Posted - 2007-11-19 : 00:39:13

hi there,

thx for your reply. i tested n it works successfully

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources