Spring 2012 Assignment 3 – Instructor Solution

Spring 2012 Assignment 3 – Instructor Solution

1. After downloading the data I first removed all the rows that had N/A in them (cleaned the spreadsheet) and then imported them. I changed the names of the columns to make it a bit cleaner. The final table I ended up with was this.

CREATE TABLE [dbo].[RawData](
    [Provider Number] [float] NULL,
    [Hospital Name] [nvarchar](255) NULL,
    [Address 1] [nvarchar](255) NULL,
    [Address 2] [nvarchar](255) NULL,
    [Address 3] [nvarchar](255) NULL,
    [City] [nvarchar](255) NULL,
    [State] [nvarchar](255) NULL,
    [ZIP Code] [float] NULL,
    [County Name] [nvarchar](255) NULL,
    [Phone Number] [float] NULL,
    [CommunicationNurse1] [float] NULL,
    [CommunicationNurse2] [float] NULL,
    [CommunicationNurse3] [float] NULL,
    [CommunicationDoctor1] [float] NULL,
    [CommunicationDoctor2] [float] NULL,
    [CommunicationDoctor3] [float] NULL,
    [Help1] [float] NULL,
    [Help2] [float] NULL,
    [Help3] [float] NULL,
    [Pain1] [float] NULL,
    [Pain2] [float] NULL,
    [Pain3] [float] NULL,
    [MedicationExplain1] [float] NULL,
    [MedicationExplain2] [float] NULL,
    [MedicationExplain3] [float] NULL,
    [CleanRoom1] [float] NULL,
    [CleanRoom2] [float] NULL,
    [CleanRoom3] [float] NULL,
    [Quiet1] [float] NULL,
    [Quiet2] [float] NULL,
    [Quiet3] [float] NULL,
    [RecoveryYes] [float] NULL,
    [RecoveryNo] [float] NULL,
    [Rating0-6] [float] NULL,
    [Rating7-8] [float] NULL,
    [Rating9-10] [float] NULL,
    [NotRecommend] [float] NULL,
    [ProbablyRecommend] [float] NULL,
    [YesRecommend] [float] NULL,
    [Number of Completed Surveys] [nvarchar](255) NULL,
    [Survey Response Rate Percent] [float] NULL,
    [Hospital Footnote] [nvarchar](255) NULL
) ON [PRIMARY]

2. The formula is really not that hard to do, you can this as shown in the Function below. I chose to do these as scalar functions since they return a specific value.

CREATE FUNCTION comScoreNurses 
(
    @state VARCHAR(2)
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @n1 float
    DECLARE @n2 float
    DECLARE @n3 float
    DECLARE @v float
    SELECT @n1 = ISNULL((SELECT AVG(CommunicationNurse1) FROM RawData Where [State] = @State), 0)
    SELECT @n2 = ISNULL((SELECT AVG(CommunicationNurse2) FROM RawData Where [State] = @state), 0) 
    SELECT @n3 = ISNULL((SELECT AVG(CommunicationNurse3) FROM RawData Where [State] = @state), 0) 
    SET @v = 5*@n2 + 10*@n3
    RETURN @V
END
GO

3. The other functions will be essentially the same – just simply pulling data from different tables. Since I used functions I can now embed calls into queries – such as this – with results

SELECT Distinct([State]), 
  CAST(dbo.comScoreNurses([State]) AS Numeric(4,2)) AS 'Nurse Communication Score'
FROM RawData
GROUP BY [State]

Also please note I did NOT include Florida – you will with your assignment. So as a simple point of curiosity who do you thing scored higher on the communication scoring. Well – this is easy to see.

SELECT Distinct([State]), 
  CAST(dbo.comScoreDoctors([State]) AS Numeric(4,2)) AS 'Doctor',
  CAST(dbo.comScoreNurses([State]) AS Numeric(4,2)) AS 'Nurse'
FROM RawData
GROUP BY [State]

Results (doctors win by a hair)

State Doctor  Nurse
AK    8.64    8.49
AL    9.15    8.65
AR    8.99    8.56
AZ    8.47    8.43
CA    8.50    8.21
CO    8.78    8.65
CT    8.64    8.51
DC    8.47    7.92
DE    8.64    8.52