The details page should be checked in for an additional month in the project. Requirement: Users select the month and inquire about the details of the employee's check-in (including leave). The vacation table (tt_off work) already exists in the database (mysql). The structure of the table is as follows:

CREATE TABLE `tt_offwork` (
  `offwork_id` varchar(32) NOT NULL,
  `create_time` datetime DEFAULT NULL,
  `day_num` decimal(19,2) DEFAULT NULL COMMENT 'Days of leave',
  `from_date` datetime DEFAULT NULL COMMENT 'Starting date of leave',
  `instruction` varchar(255) DEFAULT NULL,
  `is_delete` bit(1) NOT NULL,
  `offwork_pic` varchar(255) DEFAULT NULL,
  `offwork_status` varchar(255) DEFAULT NULL COMMENT 'Whether the leave is approved or not',
  `offwork_type` varchar(255) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL COMMENT 'Excuse for leave',
  `sales_id` varchar(255) DEFAULT NULL COMMENT 'personnel ID',
  `to_date` datetime DEFAULT NULL COMMENT 'End date of leave',
  PRIMARY KEY (`offwork_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

At first, I didn't expect to take a few days off, so I matched the user's input month directly according to the field "from_date" to find out the record, and then according to the field "day_num", I could know how many days he would take a vacation in this month. For example, the user chooses the date "2017-1", and the background sql is as follows:

SELECT
    DAY(a.from_date) d, --On which day do you start taking leave?
    a.day_num,          --How many days off do you take?
    offwork_type        --Type of leave: leaveorVacation
FROM
   tt_offwork a
WHERE
   a.sales_id = '2c90e0825905beaf015905c0092b00de'
AND YEAR (a.from_date) = '2017'
AND MONTH (a.from_date) = '1'
AND a.offwork_status = 'pass'
GROUP BY
   a.sales_id,
   DATE_FORMAT(a.from_date, '%Y-%m-%d')
ORDER BY
   DAY (a.from_date) ASC

Later, we found that there was a problem with the data, and the statistics took 7 days off (according to the returned field "day_num"), but in fact this month only took 5 days off. Query sql found that the employee had crossed the month.

The employee took seven days off from January 27, 2017 to February 02, 2017. So two problems arise:
1. If you check in details in January, sql finds out that there are seven days off, but actually there are only five days off in January.
2. If check-in details in February, the match can not match February according to the field "from_date", because the employee's initial leave date is January, so this record can not be found, resulting in two days less leave days in February.
So, start to analyze what conditions should be used to query sql in order to ensure that the data is correct. All possible situations of employee leave:

Block 1-4 represents four possible leave spans for employees, all within the months we examined.
We found that:
The beginning of January must be greater than the end of the month, otherwise it was last month.
2. The end of the month must be greater than the beginning time, otherwise it will be next month.
If you look up sql according to the above two conditions, you can find out the data. There will be no February check mentioned above, but you can't find the data in February due to taking leave from the end of January.
Next is how to calculate the number of days off this month. GREATEST (beginning of month) - LEAST (end of month, end)

DATEDIFF(LEAST(a.to_date, STR_TO_DATE('2017-1-31', '%Y-%m-%d')),
        GREATEST(a.from_date, STR_TO_DATE('2017-1-1', '%Y-%m-%d'))) + 1 AS 'dayNum',

Okay, so I've written the complete sql is

SELECT
  DAY(IF(a.from_date < '2017-1-1', '2017-1-1', a.from_date)) d,
    DATEDIFF(LEAST(a.to_date, STR_TO_DATE('2017-1-31', '%Y-%m-%d')),
        GREATEST(a.from_date, STR_TO_DATE('2017-1-1', '%Y-%m-%d'))) + 1 AS 'dayNum',
 a.offwork_type
FROM
    tt_offwork a
WHERE
    a.sales_id = '2c90e0825905beaf015905c0092b00de'
AND a.from_date <= '2017-1-31' AND a.to_date >= '2017-1-1'
AND a.offwork_status = 'pass'
GROUP BY
    a.sales_id,
    DATE_FORMAT(a.from_date, '%Y-%m-%d')
ORDER BY
    DAY(a.from_date) ASC

Reflection

The key to demand is how to make conditional judgment and find out all the four situations I mentioned. It's a little complicated to deduce the conditions from the four cases. Then we can use junior high school mathematics knowledge to find the set conditions which are not in the range of months we look up and then take the opposite side. Obviously, the shadow part shown in the following figure is the unqualified query conditions. It is easy to see that the incompatibility is that the beginning of the month is greater than the end of the month, and the end of the month is less than the beginning of the month. So the condition is to take the opposite, the beginning of the month is less than or equal to the end time. The end of the month is greater than or equal to the beginning time.