I have the below SQL query and I have discovered that there are multiple Consult, SIM, PLAN, TreatmentStart and TreatmentFinish results.

Using `MAX` only display the latest result, however I need say ,the Planning to display the closest most recent date previous to TreatmentStart, and SIM to display the closest most recent date previous to the Plan date. 

With the start date being based on the most recent TreatmentStart date. 

I am just wondering if anyone can point me in the right direction because I wrote long winded sql queries that take more then 2 minutes to complete...

Here are the queries:

```
SELECT  s.Pat_Name, s.IDA, s.Pat_ID1, 
ShortDesc = MAX(CASE WHEN s.SysDefStatus = 'SC' THEN s.Short_Desc END),
Consult = MAX(CASE WHEN s.Activity = '123'
                    AND s.SysDefStatus = 'C' THEN s.App_DtTm END),
SIM = MAX(CASE WHEN s.Activity = '456'
                      THEN s.App_DtTm END),
PLANNING = MAX(CASE WHEN s.Activity = '789'
                      THEN s.App_DtTm END),
TreatmentStart = MAX(CASE WHEN s.SysDefStatus = 'SC' THEN s.App_DtTm END),
TreatmentFinished = MAX(CASE WHEN s.SysDefStatus = 'FC' THEN s.App_DtTm END)
FROM    vw_Schedule s 
WHERE   s.Activity IN ('123', '456', '789')
OR      s.SysDefStatus IN ('SC', 'FC', 'C')
GROUP BY s.Pat_Name, s.IDA, s.Pat_ID1

)

SELECT  Pat_Name,
IDA,
Tx_Intent,
ShortDesc,
Consult,
Days = (DATEDIFF(dd, Consult, SIM)+1)-(DATEDIFF(wk, Consult, SIM)*2),
SIM,
Days = (DATEDIFF(dd, SIM, PLANNING)+1)-(DATEDIFF(wk, SIM, PLANNING)*2),
PLANNING,
Days = (DATEDIFF(dd, PLANNING, TreatmentStart)+1)-(DATEDIFF(wk, PLANNING, TreatmentStart)*2),
TreatmentStart,
Days = (DATEDIFF(dd, TreatmentStart, TreatmentFinished)+1)-(DATEDIFF(wk, TreatmentStart, TreatmentFinished)*2),
TreatmentFinished
FROM T;

```