Filter and re-arrange data based on multiple conditions

rajhesh · June 30, 2020, 10:54am

Hi All,

  I have a data in which I need to filter and re-arrange columns and add a new column based on multiple conditions. Couldn't figure it out how to be done. Kindly support. I was able to sort two columns and then I'm stuck how to produce the code and logic.

Alm id has multiple occurances. Now I need to re-arrange in such a way that those alm ids that has Action as "Insert" with Occur_time, should be consider as start time. Those alm ids whose action is Update with latest Occur_time should be consider as last occur time and those alm id with actions as delete with occur time, should be consider as End time (If delete is not available, then we should replace it with its start date 23:59:59). Below is the table (table name - almdata) and output required. Kindly share the logic and query through which this can be achieved.

Date	        Action	Alm id	 Occur_time

01-06-2020 Insert 123457 6/1/2020 11:30:45
01-06-2020 Altered 123457 6/1/2020 11:35:45
01-06-2020 Altered 123457 6/1/2020 11:36:40
01-06-2020 Altered 123457 6/1/2020 11:35:40
01-06-2020 Altered 123457 6/1/2020 11:37:45
01-06-2020 Cleared 123457 6/1/2020 11:45:45
02-06-2020 Insert 123455 6/2/2020 00:15:25
02-06-2020 Altered 123455 6/2/2020 00:15:25
02-06-2020 Altered 123455 6/2/2020 00:25:15
02-06-2020 Altered 123455 6/2/2020 00:26:05
03-06-2020 Cleared 123455 6/3/2020 00:15:25
03-06-2020 Insert 123425 6/3/2020 14:15:25
03-06-2020 Cleared 123425 6/3/2020 16:15:25
03-06-2020 Altered 123425 6/3/2020 14:45:25
03-06-2020 Altered 123425 6/3/2020 14:12:25
03-06-2020 Insert 112625 6/3/2020 15:12:25
03-06-2020 Altered 112625 6/3/2020 16:12:25
03-06-2020 Altered 112625 6/3/2020 17:12:25

Date Alm id Start_time Last_Occur_time End_Time
01-06-2020 123457 6/1/2020 11:30:45 6/1/2020 11:37:45 6/1/2020 11:45:45
02-06-2020 123455 6/2/2020 00:15:25 6/2/2020 00:26:05 6/3/2020 00:15:25
03-06-2020 123425 6/3/2020 14:15:25 6/3/2020 14:45:25 6/3/2020 16:15:25
03-06-2020 112625 6/3/2020 15:12:25 6/3/2020 17:12:25 6/3/2020 23:59:59

Thanks,
Raj.

harishgg1 · June 30, 2020, 3:00pm

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

harishgg1 · June 30, 2020, 4:01pm

hi

i fixed the issue .. with NULL
only thing is date field has to be datetime

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 else date1+'23:59:59' end ) as End_Time 
from data 
 group by alm_id

rajhesh · June 30, 2020, 4:11pm

Thanks a lot harish. It works for me.