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

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

insert into test (newdate,status,value) 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')

;with mycte as (
select newdate,status,value
,count(*) over(Partition by status,value) cnt
,row_number() Over(Partition by status Order by newdate) rnAsc
,row_number() Over(Partition by status Order by newdate DESC) rnDesc
from test
)

select newdate,status,value
from mycte
WHERE (cnt>1 and rnAsc=1) or (cnt=1 and rnDesc=1)

drop table test

Answer 2

Hi Expert,

it is showing wrong output

INSERT INTO TEST
VALUES

('2022-03-28 15:58:40','Newtwo', '6-1'),
('2022-03-28 12:52:49','Newtwo', '4-1')

it is not reflecting above records

expected output

('2022-03-28 15:58:40','Newtwo', '6-1')

Answer 3

suggestion please

Answer 4

Hi Expert,
there is small change in condition when same value comes for the status on next day or same month then out put should be min date
INSERT INTO TEST
VALUES

('2022-03-28 15:58:40','Newtwo', '6-1'),
('2022-03-29 12:52:49','Newtwo', '6-1')
('2022-03-31 12:52:49','Newtwo', '6-1')
('2022-04-01 12:52:49','Newtwo', '6-1')

expected output
('2022-03-28 15:58:40','Newtwo', '6-1'),

Answer 5

I don't think this is the end of your requirements.
What about the following if do have:

('2022-03-01 15:58:40','Newtwo', '6-1')

Think about your question and post them once. If your changes break solution, the change is not "small".
Hope you can make a whole case as one question and get some replies.