How can i count project status by change/update made

I want to count status change by id from to

I have a mysql status table which has a name with pre implementation, implementation and operations states.

• a project contains project id, name, start date fields.
• both status and projects have a many to many relationship called project_status table contains project_id, status_id, date_of_progress.

so i want to count all projects within this month who has update/change their status_id

1. from pre-implementation to implementations
2. from implementations to operations
3. from pre-implementation to operations

—

CREATE TABLE `status` (
  `status_id` int(11) NOT NULL,
  `status_name` varchar(30) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status_name_tg` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `status` (`status_id`, `status_name`, `description`, `status_name_tg`) VALUES
(1, 'Pre Implementation', 'Operational', 'Pre Implementation'),
(2, 'Implementation', NULL, 'Implementation'),
(3, 'Operational', NULL, 'Operational'),
(4, 'Inactive', NULL, 'Inactive'),
(5, 'Cancellation', NULL, 'Cancellation');

CREATE TABLE `project_status` (
  `project_status_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `project_id` int(11) NOT NULL,
  `reason_for_cancellation` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `project_status` (`project_status_id`, `status_id`, `time`, `project_id`, `reason_for_cancellation`) VALUES
(1, 1, '2019-02-24 21:51:50', 1, NULL),
(2, 2, '2019-03-26 21:52:57', 1, '        '),
(3, 1, '2019-04-30 21:57:57', 2, NULL),
(4, 1, '2019-05-26 22:04:08', 3, NULL),
(5, 3, '2019-08-24 22:06:36', 1, '        '),
(6, 2, '2019-08-11 22:07:05', 3, '        '),
(8, 1, '2019-08-01 00:14:41', 6, NULL),
(9, 1, '2019-08-09 12:11:22', 7, NULL),
(10, 1, '2019-08-09 12:15:22', 8, NULL),
(11, 3, '2019-08-14 10:07:49', 7, NULL),
(12, 2, '2019-08-14 10:10:45', 8, NULL),
(13, 2, '2019-08-26 17:16:02', 6, 'NULL');
(14, 3, '2019-08-26 17:16:02', 6, 'NULL');


CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `start_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `projects` (`project_id`, `name`, `start_date`) VALUES
(1, 'site A', '2019-02-01 00:00:00'),
(2, 'site B', '2019-03-12 00:00:00'),
(3, 'Site C', '2019-04-04 00:00:00'),
(4, 'Site D', '2019-05-03 00:00:00'),
(5, 'site E', '2019-06-01 00:00:00'),
(6, 'site F', '2019-08-02 00:00:00'),
(7, 'Site J', '2019-08-01 00:00:00'),
(8, 'Site H', '2019-08-05 00:00:00');

Desire Output

|pre - implementations|implementations - operations| pre - operations|
|2                    | 1                          |2                |

Answer

I think the key is find what is the last status for each project on each month:

SQL DEMO

SELECT YEAR(time) as year
     , MONTH(time) as month
     , project_id
     , MAX(status_id) as last_status
FROM project_status
WHERE status_id < 4   -- ignore (inactive, cancellation)
GROUP BY YEAR(time), MONTH(time), project_id;

OUTPUT

+------+-------+------------+-------------+
| year | month | project_id | last_status |
+------+-------+------------+-------------+
| 2019 |     2 |          1 |           1 |
| 2019 |     3 |          1 |           2 |
| 2019 |     4 |          2 |           1 |
| 2019 |     5 |          3 |           1 |
| 2019 |     8 |          1 |           3 |
| 2019 |     8 |          3 |           2 |
| 2019 |     8 |          6 |           2 |
| 2019 |     8 |          7 |           3 |
| 2019 |     8 |          8 |           2 |
+------+-------+------------+-------------+

Now using a correlated query you can found out the last status before the current month:

SELECT *, (SELECT MAX(p.status_id)
           FROM project_status p
           WHERE p.time < CONCAT(t.year,'/', t.month,'/1')
             AND p.project_id = t.project_id
           ) as previous_status
FROM (
    SELECT YEAR(time) as year
         , MONTH(time) as month
         , project_id
         , MAX(status_id) as last_status
    FROM project_status
    WHERE status_id < 4
    GROUP BY YEAR(time), MONTH(time), project_id
) t

OUTPUT

+------+-------+------------+-------------+-----------------+
| year | month | project_id | last_status | previous_status |
+------+-------+------------+-------------+-----------------+
| 2019 |     2 |          1 |           1 |                 |
| 2019 |     3 |          1 |           2 |               1 |
| 2019 |     4 |          2 |           1 |                 |
| 2019 |     5 |          3 |           1 |                 |
| 2019 |     8 |          1 |           3 |               2 |
| 2019 |     8 |          3 |           2 |               1 |
| 2019 |     8 |          6 |           2 |                 |
| 2019 |     8 |          7 |           3 |                 |
| 2019 |     8 |          8 |           2 |                 |
+------+-------+------------+-------------+-----------------+

Now just do some conditional counts

SELECT q.year
     , q.month
     , COUNT(CASE WHEN q.last_status = 2 THEN 1 END) as pre_implementation
     , COUNT(CASE WHEN q.last_status = 3 
                   AND q.previous_status = 2 THEN 1 END) as implementation_operation
     , COUNT(CASE WHEN q.last_status = 3 THEN 1 END) as pre_operation
FROM (
    SELECT *, (SELECT MAX(p.status_id)
               FROM project_status p
               WHERE p.time < CONCAT(t.year,'/', t.month,'/1')
                 AND p.project_id = t.project_id
               ) as previous_status
    FROM (
        SELECT YEAR(time) as year
             , MONTH(time) as month
             , project_id
             , MAX(status_id) as last_status
        FROM project_status
        WHERE status_id < 4
        GROUP BY YEAR(time), MONTH(time), project_id
    ) t
) q
GROUP BY q.year, q.month

OUTPUT

+------+-------+--------------------+--------------------------+---------------+
| year | month | pre_implementation | implementation_operation | pre_operation |
+------+-------+--------------------+--------------------------+---------------+
| 2019 |     2 |                  0 |                        0 |             0 |
| 2019 |     3 |                  1 |                        0 |             0 |
| 2019 |     4 |                  0 |                        0 |             0 |
| 2019 |     5 |                  0 |                        0 |             0 |
| 2019 |     8 |                  3 |                        1 |             2 |
+------+-------+--------------------+--------------------------+---------------+