Query question - grouping data to get start, end times

1

Hey guys, I have an audit table that shows when a value was changed to a specific flag ('Y', 'N', 'A', 'B') and I want to organize the data so that for each ID it'll reflect when the start and end time was for that flag.

Sample data:
ID,Flag_Audit,updateddatetime,
1,Changed Flag From: N To: B,8/6/2021 2:58
1,Changed Flag From: B To: Y,8/6/2021 12:58
1,Changed Flag From: Y To: N,8/20/2021 10:58
2,Changed Flag From: Y To: N,8/1/2021 12:38
2,Changed Flag From: B To: Y,8/11/2021 10:58

Expected Output:
ID,Flag,From Date,To Date
1,B,8/6/2021 2:58,8/6/2021 12:58
1,Y,8/6/2021 12:58,8/20/2021 10:58
1,N,8/20/2021 10:58,NULL
2,N,8/1/2021 12:38,8/11/2021 10:58
2,Y,8/11/2021 10:58

Could you help me with this query? Thank you!

2

From a different forum but applicable to this one as well on how to post a good question:

3

hi hope this helps .. :+1: :+1:

create table Flag 
(
ID int ,Flag_Audit varchar(100),updateddatetime datetime 
)

insert into Flag select 1,'Changed Flag From: N To: B','8/6/2021 2:58'
insert into Flag select 1,'Changed Flag From: B To: Y','8/6/2021 12:58'
insert into Flag select 1,'Changed Flag From: Y To: N','8/20/2021 10:58'
insert into Flag select 2,'Changed Flag From: Y To: N','8/1/2021 12:38'
insert into Flag select 2,'Changed Flag From: B To: Y','8/11/2021 10:58'

select 'Data',* from Flag 

select 
      'SQL Output'
    , ID
	, Flag_Audit
	, updateddatetime
	, lead(updateddatetime) over(partition by id order by ID) 
from 
   Flag 

drop table Flag

image
image936×381 11.7 KB