Server name:  bhamquants.c6s2trc0yx7t.us-east-2.rds.amazonaws.com,1433
Authentication:  SQL Server Authentication
Login:  BhamQuants##
Password:  NickIsCool
Valid through:  About 5/16/19

BhamQuants01
BhamQuants02
BhamQuants03
BhamQuants04
BhamQuants05
BhamQuants06
BhamQuants07
BhamQuants08
BhamQuants09
BhamQuants10
BhamQuants11
BhamQuants12




--Return everything
Select
     *
From
     dbo.Hospitals





--Top
Select
     Top 100
     *
From
     dbo.Hospitals





--Restricted Return
Select
     Top 100
     Hospital_Name
    ,Provider_ID
    ,City
    ,State
    ,ZIP_Code
    ,Hospital_Ownership
    ,Hospital_Overall_Rating
    ,Mortality_national_comparison
    ,Safety_of_care_national_comparison
    ,Readmission_national_comparison
    ,Patient_experience_national_comparison
    ,Effectiveness_of_care_national_comparison
    ,*
From
     dbo.Hospitals





--Where Clause
Select
     Top 100
     Hospital_Name
    ,Provider_ID
    ,City
    ,State
    ,ZIP_Code
    ,Hospital_Ownership
    ,Hospital_Overall_Rating
    ,Mortality_national_comparison
    ,Safety_of_care_national_comparison
    ,Readmission_national_comparison
    ,Patient_experience_national_comparison
    ,Effectiveness_of_care_national_comparison
    --,*
From
     dbo.Hospitals
Where
     State = 'AL'





--Left
Select
     Top 100
     Hospital_Name
    ,Provider_ID
    ,City
    ,State
    ,ZIP_Code
    ,Hospital_Ownership
    ,Hospital_Overall_Rating
    ,Mortality_national_comparison
    ,Safety_of_care_national_comparison
    ,Readmission_national_comparison
    ,Patient_experience_national_comparison
    ,Effectiveness_of_care_national_comparison
    --,*
From
     dbo.Hospitals
Where
     State = 'AL'
 And Left(ZIP_Code,3) = '352'





--IN
Select
     Top 100
     Hospital_Name
    ,Provider_ID
    ,City
    ,State
    ,ZIP_Code
    ,Hospital_Ownership
    ,Hospital_Overall_Rating
    ,Mortality_national_comparison
    ,Safety_of_care_national_comparison
    ,Readmission_national_comparison
    ,Patient_experience_national_comparison
    ,Effectiveness_of_care_national_comparison
    --,*
From
     dbo.Hospitals
Where
     Provider_ID In ('010033','010056','010139','010104')





--New Table
Select
     Top 100
     *
From
     dbo.HAI
Where
     Provider_ID = '010033' --UAB





--Like
Select
     Top 100
     *
From
     dbo.HAI
Where
     Provider_ID = '010033' --UAB
 And Measure_ID Like '%SIR'





--Group By
Select
     --Top 100
     Provider_ID
    ,Hospital_Name
    ,Avg(Score) AS Score
     --*
From
     dbo.HAI
Where
     Provider_ID In ('010033','010056','010139','010104')
 And Measure_ID Like '%SIR'
Group by
     Provider_ID
    ,Hospital_Name





--Look at all of the tables
Select * From dbo.Hospitals Where Provider_ID = '010033'
Select * From dbo.Deaths Where Provider_ID = '010033' And Measure_ID = 'PSI_15_ACC_LAC'
Select * From dbo.HCAHPS Where Provider_ID = '010033' And Right (HCAHPS_Measure_ID,11) = 'STAR_RATING' And HCAHPS_Measure_ID = 'H_HSP_RATING_STAR_RATING'
Select * From dbo.HAI Where Provider_ID = '010033' And Measure_ID Like '%SIR'





--Simplifed Look
Select
     Hospital_Name
    ,Provider_ID
    ,City
    ,State
    ,ZIP_Code
    ,Hospital_Ownership
    ,Hospital_Overall_Rating
    ,Mortality_national_comparison
    ,Safety_of_care_national_comparison
From
     dbo.Hospitals
Where
     Provider_ID = '010033'

Select
     Provider_ID
    ,Measure_Name
    ,Score
From
     dbo.Deaths
Where
     Provider_ID = '010033'
 And Measure_ID = 'PSI_15_ACC_LAC'

Select
     Provider_ID
    ,HCAHPS_Question
    ,Patient_Survey_Star_Rating
From
     dbo.HCAHPS
Where
     Provider_ID = '010033'
 And HCAHPS_Measure_ID = 'H_HSP_RATING_STAR_RATING'

Select
     --Top 100
     Provider_ID
    ,Hospital_Name
    ,Avg(Score) AS Score
     --*
From
     dbo.HAI
Where
     Provider_ID = '010033'
 And Measure_ID Like '%SIR'
Group by
     Provider_ID
    ,Hospital_Name





--Left Join 2 Tables
--Go to PowerPoint
Select
     Hospitals.Hospital_Name
    ,Hospitals.Provider_ID
    ,Hospitals.City
    ,Hospitals.State
    ,Hospitals.ZIP_Code
    ,Hospitals.Hospital_Ownership
    ,Hospitals.Hospital_Overall_Rating
    ,Hospitals.Mortality_national_comparison
    ,Hospitals.Safety_of_care_national_comparison
    ,Deaths.Measure_Name AS Deaths_Category
    ,Deaths.Score AS Deaths_Score
