Delete Rows with Conditions


 

  

create table #trp (SQID varchar(10), SPID varchar(10), Group1 varchar(10), startdate datetime, enddate datetime, cost integer)
insert into #trp values ('2310','A1','120A','2017-01-01','2017-10-01',10)
insert into #trp values ('2310','B1','120A','2016-01-01',NULL,10)
insert into #trp values ('2310','D1','120A','2017-08-01',NULL,30)  /*This should not delete*/ 
insert into #trp values ('2310','A1','121A','2017-08-01',NULL,30)
insert into #trp values ('2310','B1','121A','2017-08-01',NULL,20)
insert into #trp values ('2310','D1','121A','2017-08-01',NULL,40);

 

 ;with mycte as ( 
   SELECT *, ROW_NUMBER() OVER ( partition by SQID,group1 ORDER BY startdate) rn
        FROM    #trp
		) 
,mycte2 as ( 
select  SQID, SPID, Group1,startdate,enddate,cost
 , max(enddate) Over(PARTITION BY SQID,Group1) maxEnddate 
 , SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY SQID) cntDistinct 
 FROM  mycte)
 
 delete from mycte2
 --select * from mycte2
 where startdate > getdate() and  maxEnddate is null and cntDistinct>1

 

 select * from #trp

 

drop table #trp
 

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49432f77-79f6-438b-8636-c1795fece82c/need-help-to-delete-the-records?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s