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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 Username: Password: Save Password Forgot your Password?
 All Forums  General SQL Server Forums  New to SQL Server Programming  Calculate Sum and Average Reply to Topic  Printer Friendly
Author  Topic

archana23
Yak Posting Veteran

89 Posts

 Posted - 10/08/2013 :  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 - 10/08/2013 :  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 - 10/08/2013 :  23:27:36 select distict ArrivalDate where nurse = 'Tina' gives us the unique days they worked.Archana

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 10/08/2013 :  23:48:18 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

archana23
Yak Posting Veteran

89 Posts

 Posted - 10/16/2013 :  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
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 10/16/2013 :  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

India
52326 Posts

 Posted - 10/17/2013 :  04:21:08 seehttp://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

archana23
Yak Posting Veteran

89 Posts

 Posted - 10/31/2013 :  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
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 10/31/2013 :  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 - 10/31/2013 :  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
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 10/31/2013 :  16:34:20 See replies in your other thread :)

archana23
Yak Posting Veteran

89 Posts

 Posted - 10/31/2013 :  16:39:33 Thank you James :-)It worked.Archana
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC
 This page was generated in 0.04 seconds.