I have a table tabName
with the following rows:
id(PK) .. Created(datetime) Resolved(datetime) Status(new/resolved/open)
1 2015-05-10 00:00:00 1970-01-01 .. open
2 2015-05-10 .. 1970-01-01 .. new
3 2015-05-10 2015-05-12 resolved
4 2015-05-11 .. 1970-01-01 .. open
5 2015-05-11 .. 1970-01-01 .. new
6 2015-05-11 2015-05-11 resolved
7 2015-05-12 .. 1970-01-01 .. open
8 2015-05-12 .. 1970-01-01 .. new
9 2015-05-12 2015-05-12 resolved
NOTE: 1970-01-01 .. is default time for all the tickets which are still open.
I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3
, resolved date is not 10 but it's closed on 12 May.
I framed a query like
SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1)
FROM tabName
WHERE DATE_FORMAT(Resolved, '%d-%m') > DATE_FORMAT(Created, '%d-%m')
AND YEAR(Created) = '2015'
GROUP BY createdd;
The above query gets all the tickets whose Resolved
date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.
Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.
Updated: As suggested by Abhik, i have tried in sqlfiddle site.
Here is what i created:
CREATE TABLE test
(
idint,
createddatetime,
resolveddatetime, status varchar(15), PRIMARY KEY (id));
INSERT INTO test
(
id,'created','resolved',
status`)
VALUES
(1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),
(2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),
(3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),
(4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),
(5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),
(6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),
(7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),
(8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),
(9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')
;
`
When i run
` select created_date, @open:= @open+open_ticket-resolved as open from( select date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and ( date(resolved) > date(created) or
date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
sum(case when status = 'resolved' and date(resolved) = date(created) then 1
else 0 end) as resolved
from test
where
YEAR(Created) = '2015'
group by created_date )x,
(select @open:=0)r order by created_date ; `
I got following output
created_date open
10-05 3
11-05 5
12-05 8
Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.