T-SQL Getting Sequential events with first even criteria
Review

Your temporary table names don't say much about what they present. This makes it hard to figure out what they mean. Consider using better, more meaningful names.


DROP TABLE #TEMPA;
DROP TABLE #TEMPB;
DROP TABLE #TEMPC;
DROP TABLE #TEMPD;



It gets worse by aliasing these temporary tables with different letters:


FROM #TEMPA AS A  -- Fair enough

FROM #TEMPA AS B  -- Mamma mia!



There is only a need for an order by in the resulting query and the analytical functions (row over), not in the temporary tables.

There is no need for the temporary tables, you could use CTE's instead.



Refactored Query

Fiddle containing OP + Refactored Query

This only refactors the query for readability. I am sure a more compact and optimized query could be found.

with ACC as (
  SELECT Med_Rec_No
       , PtNo_Num
       , Adm_Date
       , Dsch_Date
       , hosp_svc
       , CASE WHEN B.READMIT IS NULL THEN 'No' ELSE 'Yes' END AS [Readmit Status]
       , [Event_Num] = ROW_NUMBER() over(partition by med_rec_no order by ADM_date)
       , [PSY_Flag] = CASE WHEN hosp_svc = 'PSY' THEN '1' ELSE '0' END
  FROM bmh_plm_ptacct_v AS A
  LEFT OUTER JOIN vReadmits AS B
  ON A.PtNo_Num = b.[INDEX] AND B.INTERIM &lt; 31
  WHERE Dsch_Date &gt;= '01-01-2018'
  AND dsch_date &lt; '12-31-2018'
)
, EMERG as (
  SELECT ACC.* FROM ACC WHERE hosp_svc = 'PSY'
)
, PSY as (
  SELECT ACC.*
  FROM ACC
  WHERE hosp_svc != 'PSY'
  AND Med_Rec_No IN (SELECT DISTINCT Med_Rec_No FROM EMERG)
)
, ACC_REL as (
  SELECT Med_Rec_No
       , PtNo_Num
       , Adm_Date
       , Dsch_Date
       , hosp_svc
       , [Readmit Status]
       , Event_Num
       , PSY_Flag
       , [Keep_Flag] = ROW_NUMBER() OVER(PARTITION BY MED_REC_NO ORDER BY ADM_DATE)
  FROM (
    SELECT * FROM EMERG
    UNION ALL
    SELECT * FROM PSY
    WHERE PSY.Med_Rec_No IN (
        SELECT e.Med_Rec_No
        FROM EMERG AS e
        WHERE e.Med_Rec_No = PSY.Med_Rec_No
        AND PSY.Event_Num &gt; e.Event_Num
    )
  ) AS A
)
SELECT A.Med_Rec_No
     , A.PtNo_Num
     , CAST(A.ADM_DATE AS DATE) AS [Adm_Date]
     , CAST(A.Dsch_Date AS DATE) AS [Dsch_Date]
     , A.hosp_svc
     , HS.hosp_svc_name
     , A.[Readmit Status]
     , A.Event_Num
     , A.Keep_Flag
FROM ACC_REL AS A
LEFT OUTER JOIN hosp_svc_dim_v AS HS
ON A.hosp_svc = HS.hosp_svc AND HS.orgz_cd = 'S0X0'
WHERE A.Med_Rec_No IN (
    SELECT DISTINCT rel.MED_REC_NO
    FROM ACC_REL AS rel
    WHERE Keep_Flag &gt; 1
)
ORDER BY Med_Rec_No, Adm_Date
;