As you can see down below, I got this query that gets the average of how long it takes to approve a number of certain forms. I'm filtering out by giving a date range, and the date range needs to include only business days (Mon-Fri) using the Calendar table.
SELECT
fg2.form_descr AS 'form_description',
CONCAT(
FLOOR(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))) / 24), ' days ',
MOD(HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed )))), 24), ' hours ',
MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, submitted, viewed)))), ' minutes'
) AS 'Average Approval Time',
COUNT(user_forms.form_id) AS ' number_of_forms'
FROM
forms.user_forms,
forms.forms,
forms.form_groups fg,
forms.form_groups fg2
WHERE
user_forms.form_id = forms.form_id
AND forms.form_group = fg.form_group_id
AND fg.approver_group = fg2.form_group_id
AND viewed > submitted
AND submitted BETWEEN '2015-01-01' AND '2015-06-30'
GROUP BY
fg.approver_group
The results is something like:
form_description Average Approval Time number_of_forms
'Log In' '1 days 2 hours 04 minutes' '35'
'Programming' '2 days 5 hours 22 minutes' '100'
...and so on
Now, I have a table called Calendar which consists of two columns. One is date, and the other is is_holiday with no primary id and/or foreign key. The Calendar table is nothing but a list of dates that tells you if it is a holiday or not. Example:
Date is_holiday
'2014-01-01' '0'
'2014-01-02' '0'
'2014-01-03' '0'
and so on...
SELECT date
FROM calendar
WHERE DAYOFWEEK(date) NOT IN (1,7)
AND date BETWEEN '2015-01-01' AND '2015-06-30'
AND is_holiday = 0
How can I merger these 2 queries together? I have tried using two subqueries in the FROM clause, but I can't seem to get this. And yes, I did tried looking around, but I can't see to get this right.