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.
Author |
Topic |
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 16:21:34
|
Hi,I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.I am calculating Average Number of patients per day per nurse by using below querySELECT Nurse,COUNT(DISTINCT patientName) AS NoOfPatients,COUNT(DISTINCT patientName) * 1.0/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]FROM Table tGROUP BY NURSEbut if ArrivalDate is Null I am getting below error saying thatDivide by zero error encountered.How can i over come this problem?Can any one please help me on this?ThanksArchana |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-31 : 16:23:37
|
one way - if you prefer to get a NULL when arrivalDate is null is:replace: COUNT(DISTINCT ArrivalDate)with: NullIf(COUNT(DISTINCT ArrivalDate),0)Be One with the OptimizerTG |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 16:27:24
|
I have one more field in the table called DepartDate .If ArrivalDate is Null i need to check for DepartDate to calculate the AvgNo.ofpatientsPerDay.ThanksArchana |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-31 : 16:32:30
|
use COALESE functionBe One with the OptimizerTG |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-31 : 16:33:49
|
Change TG's code to this:NullIf(COUNT(DISTINCT COALESCE(ArrivalDate,DepartDate)),0) |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 16:38:26
|
Thank you James :-)It worked.Archana |
|
|
|
|
|