SQL LOGIC by date


i have admit date and discharge dates as shown:

ID Admit Discharge
100 3/20/2019 3/21/2019
100 3/21/2019 3/23/2019
100 3/23/2019 3/25/2019

want to write a logic to delete the record(s) if the admit date of the subsequent is same as the discharge of the previous ID.


ID Admit Discharge
100 3/20/2019 3/21/2019

delete t1
from tbl t1
join tbl t2
on t1.ID = t2.ID
and t1.Discharge = t2.Admit

But are you sure that is what you want?
Not 100, 3/20/2019, 3/25/2019
Combining the rows.
Also - deleting rows is dangerous - I would flag them.

Thanks Nigel for the reply and if i understand correctly , we need to perform a self join correct?
yes , i agree flagging is better than deleting!
Does the below logic look correct ??

Thanks a lot

select *,
case when t1.ID = t2.ID and t1.Discharge = t2.Admit then flag=1 else flag=0 end as discharge_flag
from tbl t1
join tbl t2
on t1.ID = t2.ID

You don't need to check the ID in the case statement as that is guaranteed by the join but it doesn't hurt.
If you want to do an update from this then it is better to put the check in the join rather than the case statement otherwise you will be updating a lot of rows unnecessarily.

update t1
set flag = 1
from tbl t1
join tbl t2
on t1.ID = t2.ID
and t1.Discharge = t2.Admit
and t1.flag = 0

It depends a bit on how you want to use this and your distribution of data..
It could be better to maintain a separate table with the removed rows or maybe an indexed view to materialise it.
If you are holding the changed data you only need to run the update on entries that have been changed - maybe have a filter based on the last time the update was run and the rows that have been added since then.


i tried this

hope it helps


drop create data ..
drop table #abc 

create table #abc 
ID int ,
Admit  date,
Discharge date

insert into #abc select 100,'3/20/2019','3/21/2019'
insert into #abc select 100,'3/21/2019','3/23/2019'
insert into #abc select 100,'3/23/2019','3/25/2019'

select * from #abc 
SQL ..
;WITH cte 
     AS (SELECT Lag(discharge) 
                    ORDER BY admit) AS lagdischarge, 
         FROM   #abc) 
WHERE  lagdischarge = admit