SQL LOGIC by date

Transact-SQL
1

Hello,

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.

OUTPUT:

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

2

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.

3

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

4

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.

5

Hi

i tried this

hope it helps

:slight_smile:
:slight_smile:

drop create data .. 
drop table #abc 

create table #abc 
(
ID int ,
Admit  date,
Discharge date
)
go 

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'
go 

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

image