MySQL 8.0

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

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `categories` (`cat_id`, `name`, `description`) VALUES
(1, 'agriculture', ''),
(2, 'manufactures ', ''),
(3, 'Technology', ''),
(4, 'services', '');



CREATE TABLE `investments` (
  `investment_id` int(11) NOT NULL,
  `investment_name` varchar(100) NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `investments` (`investment_id`, `investment_name`, `cat_id`) VALUES
(1, 'Company A', 3),
(2, 'Company B', 4),
(3, 'Company C', 1),
(4, 'Company D', 2),
(5, 'Company E', 3),
(6, 'Company F', 4),
(7, 'Company G', 1),
(8, 'Company H', 2);

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, NULL),
(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, NULL),
(6, 2, '2019-08-11 22:07:05', 3, NULL),
(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,
  `investment_id` int(11) NOT NULL    
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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


cat_id
name
description


1
agriculture


2
manufactures


3
Technology


4
services





investment_id
investment_name
cat_id


1
Company A
3

2
Company B
4

3
Company C
1

4
Company D
2

5
Company E
3

6
Company F
4

7
Company G
1

8
Company H
2




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
null

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
null

6
2
2019-08-11 22:07:05
3
null

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




project_id
name
start_date
investment_id


1
site A
2019-02-01 00:00:00
1

2
site B
2019-03-12 00:00:00
2

3
Site C
2019-04-04 00:00:00
3

4
Site D
2019-05-03 00:00:00
4

5
site E
2019-06-01 00:00:00
5

6
site F
2019-08-02 00:00:00
6

7
Site J
2019-08-01 00:00:00
7

8
Site H
2019-08-05 00:00:00
8


SELECT YEAR(time) as year
     , MONTH(time) as month
     , ps.*, p.*, i.*, c.*
FROM project_status ps
JOIN projects p
  ON ps.project_id = p. project_id
JOIN investments i 
  ON p.investment_id = i.investment_id
JOIN categories c
  ON i.cat_id = c.cat_id;


year
month
project_status_id
status_id
time
project_id
reason_for_cancellation
project_id
name
start_date
investment_id
investment_id
investment_name
cat_id
cat_id
name
description


2019
2
1
1
2019-02-24 21:51:50
1
null
1
site A
2019-02-01 00:00:00
1
1
Company A
3
3
Technology


2019
3
2
2
2019-03-26 21:52:57
1
null
1
site A
2019-02-01 00:00:00
1
1
Company A
3
3
Technology


2019
4
3
1
2019-04-30 21:57:57
2
null
2
site B
2019-03-12 00:00:00
2
2
Company B
4
4
services


2019
5
4
1
2019-05-26 22:04:08
3
null
3
Site C
2019-04-04 00:00:00
3
3
Company C
1
1
agriculture


2019
8
5
3
2019-08-24 22:06:36
1
null
1
site A
2019-02-01 00:00:00
1
1
Company A
3
3
Technology


2019
8
6
2
2019-08-11 22:07:05
3
null
3
Site C
2019-04-04 00:00:00
3
3
Company C
1
1
agriculture


2019
8
8
1
2019-08-01 00:14:41
6
null
6
site F
2019-08-02 00:00:00
6
6
Company F
4
4
services


2019
8
9
1
2019-08-09 12:11:22
7
null
7
Site J
2019-08-01 00:00:00
7
7
Company G
1
1
agriculture


2019
8
10
1
2019-08-09 12:15:22
8
null
8
Site H
2019-08-05 00:00:00
8
8
Company H
2
2
manufactures


2019
8
11
3
2019-08-14 10:07:49
7
null
7
Site J
2019-08-01 00:00:00
7
7
Company G
1
1
agriculture


2019
8
12
2
2019-08-14 10:10:45
8
null
8
Site H
2019-08-05 00:00:00
8
8
Company H
2
2
manufactures


2019
8
13
2
2019-08-26 17:16:02
6
null
6
site F
2019-08-02 00:00:00
6
6
Company F
4
4
services


2019
8
14
3
2019-08-26 17:16:02
6
null
6
site F
2019-08-02 00:00:00
6
6
Company F
4
4
services



SELECT YEAR(time) as year
     , MONTH(time) as month
     , c.name
     , COUNT(*) as total_status
FROM project_status ps
JOIN projects p
  ON ps.project_id = p. project_id
JOIN investments i 
  ON p.investment_id = i.investment_id
JOIN categories c
  ON i.cat_id = c.cat_id
GROUP BY YEAR(time), MONTH(time), c.name ;  


year
month
name
total_status


2019
2
Technology
1

2019
3
Technology
1

2019
4
services
1

2019
5
agriculture
1

2019
8
Technology
1

2019
8
agriculture
3

2019
8
services
3

2019
8
manufactures
2


SELECT YEAR(time) as year
     , MONTH(time) as month
     , c.name
     , ps.project_id
     , MAX(status_id) as last_status
FROM project_status ps
JOIN projects p
  ON ps.project_id = p. project_id
JOIN investments i 
  ON p.investment_id = i.investment_id
JOIN categories c
  ON i.cat_id = c.cat_id
GROUP BY YEAR(time), MONTH(time), c.name, ps.project_id;


year
month
name
project_id
last_status


2019
2
Technology
1
1

2019
3
Technology
1
2

2019
4
services
2
1

2019
5
agriculture
3
1

2019
8
Technology
1
3

2019
8
agriculture
3
2

2019
8
services
6
3

2019
8
agriculture
7
3

2019
8
manufactures
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
         , c.name
         , ps.project_id
         , MAX(status_id) as last_status
    FROM project_status ps
    JOIN projects p
      ON ps.project_id = p. project_id
    JOIN investments i 
      ON p.investment_id = i.investment_id
    JOIN categories c
      ON i.cat_id = c.cat_id
    GROUP BY YEAR(time), MONTH(time), c.name, ps.project_id
) t


