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
 Calculate Sum and Average
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

74 Posts

Posted - 10/08/2013 :  17:07:15  Show Profile  Reply with Quote
Hi,

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.

I need to calculate Number of patients per Nurse and Average number of patients per day per nurse.

We need to calculate Average Number of patients per day per nurse = Total Patients per nurse/No.of unique days they worked

I need my report as like this.

DistinctNurse No.ofPatients AvgNo.ofpatients PerDay
Tina 100 25
Sony 50 16.6

Can any one please help me on this to get the result as above.

Thank you.

Archana

bitsmed
Constraint Violating Yak Guru

390 Posts

Posted - 10/08/2013 :  17:46:16  Show Profile  Reply with Quote
To do this, I would think you need the nurses workschedule. How else would you find out "no. of Unique days they worked"?
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 10/08/2013 :  23:27:36  Show Profile  Reply with Quote
select distict ArrivalDate where nurse = 'Tina' gives us the unique days they worked.



Archana
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/08/2013 :  23:48:18  Show Profile  Reply with Quote
Based on details you provided this is how you can get the result

SELECT Nurse,
COUNT(DISTINCT patientName) AS NoOfPatients,
COUNT(DISTINCT patientName)/COUNT(*) AS [AvgNo.ofpatients PerDay]
FROM Table t
CROSS JOIN master..spt_values v
WHERE DATEADD(dd,v.number,ArrivalDate) BETWEEN ArrivalDate AND DepartDate
AND v.type='p'
GROUP BY Nurse

However for accurate Avg result you need attendance details of Nurse also

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

archana23
Yak Posting Veteran

74 Posts

Posted - 10/16/2013 :  16:58:02  Show Profile  Reply with Quote
Hi Vishak,

Thank you for your reply.

I tried using below query to get the Average no.of patients

SELECT Nurse,
COUNT(DISTINCT patientName) AS NoOfPatients,
COUNT(DISTINCT patientName)/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]
FROM Table t
GROUP BY NURSE

is this gives right information.. If so how i wil get AvgNo.ofpatientsPerDay as decmal value, righ now i am getting integer only.

For example : 17/3 -- gives only 5 instead of 5.66
3/2 -- gives only 1 instead of 1.5

how do i get 5.66 and 1.5 instead of 5 and 1.

Thank you.


Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 10/16/2013 :  17:06:52  Show Profile  Reply with Quote
This is because of integer division. Force it to floating point division like this:
...
1.0*COUNT(DISTINCT patientName)/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]
...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/17/2013 :  04:21:08  Show Profile  Reply with Quote
see

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

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

archana23
Yak Posting Veteran

74 Posts

Posted - 10/31/2013 :  15:48:44  Show Profile  Reply with Quote
I am getting below error if ArrivalDate is null

Divide by zero error encountered.

How can i over come this problem?

Can any one help me on this?

Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 10/31/2013 :  16:20:30  Show Profile  Reply with Quote
What do you want to get if there are no Arrival dates? This will fix the problem, but will give you NULL when there are no arrivaldates.
...
1.0*COUNT(DISTINCT patientName)/NULLIF(COUNT(DISTINCT ArrivalDate),0) AS [AvgNo.ofpatientsPerDay]
...

Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 10/31/2013 :  16:24:20  Show Profile  Reply with Quote
If ArrivalDate is Null i need to check for DepartDate to get the No.of unique days they worked.

Thanks

Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 10/31/2013 :  16:34:20  Show Profile  Reply with Quote
See replies in your other thread :)
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 10/31/2013 :  16:39:33  Show Profile  Reply with Quote
Thank you James :-)

It worked.

Archana
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.08 seconds. Powered By: Snitz Forums 2000