T-SQL Getting Sequential events with first even criteria I have a query I am writing where I want output if a person has some service provided, then I want all the services they had provided after that and I don't want the individual returned if that is the only service they had (the criteria service)

I have some example data:


In this instance above I would not want the last row.

The code I have, I feel is verbose at best. I am confident it works but I think it could be written better.

This will get some sample data

declare @bmh_plm_ptacct_v as table (
    med_rec_no varchar(10)
    , ptno_num varchar(10)
    , adm_date date
    , dsch_date date
    , hosp_svc varchar(5)
)

insert into @bmh_plm_ptacct_v
values('123456','123456748','2017-12-18','2018-01-12','PSY'),
('123456','123456789','2018-01-17','2018-01-18','EME'),
('123456','123456889','2018-01-19','2018-01-21','EME'),
('123456','123478978','2018-01-25','2018-01-25','EME'),
('123456','123457979','2018-05-21','2018-05-21','EME'),
('123456','123458988','2018-06-03','2018-06-04','EME'),
('123456','123458989','2018-07-27','2018-08-14','PSY'),
('123456','123458990','2018-09-23','2018-09-24','EME'),
('123456','123459999','2018-09-25','2018-09-30','PSY')

declare @vReadmits as table (
    [index] varchar(10)
    , interim int
    , [readmit] varchar(10)
)

insert into @vReadmits
values('123458990','25','123459999')

declare @hosp_svc_dim_v as table (
    hosp_svc varchar(50)
    , hosp_svc_name varchar(100)
        , orgz_cd varchar(10)
)

insert into @hosp_svc_dim_v
values('PSY','Pyschiatry','s0x0'),
('EME','Emergency Department','s0x0')


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

INTO #TEMPA

FROM smsdss.bmh_plm_ptacct_v AS A
LEFT OUTER JOIN smsdss.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'

ORDER BY Med_Rec_No, A.Adm_Date
;

SELECT A.*
INTO #TEMPB
FROM #TEMPA AS A
WHERE A.hosp_svc = 'PSY'
;

SELECT B.*
INTO #TEMPC
FROM #TEMPA AS B
WHERE B.hosp_svc != 'PSY'
AND B.Med_Rec_No IN (
    SELECT DISTINCT Med_Rec_No
    FROM #TEMPB
)
;

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)

INTO #TEMPD

FROM (
    SELECT B.*
    FROM #TEMPB AS B

    UNION ALL

    SELECT C.*
    FROM #TEMPC AS C
    WHERE C.Med_Rec_No IN (
        SELECT ZZZ.Med_Rec_No
        FROM #TEMPB AS ZZZ
        WHERE ZZZ.Med_Rec_No = C.Med_Rec_No
        AND C.Event_Num &gt; ZZZ.Event_Num
    )
) AS A

ORDER BY MED_REC_NO, Event_Num
;

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 #TEMPD AS A
LEFT OUTER JOIN SMSDSS.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 ZZZ.MED_REC_NO
    FROM #TEMPD AS ZZZ
    WHERE Keep_Flag &gt; 1
)

DROP TABLE #TEMPA;
DROP TABLE #TEMPB;
DROP TABLE #TEMPC;
DROP TABLE #TEMPD;
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
;