Selecting ticketid with status history

Question

I'm trying to select tickets that had status that are currently open (ie on Hold, Pending suppliers, In progress) with a specific ticket class ='SR' between 2 dates (1 April 2015 - 30 April 2015).

select * from tkt_table where class = 'SR' and status = 'INPROG' or status = 'PENDING' or status = 'INC'... order date by datereport desc

There are two tables, the one that has ticket information is tkt_table and the other one has the ticket status history tktStatus_table. they both have tktid as key.

How do I get this SQL statement to show me all of the tickets that had the statuses changed between a selected date(1 April - 30April)

Many thanks :)

Table on left is tkt_table and right is tkt_status

Answer 1

You can use the COUNT(DISTINCT status) in the having clause, like this:

SELECT * FROM
tkt_table t inner join 
(SELECT tktid, count(distinct status) cnt FROM tktStatus_table 
 WHERE dateReport >= '20150401' AND dateReport < '20150501'
 GROUP BY tktid 
 HAVING COUNT(DISTINCT status) > 1) as a on t.tktid=a.tktid

Answer 2

You can join both the table and then Group By Ticket Id

SELECT tiketId from tkt_table tt JOIN  tktStatus_table  ts on tt.tiketId = ts.tiketId
where Class = 'SR' and dateReport Between '2015-04-01' AND '2015-04-30'
Group BY tktid
Having Count(DISTINCT status) > 1