help with case statement - 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 2005 Forums

Transact-SQL (2005)

help with case statement

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2009-07-22 : 05:40:59

Hi there,

I'm trying to use the following in a sql statement, but can't get the syntax correct.

CASE isdate(fldCompleted) WHEN 'true' THEN 1 WHEN 'false' THEN 0 END

What I'm trying to say is if the field fldCompleted has a date in it then mark it as a 1 if it is a NULL then mark it as 0.

I then sum them to give me a total.

Here is the whole sql I have, you can see there are similar ones that do work.

SELECT TOP (100) PERCENT fldDateofEnquiry AS strDate, SUM(1) AS total, SUM(CASE fldOpen WHEN 'y' THEN 1 WHEN 'n' THEN 0 WHEN NULL THEN 0 END) AS opened, SUM(CASE fldClick WHEN 'y' THEN 1 WHEN 'n' THEN 0 WHEN NULL THEN 0 END) AS clicked, SUM(CASE isdate(fldCompleted) WHEN 'true' THEN 1 WHEN 'false' THEN 0 END) AS completed, MONTH(fldDateofEnquiry) AS month, SUM(isdate(fldCompleted)) AS Expr1
FROM dbo.wecaredata
GROUP BY fldDateofEnquiry, fldContactby, fldType, MONTH(fldDateofEnquiry)
HAVING (fldContactby = 'P') AND (fldDateofEnquiry > CONVERT(DATETIME, '2009-07-01 00:00:00', 102)) AND (fldType = 'walkin')
ORDER BY strDate

Can anyone see what is wrong with it?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-22 : 05:46:23

CASE WHEN isdate(fldCompleted) = 1 THEN 1 ELSE 0 END