Hi @MJ-4179,
Please refer below updated one:
drop table if exists Tickets,ClosedTickets,ReOpenedTickets
create table Tickets
(TicketID Int, TicketType Varchar(20), TIcketLoggedDate DateTime, UserID INT)
insert into Tickets values
(1,'Incident','01/01/2020 19:33:00',1000),
(2,'Incident','01/13/2020 18:23:00',1000),
(3,'Incident','01/15/2020 22:33:00',1000),
(4,'SR','01/01/2020 13:33:00',1000),
(5,'Incident','02/02/2020 09:33:00',2000),
(6,'Incident','01/22/2020 11:33:00',3000),
(7,'Major','01/21/2020 00:33:00',4000)
create table ClosedTickets
(TicketID INT, ClosedTime DateTime)
insert into ClosedTickets values
(1,'01/12/2020 00:00'),
(4,'01/19/2020 00:00'),
(1,'01/15/2020 08:30'),
(1,'01/13/2020 10:11'),
(1,'01/12/2020 08:33')
create table ReOpenedTickets
(TicketID INT, ReOpenedTime DateTime)
insert into ReOpenedTickets values
(1,'01/13/2020 13:11'),
(1,'01/16/2020 22:11'),
(7,'01/23/2020 18:16')
;with cte as (
select TicketID,TicketType,TIcketLoggedDate from Tickets
union all
select b.TicketID,a.TicketType,min(b.ClosedTime) ClosedTime from Tickets a
inner join ClosedTickets b on a.TicketID=b.TicketID
left join (select *,lag(ReOpenedTime) over (PARTITION BY TicketID ORDER BY ReOpenedTime) ReOpenedTimepre
from ReOpenedTickets) c on b.TicketID=c.TicketID
where (b.ClosedTime<c.ReOpenedTime and (b.ClosedTime>c.ReOpenedTimepre or c.ReOpenedTimepre is null)) or c.ReOpenedTime is null
group by b.TicketID,a.TicketType,c.ReOpenedTime
union all
select a.TicketID,a.TicketType,b.ReOpenedTime from Tickets a
inner join ReOpenedTickets b on a.TicketID=b.TicketID
where exists (select 1 from ClosedTickets where TicketID=b.TicketID)
)
,cte1 as (
select *,ROW_NUMBER() OVER(PARTITION BY TicketID ORDER BY TIcketLoggedDate) rn from cte )
select a.TicketID,a.TicketType
,a.TIcketLoggedDate,b.TIcketLoggedDate TicketClosedDate
,case when b.TIcketLoggedDate is null then 'Open' else 'Closed' end CuurentStatus
from cte1 a left join cte1 b
on a.TicketID=b.TicketID and a.rn = b.rn -1 and b.rn % 2 = 0
where a.rn % 2 =1
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.