I need to create a DB2 query to pull ticket report from maximo for reopened tickets

Question

I need to find out the total tickets reopened in a month. I tried one query but it gives results only for the tickets that are presently open i.e. (actualfinish is not null) and (status not in('RESOLVED','CLOSED))

The other search criteria can be the changedate for the status but I don't know the query to compare the changedate for the status.

It can be something like the changedate for any open status should be greater than the first resolved.

Please help. Need it on urgent basis.

We will also need starting sample data, and an expected set of results. Expecting us to divine your setup isn't usually going to work. — Clockwork-Muse, Nov 02 '13 at 11:13

score 0 · Answer 1 · edited Jun 02 '15 at 07:12

Plz try this query, you got all the re-open ticket.

select ticketid from incident where ticketid in ( 

select b.ticketid from
(select distinct ticketid, min(changedate) x from tkstATUS WHERE orgid='IMCT' and status='RESOLVED' group by ticketid) a
inner join
(select distinct ticketid, max(changedate) y from tkstATUS WHERE orgid='IMCT' and status in ('INPROG') group by ticketid ) b
on a.ticketid=b.ticketid
where a.x < b.y)

with ur

I need to create a DB2 query to pull ticket report from maximo for reopened tickets

1 Answers1