add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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