SQL Server Forums
Profile | Register | 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 ?
 New Topic  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
2224 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
2224 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
52325 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
52325 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  
 New 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.07 seconds. Powered By: Snitz Forums 2000