Quite challenging this @Prochu1991 but I think I manage to construct a query for you.
EDIT: The query below have a few issue in some condition. Therefore, I don't recommend to use it but I leave it here in case you can do something about it:
-- Query 6: Final calculation add SUM of total leave time GROUP BY ID_LEAVE,ID_WORKER.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
SEC_TO_TIME(SUM(TIME_TO_SEC(leave_TIME))) AS 'LEAVE TIME'
FROM (
Query 5: Calculating leave time on each date only if VALID_LEAVE_DATES=1.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
IF(VALID_LEAVE_DATES=1,SEC_TO_TIME(TIME_TO_SEC(TIME(end_date))-TIME_TO_SEC(TIME(begin_date))),0) AS 'LEAVE_TIME'
FROM (
-- Query 4: Add checking' if any of the dates are in the weekend, it will be set as 0.
SELECT leave_dates,
IF(DAYNAME(LEAVE_DATES) IN ('Saturday','Sunday'),0,1) AS 'VALID_LEAVE_DATES',
ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE FROM (
-- Query 3: In this part, the main reason is to create dates between BEGIN_DATE and END_DATE.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
-- concatenating extracted year-month with days generated from Query 1.
CONCAT_WS('-',DATE_FORMAT(BEGIN_DATE, '%Y-%m'),LPAD(days,2,0)) AS 'LEAVE_DATES' FROM
-- Query 1: This part is creating day value directly from query. If you run this individually, you'll get a day value from 0 to 39.
(SELECT 1 AS 'id'
a+b AS 'days' FROM
(SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) dd
-- Query 1 end here.
) ee
LEFT JOIN
-- Query 2: This is your original query. I removed the SUM in select.
(SELECT 1 AS 'id',
leave.ID_LEAVE,
leave.ID_WORKER,
leave.BEGIN_DATE,
leave.END_DATE
FROM leave GROUP BY leave.ID_LEAVE) cd
-- Query 2 end here.
ON ee.id=cd.id
WHERE days BETWEEN DAY(BEGIN_DATE) AND DAY(END_DATE) -- `WHERE` condition only take date value between BEGIN_DATE and END_DATE from Query 2.
ORDER BY LEAVE_DATES) LCALC -- Query 3 end here.
) vvv GROUP BY ID_LEAVE,LEAVE_DATES -- Query 4 end here.
) tuv -- Query 5 end here.
GROUP BY ID_LEAVE,ID_WORKER; -- Query 6 end here.
Hopefully you can understand my explanation. I'll still be working with this query and see if there's a way to reduce some processes (less queries).
EDIT 2:
Ok, I've been doing this @Prochu1991:
SELECT *,IF(valid_leave_days=0, TIMEDIFF(end_date,begin_date),
-- Assuming that normal working hours is '08:00:00'. If more, you just need to change here.
SEC_TO_TIME(TIME_TO_SEC('08:00:00')*Valid_leave_days)) AS 'Total_leave_time'
-- So I convert 8 hours to seconds multiply with valid_leave_days calculated and convert it back to time. I think you understand this part.
FROM
(SELECT *,
-- This part where the CASE start is actually just determining how many leave days per person.
-- Then minus with the total of weekend per week (sat & sun = 2 days).
CASE
WHEN datedif<6 THEN datedif --if leave days are less than 6 days, it return datedif.
WHEN datedif=6 THEN datedif-1 --if leave days=6, datedif-1 day > because in any day you start you will surely get one weekend.
WHEN datedif BETWEEN 7 AND 12 THEN datedif-2 --if leave days between 7 and 12, datedif-2.
WHEN datedif=13 THEN datedif-3 -- from here you should get the idea.
WHEN datedif BETWEEN 14 AND 19 THEN datedif-4
WHEN datedif=20 THEN datedif-5
WHEN datedif BETWEEN 21 AND 26 THEN datedif-6
WHEN datedif=27 THEN datedif-7
WHEN datedif BETWEEN 28 AND 34 THEN datedif-8
-- Note that this is only up to 34 days. if you want to add more days, just make sure the calculation is correct.
END AS 'Valid_leave_days'
FROM
(SELECT *,DATEDIFF(end_date,begin_date) AS 'datedif' FROM LEAVE) a) b;
