Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Case IS NULL ?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mosiki
Starting Member

12 Posts

Posted - 06/19/2013 :  09:06:35  Show Profile  Reply with Quote
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

Edited by - mosiki on 06/19/2013 09:10:24

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 06/19/2013 :  09:14:47  Show Profile  Reply with Quote
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
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  09:17:34  Show Profile  Visit ditch's Homepage  Reply with Quote
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/
Go to Top of Page

mosiki
Starting Member

12 Posts

Posted - 06/19/2013 :  09:17:45  Show Profile  Reply with Quote
I used Bandi's edit, it works. Thanks guys.

Edited by - mosiki on 06/19/2013 09:20:01
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 06/19/2013 :  09:23:44  Show Profile  Reply with Quote
quote:
Originally posted by mosiki

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


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

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/19/2013 :  10:38:21  Show Profile  Reply with Quote
Actually they're equivalent. Internally COALESCE is expanded as CASE....WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  15:46:16  Show Profile  Visit ditch's Homepage  Reply with Quote
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/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/19/2013 :  18:10:46  Show Profile  Reply with Quote
Because ISNULL takes two parameters and COALESCE takes n-number of parameters. There are other subtil differences, like COALESCE is ANSI compliant, etc..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/20/2013 :  00:51:55  Show Profile  Reply with Quote
and ISNULL converts the return datatype to that of first argument


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/20/2013 :  03:52:11  Show Profile  Visit ditch's Homepage  Reply with Quote
Yes all valid points - there is a lot more subtle differences too, all makes for interesting reading.

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page
  Previous Topic Topic Next 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