/****** Analysis of Remissions  ******/
DROP TABLE #DATA
SELECT Str([src_subject_id], 7)+Concat_Levels AS ID, [src_subject_id],Concat_Levels As Trial
, [Concat]
, Max(CAST([Treatment_plan_equal_3] AS FLOAT)) as Remission
, Max(Bupropion) AS Bupropion 
, Max(CIT) AS Citalopram
, MAX(Mirzapine) AS Mirzapine
, MAX(Buspirone) AS Buspirone
, MAX(Lithium) AS Lithium
, MAX(Nortriptyline) AS Nortriptyline
, MAX(Sertraline) AS Sertraline
, MAX(Thyroid) AS Thyroid
, MAX(Tranylclypromine) AS Tranylclypromine
, Max(Venlafaxine) AS Venlafaxine
, CASE WHEN Sum([Neurological])=0 THEN 0 ELSE 1 END AS [Neurological]
, CASE WHEN Sum([PTSD])=0 THEN 0 ELSE 1 END AS [PTSD]
, CASE WHEN Sum([Heart])=0 THEN 0 ELSE 1 END AS [Heart]
, CASE WHEN Sum([Vascular])=0 THEN 0 ELSE 1 END AS [Vascular]
, CASE WHEN Sum([Haematopoietic])=0 THEN 0 ELSE 1 END AS [Haematopoietic]
, CASE WHEN Sum([Eyes_Ears_Nose_Throat_Larynx])=0 THEN 0 ELSE 1 END AS [Eyes_Ears_Nose_Throat_Larynx]
, CASE WHEN Sum([Gastrointestinal])=0 THEN 0 ELSE 1 END AS [Gastrointestinal]
, CASE WHEN Sum([Renal])=0 THEN 0 ELSE 1 END AS [Renal]
, CASE WHEN Sum([Genitourinary])=0 THEN 0 ELSE 1 END AS [Genitourinary]
, CASE WHEN Sum([Musculoskeletal_Integument])=0 THEN 0 ELSE 1 END AS [Musculoskeletal_Integument]
, CASE WHEN Sum([Psychiatric_Illness])=0 THEN 0 ELSE 1 END AS [Psychiatric_Illness]
, CASE WHEN Sum([Respiratory])=0 THEN 0 ELSE 1 END AS [Respiratory]
, CASE WHEN Sum([Liver])=0 THEN 0 ELSE 1 END AS [Liver]
, CASE WHEN Sum([Endocrine])=0 THEN 0 ELSE 1 END AS [Endocrine]
, CASE WHEN Sum([Alcohol])=0 THEN 0 ELSE 1 END AS [Alcohol]
, CASE WHEN Sum([Panic])=0 THEN 0 ELSE 1 END AS [Panic]
, CASE WHEN Sum([OCD])=0 THEN 0 ELSE 1 END AS [OCD]
, CASE WHEN Sum([Anxiety])=0 THEN 0 ELSE 1 END AS [Anxiety]
, CASE 
	WHEN Sum([Amphetamine])>0 THEN 1 
	WHEN Sum([Opioid])>0 THEN 1
	WHEN Sum([Cocaine])>0 THEN 1
		ELSE 0 END AS [AmphetamineOpioidCocaineAddiction]
, CASE 
	WHEN Sum([Specific_Phobia])>0 THEN 1 
	WHEN Sum([Social_Phobia])>0 THEN 1
		ELSE 0 END AS [Phobia]
, CASE 
	WHEN Sum([Paranoid_Personality])>0 THEN 1 
	WHEN Sum([Borderline_Personality])>0 THEN 1
	WHEN Sum([Dependent_Personality])>0 THEN 1
	WHEN Sum([Antisocial_Personality])>0 THEN 1 
	WHEN SUM([Personality_Disorder])>0 THEN 1 
		ELSE 0 END AS [Personality]
INTO #Data 
FROM [STARD].[dbo].[Data]
GROUP BY src_subject_id, Concat_Levels, [Concat]
Order by src_subject_id, Concat_Levels desc 
Go
-- 5624 trials of antidepressants
--(7374 row(s) affected)
--Select distinct [concat] from #data order by [concat] desc

-- Create a table of distinct medications
DROP TABLE #Meds 
SELECT [Concat] AS Medication, ROW_NUMBER() OVER(Order by [Concat] desc) as MedId, COUNT(distinct [src_subject_id]) As nTrials
INTO #Meds FROM #Data
GROUP BY [Concat]
HAVING COUNT(distinct [src_subject_id])>29
-- Select * from #meds
/*
Medication	MedId	nTrials
MED1100000000	1	266
MED1001000000	2	253
MED1000000100	3	33
MED1000000000	4	3670
MED0100000000	5	235
MED0010000001	6	50
MED0010000000	7	102
MED0000010000	8	107
MED0000001000	9	218
MED0000000010	10	42
MED0000000001	11	241
MED0000000000	12	91
*/

