/********************
Slide #3
Adding Columns Names
*********************/

SELECT [Column 0] as ID
      ,[Column 1] as Age
      ,[Column 2] as Gender
      ,[Column 3] as Num_Assessment
      ,[Column 4] as Days_Followed
      ,[Column 5] as First_Assess
      ,[Column 6] as Last_Assess
      ,[Column 7] as Unable_Eat
      ,[Column 8] as Unable_Transfer
      ,[Column 9] as Unable_Groom
      ,[Column 10] as Unable_Toilet
      ,[Column 11] as Unable_Bathe
      ,[Column 12] as Unable_Walk
      ,[Column 13] as Unable_Dress
      ,[Column 14] as Unable_Bowel
      ,[Column 15] as Unable_Urine
      ,[Column 16] as Survival
      ,[Column 17] as Assessment_Num
INTO Assessment_Clean
FROM [HAP823].[dbo].[Assessments]


/**************************************************************
Slide #4: 
1. Read the data, making sure all entries are numbers.  
Calculate age at each assessment not just at first assessment. 

2. Clean the data, removing impossible situations 
(remove cases with date of assessment after death).   

3. Calculating actual age at assessment
***************************************************************/
Drop Table #Assessment

SELECT Distinct [ID]
      ,CAST([Age] as float) + CAST([First_Assess] as float)/365 as Age_At_Assessment
      ,IIF(gender = 'M',1,0) Male
      ,CAST([Num_Assessment] as float) Num_Assessment
      ,CAST([Days_Followed] as float) Days_Followed
      ,CAST([First_Assess] as float) First_Assess
      ,CAST([Last_Assess] as float) Last_Assess
      ,[Unable_Eat]
      ,[Unable_Transfer]
      ,[Unable_Groom]
      ,[Unable_Toilet]
      ,[Unable_Bathe]
      ,[Unable_Walk]
      ,[Unable_Dress]
      ,[Unable_Bowel]
      ,[Unable_Urine]
      ,[Survival]
      ,cast([Assessment_Num] as float) Assessment_Num
into #Assessment
FROM [HAP823].[dbo].[Assessment_Clean]
--(1306456 rows affected)

/*********************************************************************
Slide #5:
3. Remove irrelevant cases (all cases that have only one assessment) 
**********************************************************************/
Drop table #Assessment_Final

select *
into #Assessment_Final
from #Assessment
where Num_Assessment > 1
-- (1306456 rows affected)

/******************************************************************************************************** 
Slide #6:
Assess the number of days from first assessment for the death. 
Then examine if the assessment time is within 180 days of day of death. 
*********************************************************************************************************/
Drop Table #daysdead

SELECT ID
	  ,MAX(first_assess) as daydead
INTO #daysdead
FROM #Assessment_Final
Where Survival = 1
Group by id
-- (196533 rows affected)

Drop Table #Assessment_Data

Select Cast(IIF(Assessment.First_Assess > #daysdead.daydead-180,1,0) as float) as Dead
	  ,Assessment.*
into #Assessment_Data
From #Assessment_Final as Assessment
	 join #daysdead on Assessment.id = #daysdead.id 
-- (994019 rows affected)


/****************************************************************************************************************
Slide : 7
*****************************************************************************************************************/
Drop Table #Final_Assessment_Data

Select Top 100 * from #Assessment_Data

SELECT DISTINCT ID
	  ,FLOOR(Age_At_Assessment/10)*10 as Decade
	  ,Male
	  ,Unable_Eat
	  ,Unable_Transfer
	  ,Unable_Groom
	  ,Unable_Toilet
	  ,Unable_Bathe
	  ,Unable_Walk
	  ,Unable_Dress
	  ,Unable_Bowel
	  ,Unable_Urine
	  ,Dead
into #Final_Assessment_Data
From #Assessment_Data
--(605133 rows affected)


/****************************************************************************************************************
Slide : 8
Group the data based on current disabilities, gender, and age.  
Count the number of residents who died within 6 months of assessment for combination of disabilities, gender and age. 
To do this, first assess the number of days from first assessment for the death. 
Then examine if the assessment time is within 180 days of day of death. (Did this is earlier step Column: Dead)
*****************************************************************************************************************/
DROP TABLE #Prob

SELECT SUM(Dead)/Cast(Count(Distinct ID) as float) AS Prob
	  ,Count(Distinct ID) as n
	  ,Decade,Male,Unable_Eat,Unable_Transfer, Unable_Groom, Unable_Toilet
	  ,Unable_Bathe, Unable_Walk, Unable_Dress, Unable_Bowel, Unable_Urine
INTO #Prob
FROM #Final_Assessment_Data
GROUP BY Decade, Male, Unable_Eat, Unable_Transfer, Unable_Groom
	  ,Unable_Toilet, Unable_Bathe, Unable_Walk, Unable_Dress, Unable_Bowel, Unable_Urine
Having Count(distinct ID)>9
order by Prob
--(958 rows affected)

/****************************************************************************************************************
Slide : 9
Use ordinary regression to regress the logit of odds of mortality in 6 months on 
various current disabilities, age, gender, and pair wise interactions of these variables.  
*****************************************************************************************************************/

SELECT CASE WHEN Prob=0 THEN log(1/cast(n as FLOAT) )
	   WHEN Prob=1 Then Log(Cast(n as FLOAT)/(Cast(n as FLOAT)+1.))
	   ELSE log(Prob/(1-prob)) END AS [Logit] 
	  ,*
FROM #Prob