Проверка диапазона дат MySQL перед вставкой в MySQL
Это не повторяющийся вопрос, мой запрос, который я имею в своей демонстрационной программе скрипки, обрабатывает перекрывающуюся дату из моей базы данных. Что мне нужно, так это способ выполнить запрос в диапазоне дат $ startdate и $ enddate перед вставкой дат в MySQL. По сути, я хочу проверить, достаточно ли у каждого работника выходных дней в эти годы.
Я пытаюсь проверить, в каком году каждая дата в диапазоне дат, прежде чем вставить в MYSQL. Каждый год для каждого сотрудника начинается в другой день
Eg. jon started on 2013-10-10 so his year is
2013-10-10 to 2014-10-10
2014-10-10 to 2015-10-10
В данный момент мой запрос проверяет дату, когда они уже были вставлены в MYSQL (h.date), e.startdate работает каждый год с момента их запуска. Кому я проверяю каждую дату между $ startdate, $ enddate, прежде чем вставлять даты.
Я настроил демо Fiddle, которое работает, когда даты уже в базе данных
$startdate ='2015-10-05';
$enddate ='2015-10-20';
SELECT
e.name AS Employee,
CONCAT(
CEIL(DATEDIFF(h.date, e.startdate)/365),
' (',
DATE_ADD(e.startdate, INTERVAL FLOOR(DATEDIFF(h.date, e.startdate)/365) YEAR), ' to ',
DATE_ADD(e.startdate, INTERVAL CEIL(DATEDIFF(h.date, e.startdate)/365) YEAR),
')'
) as Year,
COUNT(h.date) AS Holidays_Taken,
SUM(h.hours) AS Hours
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year
Я надеюсь, что кто-то может помочь Спасибо
Спасибо, я забыл упомянуть, что часы для каждого дня, в течение которого сотрудник работает, отличаются, поэтому я использовал этот запрос, чтобы вставить часы для каждого дня в отпуск, например. Джон (empId 1) работает 10 часов в понедельник, 6 во вторник и т. д.
$sql= "select empId,
'".$holidayStart."' as date,
case dayname('".$holidayStart."')
when 'Sunday' then Sun
when 'Monday' then Mon
when 'Tuesday' then Tue
when 'Wednesday' then Wed
when 'Thursday' then Thu
when 'Friday' then Fri
when 'Saturday' then Sat
else 0 end as hours
from employees where empId = '".$employee."'" ;
$result = $mysqli->query($sql);
тогда база данных
id empid Mon Tue Wed Thu Fri Sat Sun
102 1 10 6 4 10 10 0 0
Решение
Не уверен, правильно ли я понял проблему (я не смог найти знак вопроса :-)). Надеюсь, что следующий запрос поможет вам в любом случае:
SET @hoursPerDay := 8;
SET @startdate := DATE('2015-10-05');
SET @enddate := DATE('2015-10-20');
SET @empid := 1;
SELECT years.*, holidays.*,
GREATEST(holidays.startdate, years.yearStartDay) AS holidaysStartDay,
LEAST(holidays.enddate, years.yearEndDay) AS holidaysEndDay
FROM (
SELECT e.name,
TIMESTAMPDIFF(YEAR, e.startdate, h.date) AS yearNr,
DATE_ADD(
e.startdate, INTERVAL TIMESTAMPDIFF(YEAR, e.startdate, h.date) YEAR
) AS yearStartDay,
DATE_ADD(
DATE_ADD(e.startdate,
INTERVAL TIMESTAMPDIFF(YEAR, e.startdate, h.date) +1 YEAR
), INTERVAL -1 DAY
) AS yearEndDay,
e.holidays,
SUM(h.hours) AS hoursTaken,
e.holidays * @hoursPerDay - SUM(h.hours) AS hoursLeft
FROM (
SELECT empid, DATE, hours
FROM holidays_taken h
-- need union all if didnt have holidays in that year yet
UNION ALL SELECT @empid AS empid, @startdate AS DATE, 0 AS hours
UNION ALL SELECT @empid AS empid, @enddate AS DATE, 0 AS hours
) h
JOIN employees e ON e.id = h.empid
WHERE h.empid = @empid /* @todo replace with correct employeeID */
GROUP BY e.id, e.name, e.holidays, yearStartDay, yearEndDay
) years
JOIN (SELECT @startdate AS startdate, @enddate AS enddate) holidays /* @todo replace with $startdate and $enddate */
WHERE holidays.startdate BETWEEN years.yearStartDay AND years.yearEndDay
OR holidays.enddate BETWEEN years.yearStartDay AND years.yearEndDay
http://sqlfiddle.com/#!9/10a1d/15
Вам не нужно использовать переменные сеанса. Просто замените их значениями в PHP.
Удалите все во внешнем выборе, который вам не нужен.
Итак, что делает этот запрос:
Внутренний выбор почти такой же, как ваш запрос, но
- Это может справиться со случаем, что год сотрудников начинается 29 февраля
-
UNION ALLpart создает новый год, если у работника еще нет выходных в этом году. - Последний день года не совпадает с первым днем следующего года.
Затем он ищет пересечения между новыми определенными праздниками и всеми годами сотрудников. Затем вычисляется диапазон дат новых выходных для каждого работника.
Чтобы проверить больше случаев, замените определения переменных сеанса на:
-- sample 2: first time taking holidays in a year
SET @startdate := DATE('2017-10-05');
SET @enddate := DATE('2017-10-20');
SET @empid := 1;
-- sample 3: first year of employee who starts on Feb. 29th
SET @startdate := DATE('2016-10-05');
SET @enddate := DATE('2016-10-20');
SET @empid := 2;
-- sample 4: second year of employee who starts on Feb. 29th
SET @startdate := DATE('2017-10-05');
SET @enddate := DATE('2017-10-20');
SET @empid := 2;
Другие решения
Других решений пока нет …