MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');
SELECT * FROM status;
SELECT * FROM project_status;
SELECT * FROM projects;


status_id
status_name
description
status_name_tg


1
Pre Implementation
Operational
Pre Implementation

2
Implementation
null
Implementation

3
Operational
null
Operational

4
Inactive
null
Inactive

5
Cancellation
null
Cancellation




project_status_id
status_id
time
project_id
reason_for_cancellation


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




project_id
name
start_date


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


SELECT YEAR(time) as year
     , MONTH(time) as month
     , COUNT(*) as total_status
FROM project_status
GROUP BY YEAR(time), MONTH(time);



year
month
total_status


2019
2
1

2019
3
1

2019
4
1

2019
5
1

2019
8
8


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;


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


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


year
month
project_id
last_status
previous_status


2019
2
1
1
null

2019
3
1
2
1

2019
4
2
1
null

2019
5
3
1
null

2019
8
1
3
2

2019
8
3
2
1

2019
8
6
2
null

2019
8
7
3
null

2019
8
8
2
null

status_id	status_name	description	status_name_tg
1	Pre Implementation	Operational	Pre Implementation
2	Implementation	null	Implementation
3	Operational	null	Operational
4	Inactive	null	Inactive
5	Cancellation	null	Cancellation

project_status_id	status_id	time	project_id	reason_for_cancellation
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

project_id	name	start_date
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

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