Author |
Topic |
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-08 : 17:07:15
|
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 workedI 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
Aged Yak Warrior
545 Posts |
Posted - 2013-10-08 : 17:46:16
|
To do this, I would think you need the nurses workschedule. How else would you find out "no. of Unique days they worked"? |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-08 : 23:27:36
|
select distict ArrivalDate where nurse = 'Tina' gives us the unique days they worked.Archana |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-08 : 23:48:18
|
Based on details you provided this is how you can get the resultSELECT Nurse,COUNT(DISTINCT patientName) AS NoOfPatients,COUNT(DISTINCT patientName)/COUNT(*) AS [AvgNo.ofpatients PerDay]FROM Table tCROSS JOIN master..spt_values vWHERE DATEADD(dd,v.number,ArrivalDate) BETWEEN ArrivalDate AND DepartDateAND v.type='p'GROUP BY Nurse However for accurate Avg result you need attendance details of Nurse also------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-16 : 16:58:02
|
Hi Vishak,Thank you for your reply.I tried using below query to get the Average no.of patientsSELECT Nurse,COUNT(DISTINCT patientName) AS NoOfPatients,COUNT(DISTINCT patientName)/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]FROM Table tGROUP BY NURSEis 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.5how do i get 5.66 and 1.5 instead of 5 and 1.Thank you.Archana |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-16 : 17:06:52
|
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]... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 15:48:44
|
I am getting below error if ArrivalDate is nullDivide by zero error encountered.How can i over come this problem?Can any one help me on this?Archana |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-31 : 16:20:30
|
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]... |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 16:24:20
|
If ArrivalDate is Null i need to check for DepartDate to get the No.of unique days they worked.ThanksArchana |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-31 : 16:34:20
|
See replies in your other thread :) |
|
|
archana23
Yak Posting Veteran
89 Posts |
Posted - 2013-10-31 : 16:39:33
|
Thank you James :-)It worked.Archana |
|
|
|