if status has same value else..

Shambhu Rai 1,406 Reputation points
2022-04-19T14:21:31+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,823 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2022-04-19T15:56:08.087+00:00

    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

    1 person found this answer helpful.
    0 comments
  2. Shambhu Rai 1,406 Reputation points
    2022-04-19T16:41:29.19+00:00

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

    0 comments
  3. Shambhu Rai 1,406 Reputation points
    2022-04-19T18:32:03.47+00:00

    suggestion please

  4. Shambhu Rai 1,406 Reputation points
    2022-04-19T21:13:53.387+00:00

    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'),

    0 comments
  5. Jingyang Li 5,891 Reputation points
    2022-04-19T21:20:11.383+00:00

    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.