hi
i got it till here !! but the null part has to be fixed with 23:59:59
please click arrow to the left for Create Data Script
----------------------
-- create table
create table data
(
date1 date , action varchar(100) , alm_id int , occur_time datetime
)
go
---------------------------
-- insert into data select
insert into data select '01-06-2020','Insert ',123457,'6/1/2020 11:30:45'
insert into data select '01-06-2020','Altered',123457,'6/1/2020 11:35:45'
insert into data select '01-06-2020','Altered',123457,'6/1/2020 11:36:40'
insert into data select '01-06-2020','Altered',123457,'6/1/2020 11:35:40'
insert into data select '01-06-2020','Altered',123457,'6/1/2020 11:37:45'
insert into data select '01-06-2020','Cleared',123457,'6/1/2020 11:45:45'
insert into data select '02-06-2020','Insert ',123455,'6/2/2020 00:15:25'
insert into data select '02-06-2020','Altered',123455,'6/2/2020 00:15:25'
insert into data select '02-06-2020','Altered',123455,'6/2/2020 00:25:15'
insert into data select '02-06-2020','Altered',123455,'6/2/2020 00:26:05'
insert into data select '03-06-2020','Cleared',123455,'6/3/2020 00:15:25'
insert into data select '03-06-2020','Insert ',123425,'6/3/2020 14:15:25'
insert into data select '03-06-2020','Cleared',123425,'6/3/2020 16:15:25'
insert into data select '03-06-2020','Altered',123425,'6/3/2020 14:45:25'
insert into data select '03-06-2020','Altered',123425,'6/3/2020 14:12:25'
insert into data select '03-06-2020','Insert ',112625,'6/3/2020 15:12:25'
insert into data select '03-06-2020','Altered',112625,'6/3/2020 16:12:25'
insert into data select '03-06-2020','Altered',112625,'6/3/2020 17:12:25'
go
select 'SQL OutPut'
, alm_id
, max(case when action = 'Insert ' then occur_time end) as start_time
, max(case when action = 'Altered' then occur_time end) as Last_Occur_time
, max(case when action = 'Cleared' then occur_time end) as End_Time
from data
group by alm_id
