Database Query - How to get total number of New Issues on specific date ?

Added by Sudheendra Panganamala about 9 years ago

a. How to get total number of New Issues as on specific date ?
b. How to get total number of Resolved Issues as on specific date ?

Replies (4)

RE: Database Query - How to get total number of New Issues on specific date ? - Added by Pavel Potcheptsov about 9 years ago

mysql> SELECT COUNT(issues.id) AS NumberOfNewIssues FROM issues WHERE issues.created_on LIKE '2014-01-15%';
+-------------------+
| NumberOfNewIssues |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(issues.id) AS NumberOfClosedIssues FROM issues WHERE issues.closed_on LIKE '2014-01-15%';
+----------------------+
| NumberOfClosedIssues |
+----------------------+
|                   28 |
+----------------------+
1 row in set (0.00 sec)

RE: Database Query - How to get total number of New Issues on specific date ? - Added by Sudheendra Panganamala about 9 years ago

Hi Pavel Potcheptsov

Thanks for reply.

I need the report as per attachment.

A function with parameters(AsonDate, StatusMsg) should return count.

RE: Database Query - How to get total number of New Issues on specific date ? - Added by Alexandre Sabán about 9 years ago

Hi Sudheendra Panganamala, add GROUP BY and LEFT JOIN to Pavel query like that :

mysql> SELECT COUNT(i.id) AS NumberOfNewIssues, isst.name AS statusName
 FROM issues i
 LEFT JOIN issue_statuses isst ON isst.id=i.status_id

WHERE i.created_on LIKE '2014-01-15%' 

GROUP BY status;

(1-4/4)