Help with T-SQL problem

Question

I have a scenario for which I am struggling to come up with a query.

We get some tickets logged every day by different users. Every time a user logs a ticket we create a new ticket reference for it. Once the ticket resolves we mark it as completed but if the user is not satisfied he/she can re-open the same ticket.

We have to readdress the issue and mark the ticket as completed. The user might accept it or re-open it again for further investigation. This cycle will continue.

So basically a ticket gets closed reopened N number of times.

We log all this information in 3 tables.

Table 1 --> Tickets (TicketID Int, TicketType Varchar, TIcketLoggedDate DateTime, UserID INT)
Table 2 --> ClosedTickets (TicketID INT, ClosedTime DateTime)
Table 3 --> ReOpenedTickets (TicketID INT, ReOpenedTime DateTime)

Let's say the data looks like this.

Ticket Close Table looks similar to this

Ticket ReOpenTable look something similar to this

Now I want to generate a report using these 3 tables to show all the tickets whether they are currently open or closed. Also the history of ticket closures/reopens

So I need to write a query which should give me this result.

Hope the result set is self-explanatory.

I tried different types of queries but I am not able to get the output. Could someone please assist?

Thanks in advance.

-Roger

Answer 1

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.

Answer 2

Hi @MJ-4179,

Welcome to Microsoft Q&A!

Please refer below:

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')  
  
create table ReOpenedTickets  
(TicketID INT, ReOpenedTime DateTime)  
  
insert into  ReOpenedTickets values  
(1,'01/13/2020 13:11'),  
(1,'01/16/2020 22:11')  
  
 ;with cte as (  
 select TicketID,TicketType,TIcketLoggedDate from Tickets   
 union all  
 select a.TicketID,a.TicketType,b.closedtime from Tickets a   
 inner join  ClosedTickets b on a.TicketID=b.TicketID  
 union all  
 select a.TicketID,a.TicketType,b.ReOpenedTime  from Tickets a  
 inner join ReOpenedTickets b on a.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  

Output:

TicketID TicketType TIcketLoggedDate TicketClosedDate CuurentStatus  
1 Incident 2020-01-01 19:33:00.000 2020-01-12 00:00:00.000 Closed  
1 Incident 2020-01-13 13:11:00.000 2020-01-15 08:30:00.000 Closed  
1 Incident 2020-01-16 22:11:00.000 NULL Open  
2 Incident 2020-01-13 18:23:00.000 NULL Open  
3 Incident 2020-01-15 22:33:00.000 NULL Open  
4 SR 2020-01-01 13:33:00.000 2020-01-19 00:00:00.000 Closed  
5 Incident 2020-02-02 09:33:00.000 NULL Open  
6 Incident 2020-01-22 11:33:00.000 NULL Open  
7 Major 2020-01-21 00:33:00.000 NULL Open  

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.

Answer 3

Thanks, Melissa,

It worked, but there's a slight challenge.

It's actually a data quality issue we have.

We might sometimes get a few bogus entries in both ClosedTickets table as well as ReOpenedTickets table (we don't currently have bogus entries in the latter table yet but we might get them).

Those yellow highlighted records in ClosedTickets table are incorrect records. They got closed multiple times without the ticket being reOpened. Whenever this happens we should pick the minimum closedTime of the closed tickets until it got reOpened.

Similarly, there's one more possibility that a ticket might get ReOpened without it being closed. We should ignore them.

Sorry, I didn't like the system design but it what it is.

So the Output I require is the same as my original request.

Thank you for your help.

Regards,
-Roger