DROP TABLE IF EXISTS `leave`;
CREATE TABLE `leave` (
`id` INT(11) NOT NULL,
`user_name` TEXT NOT NULL,
`user_id` INT(11) NOT NULL,
`leave_type` VARCHAR(255) NOT NULL,
`leave_start` DATE DEFAULT NULL,
`leave_end` DATE DEFAULT NULL,
`leave_days` INT DEFAULT NULL,
`status` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ORIGINAL FAKE ROWS
INSERT INTO `leave` (
`id`, `user_name`, `user_id`, `leave_type`,
`leave_start`, `leave_end`, `leave_days`, `status`
) VALUES
(121, 'One', 6, 'Casual', '2018-06-28', '2018-06-29', 1, 'Approved'),
(122, 'Two', 7, 'Annual', NULL, '2018-06-25', NULL, 'Requested'),
(123, 'Three', 8, 'Casual', '2018-06-30', '2018-06-25', -5, 'Approved');
SELECT * FROM `leave`;
-- RECONCILE AND AUTO-CALCULATE
UPDATE `leave`
SET
`leave_start` = @tmp := `leave_start`,
`leave_start` = `leave_end`,
`leave_end` = @tmp
WHERE
`leave_start` IS NOT NULL
AND `leave_end` IS NOT NULL
AND `leave_start` > `leave_end`;
ALTER TABLE `leave`
DROP COLUMN `leave_days`;
ALTER TABLE `leave`
ADD CONSTRAINT `chk_leave_days_nonneg`
CHECK (`leave_end` >= `leave_start`);
ALTER TABLE `leave`
ADD CONSTRAINT chk_cannot_approve_null_dates
CHECK (
status <> 'Approved'
OR (leave_start IS NOT NULL AND leave_end IS NOT NULL)
);
ALTER TABLE `leave`
ADD COLUMN `leave_days` INT AS (DATEDIFF(`leave_end`, `leave_start`)) STORED;
SELECT * FROM `leave`;
Telegram: DB developers channel