clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921149 fiddles created (11939 in the last week).

create table bmh_plm_ptacct_v ( med_rec_no varchar(10) , ptno_num varchar(10) , adm_date date , dsch_date date , hosp_svc varchar(5) )
 hidden batch(es)


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')
9 rows affected
 hidden batch(es)


create table vReadmits ( [index] varchar(10) , interim int , [readmit] varchar(10) )
 hidden batch(es)


insert into vReadmits values('123458990','25','123459999')
1 rows affected
 hidden batch(es)


create table hosp_svc_dim_v ( hosp_svc varchar(50) , hosp_svc_name varchar(100) , orgz_cd varchar(10) )
 hidden batch(es)


insert into hosp_svc_dim_v values('PSY','Pyschiatry','s0x0'), ('EME','Emergency Department','s0x0')
2 rows affected
 hidden batch(es)


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 bmh_plm_ptacct_v AS A LEFT OUTER JOIN vReadmits AS B ON A.PtNo_Num = b.[INDEX] AND B.INTERIM < 31 WHERE Dsch_Date >= '01-01-2018' AND dsch_date < '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 > 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 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 > 1 ) DROP TABLE #TEMPA; DROP TABLE #TEMPB; DROP TABLE #TEMPC; DROP TABLE #TEMPD;
Med_Rec_No PtNo_Num Adm_Date Dsch_Date hosp_svc hosp_svc_name Readmit Status Event_Num Keep_Flag
123456 123456748 18/12/2017 00:00:00 12/01/2018 00:00:00 PSY Pyschiatry No 1 1
123456 123456789 17/01/2018 00:00:00 18/01/2018 00:00:00 EME Emergency Department No 2 2
123456 123456889 19/01/2018 00:00:00 21/01/2018 00:00:00 EME Emergency Department No 3 3
123456 123478978 25/01/2018 00:00:00 25/01/2018 00:00:00 EME Emergency Department No 4 4
123456 123457979 21/05/2018 00:00:00 21/05/2018 00:00:00 EME Emergency Department No 5 5
123456 123458988 03/06/2018 00:00:00 04/06/2018 00:00:00 EME Emergency Department No 6 6
123456 123458989 27/07/2018 00:00:00 14/08/2018 00:00:00 PSY Pyschiatry No 7 7
123456 123458990 23/09/2018 00:00:00 24/09/2018 00:00:00 EME Emergency Department Yes 8 8
123456 123459999 25/09/2018 00:00:00 30/09/2018 00:00:00 PSY Pyschiatry No 9 9
 hidden batch(es)


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 < 31 WHERE Dsch_Date >= '01-01-2018' AND dsch_date < '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 > 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 > 1 ) ORDER BY Med_Rec_No, Adm_Date ;
Med_Rec_No PtNo_Num Adm_Date Dsch_Date hosp_svc hosp_svc_name Readmit Status Event_Num Keep_Flag
123456 123456748 18/12/2017 00:00:00 12/01/2018 00:00:00 PSY Pyschiatry No 1 1
123456 123456789 17/01/2018 00:00:00 18/01/2018 00:00:00 EME Emergency Department No 2 2
123456 123456889 19/01/2018 00:00:00 21/01/2018 00:00:00 EME Emergency Department No 3 3
123456 123478978 25/01/2018 00:00:00 25/01/2018 00:00:00 EME Emergency Department No 4 4
123456 123457979 21/05/2018 00:00:00 21/05/2018 00:00:00 EME Emergency Department No 5 5
123456 123458988 03/06/2018 00:00:00 04/06/2018 00:00:00 EME Emergency Department No 6 6
123456 123458989 27/07/2018 00:00:00 14/08/2018 00:00:00 PSY Pyschiatry No 7 7
123456 123458990 23/09/2018 00:00:00 24/09/2018 00:00:00 EME Emergency Department Yes 8 8
123456 123459999 25/09/2018 00:00:00 30/09/2018 00:00:00 PSY Pyschiatry No 9 9
 hidden batch(es)