Usage scenarios and usage of judgment and rounding down in MySQL

Recently, there is a demand like this , Query the user's leave days and the number of days to log .
The data are as follows ：

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `type` tinyint(2) NOT NULL DEFAULT '0' COMMENT ' Leave type  0= all day  1= In the morning  2= Afternoon ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (1, 40, '2022-05-16', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (2, 40, '2022-05-17', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (3, 40, '2022-05-18', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (4, 40, '2022-05-19', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (5, 40, '2022-05-31', 2);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (6, 40, '2022-05-31', 1);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (7, 40, '2022-05-20', 1);
');

Need one ： Query leave days

According to the data in the table , We need to find out the users id by 40 How many days of leave did your users take in May . The type type 0= all day （ Ask for a day off ）1= In the morning 2= Afternoon

We can use the following SQL Inquire about ：

SELECT
	*,
	( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long 
FROM
	test2 
WHERE
	date BETWEEN '2022-05-01' 
	AND '2022-05-31';

obtain ：

among ,time_long That is, the length of leave , Half a day is recorded as 0.5, One day is recorded as 1.

Then we can easily calculate the leave duration of the user in this time period . We use sum Make a subquery .

SELECT
	SUM( time_long ) AS time_long 
FROM
	(
	SELECT
		*,
		( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long 
	FROM
		test2 
	WHERE
		date BETWEEN '2022-05-01' 
	AND '2022-05-31' 
	) a

obtain ：

By this time, our first requirement is completed .

Demand two ： Query the number of logs that should be written

The number of logs is calculated as （ Take may as an example ）： The actual number of days in May is ：31 God , Excluding holidays, Saturdays and Sundays, the actual working days are ：20 God . Here we can wait through holidays API The interface calculates the acquisition duration in days .

hypothesis ： We get the actual working days in this time range through the interface ：20 God . then Ask for a day off without writing a diary , Ask for leave for half a day and write a diary .

Based on the existing data , You can get , The number of days this user needs to write logs is ：15 God

Be careful ： there 5 month 31 I took a day off together , therefore 5 month 31 You don't have to write a log every day .5 month 20 You need to write a log every day .

Here's a recommended one The holiday season API：
The holiday season API

Then here we need to use a function of rounding down FLOOR

SELECT
	FLOOR(
	SUM( time_long )) AS time_long 
FROM
	(
	SELECT
		date,
		SUM( time_long ) AS time_long 
	FROM
		(
		SELECT
			*,
			( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long 
		FROM
			test2 
		WHERE
			date BETWEEN '2022-05-01' 
			AND '2022-05-31' 
		) a 
	GROUP BY
	date 
	) b;

Reverse query to get the number of days that should not be written to the log ：5 God .

Finally, the number of days to write logs is calculated by code ：20 - 5 = 15 God

You are welcome to comment and make corrections .