mysql: counting number of tickets which are open per day basis

Question

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.

Answer 1

You can use conditional sum for this calculation something as

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) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    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 |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> 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) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

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 ; 

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ; 

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Answer 2

I see a problem when you don't have a ticket created on specific date but you get ticket closed. Example, I created like this:

+----+---------------------+---------------------+----------+
| id | created             | resolved            | status   |
+----+---------------------+---------------------+----------+
|  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     |
| 10 | 2015-05-14 00:00:00 | 1970-01-01 00:00:00 | open     |
| 11 | 2015-05-10 00:00:00 | 2015-05-13 00:00:00 | resolved |
+----+---------------------+---------------------+----------+


+--------------+------+
| created_date | open |
+--------------+------+
| 2015-05-10   |    4 |
| 2015-05-11   |    6 |
| 2015-05-12   |    8 |
| 2015-05-14   |    9 |
+--------------+------+