Author |
Topic |
mosiki
Starting Member
12 Posts |
Posted - 2013-06-19 : 09:06:35
|
HiWhen I run the below code I get the below result set.Team Manager Appointed Terminated Days Season Competition Home Games Win Draw LossAberdeen Craig Brown 2012-07-31 2013-03-14 226 2012-13 Scottish Premier 16 4 8 4Aberdeen Derek McInnes 2013-03-25 NULL NULL 2012-13 Scottish Premier 3 2 1 0The 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.ManagerAppointedGOMany Thanks |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 09:14:47
|
That should beCASE WHEN Soccer_Base.dbo.Managers.ManagerTerminated IS NULL THENDATEDIFF(DD,ManagerAppointed,GETDATE()) ELSEDATEDIFF(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 COALESCESELECT 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.ManagerAppointedDuane.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 MVPhttp://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 MVPhttp://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/ |
|
|
|