if status has same value else..

Question

Hi Expert,

there is condition when value is same for the status then it should take min date and if the value is change for the status then max date

create table test
(newdate datetime,status varchar(10), value varchar(10))

insert into test

values

('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmentone'),
('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')

expected output

('2022-01-04 18:00:57','Newone','segmentone')
('2022-04-04 18:00:57','Newtwo','segmenthee')

Answer 1

Hi，@Shambhu Rai

If your code is fixed, I think this will work for you, my idea is to find the column based on the sorted result, but this is all work beforehand for you, if your code changes a lot, I think you requirements are more complex.

create table #test  
(newdate datetime,status varchar(10), value varchar(10))  
  
insert into #test values  
('2022-01-04 18:00:57','Newone','segmentone')  
,('2022-01-04 19:00:57','Newone','segmentone'),  
('2022-04-04 18:00:57','Newtwo','segmenthee')  
,('2022-04-04 19:00:57','Newtwo','segmenfour'),  
('2022-03-28 15:58:40','Newtwo', '6-1'),  
('2022-03-28 12:52:49','Newtwo', '4-1'),  
('2022-03-31 12:52:49','Newtwo', '6-1'),  
('2022-04-01 12:52:49','Newtwo', '6-1'),  
('2022-04-28 15:58:40','Newtwo', '6-1'),  
('2022-04-29 12:52:49','Newtwo', '6-1'),  
('2022-04-30 12:52:49','Newtwo', '6-1'),  
('2022-05-01 12:52:49','Newtwo', '6-1'),  
('2022-05-29 12:52:49','Newtwo', '6-1'),  
('2022-05-31 12:52:49','Newtwo', '6-1'),  
('2022-06-01 12:52:49','Newtwo', '6-1'),  
('2022-06-02 12:52:49','Newtwo', '6-1')  
  
;with cte as  
(  
	select newdate,status,value,row_number() Over(Partition by status Order by newdate) rnAsc  
          ,row_number() Over(Partition by value Order by newdate DESC) rnDesc   
		  from #test  
),cte1 as  
(  
select  min(newdate)as newdate,status,value, row_number() Over(Partition by status Order by value DESC) rnDesc   
from cte  
group by status,value  
)  
select newdate,status,value from cte1 where rnDesc=2 or rnDesc=3  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.