year
month
name
project_id
last_status
previous_status


2019
2
Technology
1
1
null

2019
3
Technology
1
2
1

2019
4
services
2
1
null

2019
5
agriculture
3
1
null

2019
8
Technology
1
3
2

2019
8
agriculture
3
2
1

2019
8
services
6
3
null

2019
8
agriculture
7
3
null

2019
8
manufactures
8
2
null


SELECT q.year
     , q.month
     , q.name
     , 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 
                   AND (q.previous_status = 1 or q.previous_status IS NULL)
                  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
             , c.name
             , ps.project_id
             , MAX(status_id) as last_status
        FROM project_status ps
        JOIN projects p
          ON ps.project_id = p. project_id
        JOIN investments i 
          ON p.investment_id = i.investment_id
        JOIN categories c
          ON i.cat_id = c.cat_id
        GROUP BY YEAR(time), MONTH(time), c.name, ps.project_id
    ) t
) q
GROUP BY q.year, q.month, q.name


year
month
name
pre_implementation
implementation_operation
pre_operation


2019
2
Technology
0
0
0

2019
3
Technology
1
0
0

2019
4
services
0
0
0

2019
5
agriculture
0
0
0

2019
8
Technology
0
1
0

2019
8
agriculture
1
0
1

2019
8
services
0
0
1

2019
8
manufactures
1
0
0

investment_id	investment_name	cat_id
1	Company A	3
2	Company B	4
3	Company C	1
4	Company D	2
5	Company E	3
6	Company F	4
7	Company G	1
8	Company H	2

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	null
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	null
6	2	2019-08-11 22:07:05	3	null
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

project_id	name	start_date	investment_id
1	site A	2019-02-01 00:00:00	1
2	site B	2019-03-12 00:00:00	2
3	Site C	2019-04-04 00:00:00	3
4	Site D	2019-05-03 00:00:00	4
5	site E	2019-06-01 00:00:00	5
6	site F	2019-08-02 00:00:00	6
7	Site J	2019-08-01 00:00:00	7
8	Site H	2019-08-05 00:00:00	8

year	month	project_status_id	status_id	time	project_id	reason_for_cancellation	project_id	name	start_date	investment_id	investment_id	investment_name	cat_id	cat_id	name
2019	2	1	1	2019-02-24 21:51:50	1	null	1	site A	2019-02-01 00:00:00	1	1	Company A	3	3	Technology
2019	3	2	2	2019-03-26 21:52:57	1	null	1	site A	2019-02-01 00:00:00	1	1	Company A	3	3	Technology
2019	4	3	1	2019-04-30 21:57:57	2	null	2	site B	2019-03-12 00:00:00	2	2	Company B	4	4	services
2019	5	4	1	2019-05-26 22:04:08	3	null	3	Site C	2019-04-04 00:00:00	3	3	Company C	1	1	agriculture
2019	8	5	3	2019-08-24 22:06:36	1	null	1	site A	2019-02-01 00:00:00	1	1	Company A	3	3	Technology
2019	8	6	2	2019-08-11 22:07:05	3	null	3	Site C	2019-04-04 00:00:00	3	3	Company C	1	1	agriculture
2019	8	8	1	2019-08-01 00:14:41	6	null	6	site F	2019-08-02 00:00:00	6	6	Company F	4	4	services
2019	8	9	1	2019-08-09 12:11:22	7	null	7	Site J	2019-08-01 00:00:00	7	7	Company G	1	1	agriculture
2019	8	10	1	2019-08-09 12:15:22	8	null	8	Site H	2019-08-05 00:00:00	8	8	Company H	2	2	manufactures
2019	8	11	3	2019-08-14 10:07:49	7	null	7	Site J	2019-08-01 00:00:00	7	7	Company G	1	1	agriculture
2019	8	12	2	2019-08-14 10:10:45	8	null	8	Site H	2019-08-05 00:00:00	8	8	Company H	2	2	manufactures
2019	8	13	2	2019-08-26 17:16:02	6	null	6	site F	2019-08-02 00:00:00	6	6	Company F	4	4	services
2019	8	14	3	2019-08-26 17:16:02	6	null	6	site F	2019-08-02 00:00:00	6	6	Company F	4	4	services