Case IS NULL ? - 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

General SQL Server Forums

New to SQL Server Programming

Case IS NULL ?

Author

Topic

mosiki
Starting Member

12 Posts

Posted - 2013-06-19 : 09:06:35

Hi

When I run the below code I get the below result set.

Team Manager Appointed Terminated Days Season Competition Home Games Win Draw Loss
Aberdeen Craig Brown 2012-07-31 2013-03-14 226 2012-13 Scottish Premier 16 4 8 4
Aberdeen Derek McInnes 2013-03-25 NULL NULL 2012-13 Scottish Premier 3 2 1 0

The first NULL in the second row is fine as no termination date exists. However, the second NULL i dont expect, rather instead have a number as the difference between todays date and the date appointed. Any ideas?

SELECT Soccer_Base.dbo.Managers.ManagerTeam as Team
,Soccer_Base.dbo.Managers.ManagerName as Manager
,Soccer_Base.dbo.Managers.ManagerAppointed as Appointed
,Soccer_Base.dbo.Managers.ManagerTerminated as Terminated
,CASE Soccer_Base.dbo.Managers.ManagerTerminated
WHEN NULL THEN
DATEDIFF(DD,ManagerAppointed,GETDATE()) ELSE
DATEDIFF(DD,ManagerAppointed,ManagerTerminated)
END AS Days ,Soccer_Base.dbo.Results.Season
,Soccer_Base.dbo.Results.Competition
,COUNT(ManagerName) as [Home Games]
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Win
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Draw
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Loss

FROM Soccer_Base.dbo.Managers JOIN Soccer_Base.dbo.Results
ON Soccer_Base.dbo.Managers.ManagerTeam =
Soccer_Base.dbo.Results.HomeTeam

WHERE Soccer_Base.dbo.Managers.ManagerAppointed <= Soccer_Base.dbo.Results.Date
AND (Soccer_Base.dbo.Managers.ManagerTerminated >= Soccer_Base.dbo.Results.Date
OR Soccer_Base.dbo.Managers.ManagerTerminated IS NULL)

GROUP BY ManagerTeam
, ManagerName,ManagerAppointed, ManagerTerminated
, Season
, Competition

HAVING ManagerTeam = 'Aberdeen'

ORDER BY Soccer_Base.dbo.Managers.ManagerAppointed

GO

Many Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 09:14:47

That should be
CASE
WHEN Soccer_Base.dbo.Managers.ManagerTerminated IS NULL THEN
DATEDIFF(DD,ManagerAppointed,GETDATE()) ELSE
DATEDIFF(DD,ManagerAppointed,ManagerTerminated)
END AS Days

--
Chandu

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 09:17:34

It could be caused by your where clause on the manager_terminated field.

I prefer using built-in functions as opposed to case statements when handling nulls,
You can use ISNULL or COALESCE

SELECT Soccer_Base.dbo.Managers.ManagerTeam as Team
,Soccer_Base.dbo.Managers.ManagerName as Manager
,Soccer_Base.dbo.Managers.ManagerAppointed as Appointed
,Soccer_Base.dbo.Managers.ManagerTerminated as Terminated
,DATEDIFF(DD,ManagerAppointed,COALESCE(ManagerTerminated, GETDATE())) AS Days
,Soccer_Base.dbo.Results.Season
,Soccer_Base.dbo.Results.Competition
,COUNT(ManagerName) as [Home Games]
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Win
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Draw
,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS Loss

FROM Soccer_Base.dbo.Managers JOIN Soccer_Base.dbo.Results
ON Soccer_Base.dbo.Managers.ManagerTeam =
Soccer_Base.dbo.Results.HomeTeam

WHERE Soccer_Base.dbo.Managers.ManagerAppointed <= Soccer_Base.dbo.Results.Date
AND COALESCE(Soccer_Base.dbo.Managers.ManagerTerminated, GETDATE()) >= Soccer_Base.dbo.Results.Date

GROUP BY ManagerTeam
, ManagerName,ManagerAppointed, ManagerTerminated
, Season
, Competition

HAVING ManagerTeam = 'Aberdeen'

ORDER BY Soccer_Base.dbo.Managers.ManagerAppointed

Duane.
http://ditchiecubeblog.wordpress.com/

mosiki
Starting Member

12 Posts

Posted - 2013-06-19 : 09:17:45

I used Bandi's edit, it works. Thanks guys.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 09:23:44

quote:
Originally posted by mosiki

I used Bandi's edit, it works. Thanks guys.

Welcome

That was the problem with IS NULL check....

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 10:38:21

Actually they're equivalent. Internally COALESCE is expanded as CASE....WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 15:46:16

Ahhh that makes sense Visakh. That would explain why it can have an array of parameters as opposed to just 2 like ISNULL.
It's just a lot easier to refer to coalesce as opposed to a big case statement.

Duane.
http://ditchiecubeblog.wordpress.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-19 : 18:10:46

Because ISNULL takes two parameters and COALESCE takes n-number of parameters. There are other subtil differences, like COALESCE is ANSI compliant, etc..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 00:51:55

and ISNULL converts the return datatype to that of first argument

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-20 : 03:52:11

Yes all valid points - there is a lot more subtle differences too, all makes for interesting reading.

Duane.
http://ditchiecubeblog.wordpress.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources