Linking multiple tickets

Database
Fan

Linking multiple tickets

For our ticketing system, we would like to link multiple tickets that follow each other closely to the original (root) ticket.

When there is less than or equal to 5 days between the end date of a ticket and the start date of the next for a same customer, we consider that it is the same problem, even if this happens several times.

 

So when we have the following data :

cust_idticket_idstart_dtend_dt
101112018-01-012018-01-03
101122018-01-052018-01-06
101132018-01-092018-01-12
101142018-02-012018-02-05
101152018-02-082018-02-15
102212018-02-152018-02-18
102222018-04-032018-04-12
102232018-04-152018-04-15

 

We would like to obtain the following result :

cust_idticket_idroot_ticket_id
1011111
1011211
1011311
1011414
1011514
1022121
1022222
1022322

 

Any ideas on how to accomplish this?

 

Thanks a lot,

Stef.


Accepted Solutions
Teradata Employee

Re: Linking multiple tickets

You can go for a recursive query :

 

create multiset volatile table mvt_mydata, no log
( cust_id       byteint not null
, ticket_id     byteint not null
, start_dt      date    not null
, end_dt        date        null
)
primary index (cust_id)
on commit preserve rows;

insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 11, date '2018-01-01', date '2018-01-03');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 12, date '2018-01-05', date '2018-01-06');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 13, date '2018-01-09', date '2018-01-12');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 14, date '2018-02-01', date '2018-02-05');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 15, date '2018-02-08', date '2018-02-15');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 21, date '2018-02-15', date '2018-02-18');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 22, date '2018-04-03', date '2018-04-12');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 23, date '2018-04-15', date '2018-04-15');


with recursive cte_recurs (cust_id, ticket_id, root_ticket_id, start_dt, end_dt) as
(
  select cust_id, ticket_id, ticket_id, start_dt, end_dt
    from mvt_mydata
 qualify row_number() over(partition by cust_id order by ticket_id asc) = 1
   union all
  select mdt.cust_id
       , mdt.ticket_id
       , case when cte.end_dt + 5 >= mdt.start_dt then cte.root_ticket_id else mdt.ticket_id end
       , mdt.start_dt
       , mdt.end_dt
    from mvt_mydata as mdt
    join cte_recurs as cte  on cte.cust_id   = mdt.cust_id
                           and cte.ticket_id = mdt.ticket_id - 1
)
  select cust_id, ticket_id, root_ticket_id
    from cte_recurs
order by ticket_id asc;

cust_id	ticket_id	root_ticket_id
101	11	        11
101	12	        11
101	13	        11
101	14	        14
101	15	        14
102	21	        21
102	22	        22
102	23	        22

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Linking multiple tickets

You can go for a recursive query :

 

create multiset volatile table mvt_mydata, no log
( cust_id       byteint not null
, ticket_id     byteint not null
, start_dt      date    not null
, end_dt        date        null
)
primary index (cust_id)
on commit preserve rows;

insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 11, date '2018-01-01', date '2018-01-03');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 12, date '2018-01-05', date '2018-01-06');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 13, date '2018-01-09', date '2018-01-12');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 14, date '2018-02-01', date '2018-02-05');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (101, 15, date '2018-02-08', date '2018-02-15');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 21, date '2018-02-15', date '2018-02-18');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 22, date '2018-04-03', date '2018-04-12');
insert into mvt_mydata (cust_id, ticket_id, start_dt, end_dt) values (102, 23, date '2018-04-15', date '2018-04-15');


with recursive cte_recurs (cust_id, ticket_id, root_ticket_id, start_dt, end_dt) as
(
  select cust_id, ticket_id, ticket_id, start_dt, end_dt
    from mvt_mydata
 qualify row_number() over(partition by cust_id order by ticket_id asc) = 1
   union all
  select mdt.cust_id
       , mdt.ticket_id
       , case when cte.end_dt + 5 >= mdt.start_dt then cte.root_ticket_id else mdt.ticket_id end
       , mdt.start_dt
       , mdt.end_dt
    from mvt_mydata as mdt
    join cte_recurs as cte  on cte.cust_id   = mdt.cust_id
                           and cte.ticket_id = mdt.ticket_id - 1
)
  select cust_id, ticket_id, root_ticket_id
    from cte_recurs
order by ticket_id asc;

cust_id	ticket_id	root_ticket_id
101	11	        11
101	12	        11
101	13	        11
101	14	        14
101	15	        14
102	21	        21
102	22	        22
102	23	        22

 

Fan

Re: Linking multiple tickets

Thanks a lot!

I had to add a ticket sequence because in practice the ticket ID's are not sequential, but now it works like I wanted.

Teradata Employee

Re: Linking multiple tickets

You're right, this should cover your case (posting for others) :

with recursive cte_recurs (cust_id, ticket_id, root_ticket_id, start_dt, end_dt, ticket_ord) as
(
select cust_id, ticket_id, ticket_id, start_dt, end_dt, ticket_ord
  from cte_mydata
 where ticket_ord = 1
 union all
select mdt.cust_id
     , mdt.ticket_id
     , case when cte.end_dt + 5 >= mdt.start_dt then cte.root_ticket_id else mdt.ticket_id end
     , mdt.start_dt
     , mdt.end_dt
     , mdt.ticket_ord
  from cte_mydata as mdt
  join cte_recurs as cte  on cte.cust_id    = mdt.cust_id
                         and cte.ticket_ord = mdt.ticket_ord - 1
)
  ,  cte_mydata (cust_id, ticket_id, start_dt, end_dt, ticket_ord) as
(
select cust_id, ticket_id, start_dt, end_dt
     , row_number() over(partition by cust_id order by ticket_id asc)
  from mvt_mydata
)
  select cust_id, ticket_id, root_ticket_id
    from cte_recurs
order by cust_id asc, ticket_id asc;