DTI_SCANS_YEAR4.sql

WITH A AS ( /* Calculated Part III UPDRS Score */
  SELECT PATNO, EVENT_ID , 
    NP3SPCH +  NP3FACXP +  NP3RIGN + NP3RIGRU + NP3RIGLU + PN3RIGRL + 
    NP3RIGLL + NP3FTAPR + NP3FTAPL + NP3HMOVR + NP3HMOVL + NP3PRSPR + 
    NP3PRSPL + NP3TTAPR + NP3TTAPL + NP3LGAGR + NP3LGAGL + NP3RISNG + 
    NP3GAIT + NP3FRZGT + NP3PSTBL + NP3POSTR + NP3BRADY + NP3PTRMR +
    NP3PTRML + NP3KTRMR + NP3KTRML + NP3RTARU + NP3RTALU + NP3RTARL + 
    NP3RTALL + NP3RTALJ + NP3RTCON  as UPDRS_SCORE
  FROM NUPDRS3 WHERE 
    EVENT_ID IN ("V10", "BL")  AND /* V10 is year 4 */
    PAG_NAME = "NUPDRS3"
  ORDER BY PATNO),
A_BL AS ( /* SELECT the Baseline events from UPDRS scores */
   SELECT * FROM A WHERE EVENT_ID = "BL"
),
A_V10 AS ( /* SELECT the year 4 events from UPDRS scores */
   SELECT * FROM A WHERE EVENT_ID = "V10"
),
RANDOM_SCREEN AS ( /* Join RANDOM and SCREEN to see who is which group */
  SELECT * FROM SCREEN INNER JOIN RANDOM ON SCREEN.PATNO = RANDOM.PATNO 
  WHERE ENROLLDT IS NOT NULL
),
MRI_DTI AS ( /* Select Patients with DTI Scans in year 4 */
  SELECT * FROM MRI WHERE 
  EVENT_ID = "V10" AND 
  MRIWDTI = 1
)
SELECT RANDOM_SCREEN.PATNO AS PATNO, 
	RANDOM_SCREEN.APPRDX AS APPRDX,
	RANDOM_SCREEN.GENDER AS GENDER, 
	A_BL.UPDRS_SCORE AS BL, 
	A_V10.UPDRS_SCORE AS V10, 
	A_V10.UPDRS_SCORE - A_BL.UPDRS_SCORE AS UPDRS3_CHANGE 
FROM A_BL INNER JOIN A_V10 ON (A_BL.PATNO = A_V10.PATNO)
INNER JOIN RANDOM_SCREEN ON A_BL.PATNO = RANDOM_SCREEN.PATNO 
INNER JOIN MRI_DTI ON A_BL.PATNO = MRI_DTI.PATNO
/* ORDER BY PATNO  */
ORDER BY UPDRS3_CHANGE DESC