Requesting T SQL Logic for the below scenario

Question

Hi All,

I have data like below.

issue_id	Created	        from_string	           to_string  
420145	2018-06-13	null	                          EPC-Management  
420145	2018-11-18	null	                          L3-7  
420145	2018-11-18	EPC-Management	  null  
420145	2018-11-18	null	                          L2-4  
420145	2018-11-21	L3-7	                          null  
420145	2018-11-21	null	                          Management  
420145	2018-11-25	Management	          null  
420145	2018-11-25	null	                          XM  
  

Need to get the output like below

issue_id	Created	        final_string  
420145	2018-06-13	EPC-Management  
420145	2018-11-18	L3-7,L2-4  
420145	2018-11-21	L2-4,Management  
420145	2018-11-25	L2-4,XM  
  

Ex: On 2018-06-13, issue is having component EPC-Management, So output for this record will be EPC-Management
on 2018-11-18 , issue is having 3 records in the source . Two components added (L3-7, L2-4) and previous component removed .
So output is L3-7,L2-4
on 2018-11-21 , issue is having 2 records in the source . One component added (Management) and one component removed (L3-7) .
So output is L2-4,Managment
on 2018-11-25 , issue is having 2 records in the source . One component added (XM) and one component removed (Management) .
So output is L2-4,XM

Could any one please help me on this

Answer 1

To obtain reliable results for new scenario, I think that it is necessary to have a column that orders the rows (for example a Created with distinct time part).

The next variation relies on default ordering:

;
with T1 as
(
    select *, 
        row_number() over (partition by issue_id order by Created) i
    from #temp
),
T2 as
(
    select *, 
        row_number() over (partition by issue_id, Created order by i desc) j
    from T1
),
T3 as
(
    select *, cast(isnull(to_string, '') as varchar(max)) as final_string
    from T2
    where i = 1
    union all
    select T2.*, 
        cast(stuff((select ',' + s from (
            select value from string_split(T3.final_string, ',') 
            except 
            select * from (values (T2.from_string)) d(s) 
            union all
            select * from (values (T2.to_string)) d(s)
        ) d(s) for xml path('')), 1, 1, '') as varchar(max))
    from T2
    inner join T3 on t3.issue_id = t2.issue_id and T3.i+1 = T2.i
)
select issue_id, Created, final_string
from T3
where j = 1
order by issue_id, i

Results:

/*

issue_id    Created final_string
420145  2018-06-13  EPC-Management
420145  2018-11-18  L3-7,L2-4
420145  2018-11-21  L2-4,Management
420145  2018-11-25  L2-4,XM
420145  2018-12-10  XM
420145  2018-12-23  XM,L2-4

*/

Answer 2

Hi ,

I have implemeted below logic and working as expected. Thanks all for your inputs.

drop table if exists #temp
create table #temp
(
issue_id varchar(100)
,Created date
,from_string varchar(100)
,to_string varchar(100)
)
insert into #temp select '420145','2018-06-13',NULL,'EPC-Management'
insert into #temp select '420145','2018-11-18',NULL,'L3-7'
insert into #temp select '420145','2018-11-18','EPC-Management',NULL
insert into #temp select '420145','2018-11-18',NULL,'L2-4'
insert into #temp select '420145','2018-11-21','L3-7',NULL
insert into #temp select '420145','2018-11-21',NULL,'Management'
insert into #temp select '420145','2018-11-25','Management',NULL
insert into #temp select '420145','2018-11-25',NULL,'XM'
insert into #temp select '420145','2018-12-10','L2-4',NULL
insert into #temp select '420145','2018-12-23',NULL,'L2-4'
insert into #temp select '420145','2019-01-17',NULL,'EPC-Management'
insert into #temp select '420145','2019-01-17',NULL,'L3-7'
insert into #temp select '420145','2019-02-20','L3-7',NULL
insert into #temp select '420145','2019-02-20','EPC-Management',NULL

--------------------------------------------------------------

;with cte_created as (
select issue_id,to_string,created
,lead(created,1,getdate())over(partition by issue_id,to_string order by created) lead_created
from #temp
where to_string is not null
)
,cte_deleted as (
select issue_id,from_string,created as deleted from #temp
where from_string is not null
)
,cte_distinct as (
select distinct issue_id,created from #temp
)
,cte_inter as (
select a.issue_id,a.to_string,a.created,b.deleted from cte_created a
left join cte_deleted b on a.issue_id=b.issue_id and a.to_string=b.from_string and b.deleted between a.created and a.lead_created
)
,cte_final as
(
select a.issue_id,a.created,b.to_string from cte_distinct a
left join cte_inter b on a.issue_id=b.issue_id and a.created >= b.created and a.created<coalesce(b.deleted,getdate())
)

select a.issue_id,a.created,
stuff((select ','+to_string from cte_final b where a.issue_id=b.issue_id and a.created=b.created for xml path('')),1,1,'') as final_string
from
cte_final a
group by a.issue_id,a.created