From
     dbo.Hospitals Hospitals  --Select Top 100 * From dbo.Hospitals
     Left Join dbo.Deaths Deaths  --Select Top 100 * From dbo.Hospitals
       On Deaths.Provider_ID = Hospitals.Provider_ID
Where
     Hospitals.Provider_ID = '010033'
 And Deaths.Measure_ID = 'PSI_15_ACC_LAC'





--Left Join 4 Tables
Select
     Hospitals.Hospital_Name
    ,Hospitals.Provider_ID
    ,Hospitals.City
    ,Hospitals.State
    ,Hospitals.ZIP_Code
    ,Hospitals.Hospital_Ownership
    ,Hospitals.Hospital_Overall_Rating
    ,Hospitals.Mortality_national_comparison
    ,Hospitals.Safety_of_care_national_comparison
    ,Deaths.Measure_Name AS Deaths_Category
    ,Deaths.Score AS Deaths_Score
    ,Deaths.Compared_to_National As Deaths_Compare
    ,HCAHPS.HCAHPS_Question
    ,HCAHPS.Patient_Survey_Star_Rating
    ,HAI.Measure_Name AS HAI_Measure
    ,HAI.Score AS HAI_Score
    ,HAI.Compared_to_National As HAI_Compare
From
     dbo.Hospitals Hospitals  --Select Top 100 * From dbo.Hospitals
     Left Join dbo.Deaths Deaths  --Select Top 100 * From dbo.Deaths
       On Deaths.Provider_ID = Hospitals.Provider_ID
     Left Join dbo.HCAHPS HCAHPS  --Select Top 100 * From dbo.HCAHPS
       On HCAHPS.Provider_ID = Hospitals.Provider_ID
     Left Join dbo.HAI HAI  --Select Top 100 * From dbo.HAI
       On HAI.Provider_ID = Hospitals.Provider_ID

Where
     Hospitals.Provider_ID = '010033'
 And Deaths.Measure_ID = 'PSI_15_ACC_LAC'
 And HCAHPS.HCAHPS_Measure_ID = 'H_HSP_RATING_STAR_RATING'
 And HAI.Measure_ID Like '%SIR'





--Aggregate with a Join
Select
     Hospitals.Hospital_Name
    ,Hospitals.Provider_ID
    ,Hospitals.City
    ,Hospitals.State
    ,Hospitals.ZIP_Code
    ,Hospitals.Hospital_Ownership
    ,Hospitals.Hospital_Overall_Rating
    ,Hospitals.Mortality_national_comparison
    ,Hospitals.Safety_of_care_national_comparison
    ,Deaths.Measure_Name AS Deaths_Category
    ,Deaths.Score AS Deaths_Score
    ,Deaths.Compared_to_National As Deaths_Compare
    ,HCAHPS.HCAHPS_Question
    ,HCAHPS.Patient_Survey_Star_Rating
    ,Avg(HAI.Score) AS HAI_Score
From
     dbo.Hospitals Hospitals  --Select Top 100 * From dbo.Hospitals
     Left Join dbo.Deaths Deaths  --Select Top 100 * From dbo.Deaths
       On Deaths.Provider_ID = Hospitals.Provider_ID
     Left Join dbo.HCAHPS HCAHPS  --Select Top 100 * From dbo.HCAHPS
       On HCAHPS.Provider_ID = Hospitals.Provider_ID
     Left Join dbo.HAI HAI  --Select Top 100 * From dbo.HAI
       On HAI.Provider_ID = Hospitals.Provider_ID

Where
     Hospitals.Provider_ID In ('010033','010056','010139','010104')
 And Deaths.Measure_ID = 'PSI_15_ACC_LAC'
 And HCAHPS.HCAHPS_Measure_ID = 'H_HSP_RATING_STAR_RATING'
 And HAI.Measure_ID Like '%SIR'
Group By
     Hospitals.Hospital_Name
    ,Hospitals.Provider_ID
    ,Hospitals.City
    ,Hospitals.State
    ,Hospitals.ZIP_Code
    ,Hospitals.Hospital_Ownership
    ,Hospitals.Hospital_Overall_Rating
    ,Hospitals.Mortality_national_comparison
    ,Hospitals.Safety_of_care_national_comparison
    ,Deaths.Measure_Name
    ,Deaths.Score
    ,Deaths.Compared_to_National
    ,HCAHPS.HCAHPS_Question
    ,HCAHPS.Patient_Survey_Star_Rating





--PowerPoint for more Joins





--Inserts
Select
     Provider_ID
	,Hospital_Name
Into ##LocalHospitals
From
     dbo.Hospitals  --Select Top 100 * From dbo.Hospitals
Where Left(Zip_Code,3) = '352'


Select * From ##LocalHospitals


Insert Into ##LocalHospitals
Select
     Provider_ID
    ,Hospital_Name
From
     dbo.Hospitals
Where
     County_Name = 'MONTGOMERY'


Select * From ##LocalHospitals


Drop Table ##LocalHospitals

Select * From ##LocalHospitals