DECLARE @Index INT
SET @index = 1  
WHILE (@Index <13)
BEGIN
    DROP TABLE #Cases, #Med
    SELECT Medication INTO #Med FROM #Meds Where MedID=@index
	SELECT CASE 
		WHEN SUM(Remission)=0 THEN 1./(SUM(Remission)+SUM(1-Remission)+1) 
		WHEN SUM(1-Remission) =0 THEN SUM(Remission)+SUM(1-Remission)+1 
		ELSE SUM(Remission)/SUM(1-Remission) END as pCases
	  , SUM(Remission)+SUM(1-Remission) as nCases
	  , STR([Heart],1)+STR([Vascular],1)+STR([Haematopoietic],1)+STR([Eyes_Ears_Nose_Throat_Larynx],1)+STR([Gastrointestinal],1)+STR([Renal],1)
	  +STR([Genitourinary],1)+STR([Musculoskeletal_Integument],1)+STR([Neurological],1)+STR([Psychiatric_Illness],1)+STR([Respiratory],1)+STR([Liver],1)
	  +STR([Endocrine],1)+STR([Alcohol],1)+STR([AmphetamineOpioidCocaineAddiction],1)+STR([Panic],1)+STR([Phobia],1)
	  +STR([OCD],1)+STR([PTSD],1)+STR([Anxiety],1)+STR([Personality],1)AS CaseStrata
	  , [Concat]
	INTO #Cases
	FROM #DATA INNER JOIN #Med On #DATA.[Concat]=#Med.Medication
	WHERE [Neurological]=1 and PTSD=1
	GROUP BY   
		   [Heart],[Vascular],[Haematopoietic],[Eyes_Ears_Nose_Throat_Larynx],[Gastrointestinal],[Renal],[Genitourinary],[Musculoskeletal_Integument]
		  ,[Neurological],[Psychiatric_Illness],[Respiratory],[Liver],[Endocrine],[Alcohol],[AmphetamineOpioidCocaineAddiction],[Panic],[Phobia],[OCD]
		  ,[PTSD],[Anxiety],[Personality],[Concat]
	-- (103 row(s) affected)

	DROP TABLE #Controls
	SELECT CASE 
		WHEN SUM(Remission)=0 THEN 1./(SUM(Remission)+SUM(1-Remission)+1) 
		WHEN SUM(1-Remission) =0 THEN SUM(Remission)+SUM(1-Remission)+1 
		ELSE SUM(Remission)/SUM(1-Remission) END as pControls
	  , SUM(Remission)+SUM(1-Remission) as nCases
	  , STR([Heart],1)+STR([Vascular],1)+STR([Haematopoietic],1)+STR([Eyes_Ears_Nose_Throat_Larynx],1)+STR([Gastrointestinal],1)+STR([Renal],1)
	  +STR([Genitourinary],1)+STR([Musculoskeletal_Integument],1)+STR([Neurological],1)+STR([Psychiatric_Illness],1)+STR([Respiratory],1)+STR([Liver],1)
	  +STR([Endocrine],1)+STR([Alcohol],1)+STR([AmphetamineOpioidCocaineAddiction],1)+STR([Panic],1)+STR([Phobia],1)
	  +STR([OCD],1)+STR([PTSD],1)+STR([Anxiety],1)+STR([Personality],1)AS ControlStrata
	  , [Concat]as Medication
	INTO #Controls
	FROM #DATA INNER JOIN #Med On #DATA.[Concat]!=#Med.Medication
	WHERE [Neurological]=1 and PTSD=1 
	GROUP BY   [Heart],[Vascular],[Haematopoietic],[Eyes_Ears_Nose_Throat_Larynx],[Gastrointestinal],[Renal],[Genitourinary],[Musculoskeletal_Integument]
		  ,[Neurological],[Psychiatric_Illness],[Respiratory],[Liver],[Endocrine],[Alcohol],[AmphetamineOpioidCocaineAddiction],[Panic],[Phobia],[OCD],[PTSD]
		  ,[Anxiety],[Personality], [Concat]
	--(103 row(s) affected)

	Select #Cases.*, #Controls.* 
	FROM #Cases inner join #Controls on #Cases.CaseStrata=#Controls.ControlStrata 
	ORDER BY Medication desc
	
SET @Index = @Index + 1
END
GO