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,676 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shambhu Rai 1,406 Reputation points
    2022-04-19T21:37:50.583+00:00

    Hi Expert,
    if there is no value change for status continuously and without any new value in between then

    expected output

    ('2022-03-31 12:52:49','Newtwo', '6-1')

    0 comments
  2. Shambhu Rai 1,406 Reputation points
    2022-04-20T01:48:30.33+00:00

    suggestion please

  3. Shambhu Rai 1,406 Reputation points
    2022-04-20T02:13:57.48+00:00

    here is overall requirement and expected ... first two conditions are working only last row result condition required
    ('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')

    Expected output
    ('2022-01-04 19:00:57','Newone','segmentone')
    ('2022-04-04 19:00:57','Newtwo','segmenfour')
    ('2022-03-28 15:58:40','Newtwo', '6-1')

  4. Shambhu Rai 1,406 Reputation points
    2022-04-20T03:02:49.733+00:00

    the condition for last pair is if value of same status is repeated on next day and so on then it should take minimum date or may be you send me the query for only this let me try from my end

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

  5. Shambhu Rai 1,406 Reputation points
    2022-04-20T03:40:24.83+00:00

    HI Expert,

    you can neglect the first condition,

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

    expected output
    Expected output
    ('2022-04-04 19:00:57','Newtwo','segmenfour')
    ('2022-03-28 15:58:40','Newtwo', '6-1')

    0 comments