Firstly, some of your dates are "date" type, and others must be "datetime" type. This is important for joins: when comparing "date" and "datetime" type fields, you may need to apply the MySQL DATE() function to the "datetime" field to enable direct comparison. (I'm assuming you're using MySQL: there are some small differences between SQL systems, in calendar-date types, ranges and functions: you will need to be wary of the ranges of date-calculation function results too, as these can sometimes have practical limits.) The following examples use subqueries: some versions of MySQL may support these better than others.
I've created queries of this sort before, and I think there are basically two approaches to solving this problem:
- Create a lookup table of all working-days since your business started; and left-join this (with WHERE condition: right-side NULL) with tbl_attendance. This approach would be simple to design but very sub-optimal in terms of computational efficiency, and it's not necessary to take this approach…
- Assess the total number of calendar days when your staff were working, with tbl_attendance. Assess the total number of calendar days when your staff should have been working, by assessing the sums/differences of date-ranges. You are going to be using MySQL functions like: IF(), DATEDIFF(), DATE(), WEEKDAY() — the form of your employment contracts will determine what form your reports should be in, which will determine the precise form of your SQL queries.
It makes sense to have official public holidays listed by date (governments can create/change/cancel statutory public holidays almost any time they like); but on the other hand, to have leave-periods and attendance-periods listed by date-range. So I can see the rationale behind your table/field design (which looks good); but you should be aware that your solution (quite properly) has elements of design approach 1, and also of design approach 2.
Before designing/ testing your SQL queries, you will need to lay out some ground rules, which will be supported by validation rules/queries/triggers:
Make sure that date-ranges of leave-periods and attendance-periods do not, and cannot, overlap with the date-ranges of other leave-periods or attendance-periods. Also, validate/ensure that end_dates don't/cannot precede start_dates (which would violate the design assumptions of what follows): to support/simplify this design assumption, I will assume that all of your date or datetime fields are recorded in UTC/GMT (Zulu time), to prevent timezone transitions from mucking up our assumptions/calculations (data will need to be converted into UTC as entered, and into local times where relevant, when extracted). It's very possible that there are bugs (errors/omissions) in my suggestions (following); as I haven't tested this specific code (though I have written code like it).
First, let's write a query showing the total number of days attended. I presume we don't care here, how many hours the staff member attended for during any given day (we only care whether they attended that day, or not). For simplicity, I am going to ignore the possibility that your staff do day/night shift work (such that a shift/attendance can start on one day and continue to the next), and ignore the possibility that emergency deployment might involve attendance for more than 24 hours. I'm also presuming that emergency attendance never occurs on a Saturday, or on another excluded day. (Otherwise, the query will be a little more complex). I suggest:
SELECT staff_id,COUNT(DATE(checked_in)) FROM tbl_attendance GROUP BY staff_id,DATE(checked_in) WHERE checked_in>:start_date AND checked_in<:end_date;
I'm using a parameterized format here for your stand_date, end_date conditions; which might be used as follows with PHP/PDO:
$pdo=//You need to create a PDO object: see the manual for more information…
$q=$pdo->prepare(SELECT staff_id,COUNT(DATE(checked_in)) AS attended_start_days FROM tbl_attendance GROUP BY staff_id,DATE(checked_in) WHERE checked_in>=:start_date AND checked_in<:end_date;);
$q->bindValue(':start_date',$start_date,\PDO::PARAM_STR);
$q->bindValue(':end_date',$start_date,\PDO::PARAM_STR);
$q->execute();
$staff_attended_start_days=$q->fetchAll(\PDO::FETCH_OBJ);
//etc.
Note that wherever you see ":start_date" or ":end_date" in SQL, these are the specified date-range parameters of your query. Wherever you see "start_date" or "end_date" (no colon) in a query, these are from your database fields/records!
Next, we need the total number of days official public holiday (presuming public holidays are not allowed to be recorded in the database when they fall on Saturdays, or on other excluded days) — I presume here that your date-ranges are inclusive of start_date, and exclusive of end_date (an inequality format which makes certain calculations simpler):
SELECT COUNT(holiday_date) FROM tbl_holiday WHERE holiday_date>=:start_date AND holiday_date<end_date;
To calculate the total number of days of leave, I could (and perhaps should) use DATEDIFF; but since you have a separate database field called "no_of_days", I'm going to presume that information in this column is accurate/reliable, and keep things simple (don't forget that leave periods mustn't overlap or summing them up would produce incorrect results): I'm also going to temporarily cheat and pretend that periods of leave cannot overlap the periods you're querying (this assumption is obviously wrong):
SELECT staff_id,SUM(no_of_days) FROM tbl_leave GROUP BY staff_id WHERE start_date>=:start_date AND end_date<:end_date;
Now to fix the obviously incorrect assumption we just made: we're potentially looking at the parts of leave-periods which fall within the specified date-range. Conceptually, the simplest way to do this is with a subquery (where we actually have to ignore your field, "no_of_days", and calculate this from scratch again, obviating the need for this field, for these purposes):
SELECT staff_id,SUM(DATEDIFF(bounded_end_date,bounded_start_date))+1
FROM (SELECT staff_id,IF(start_date>:start_date,start_date,:start_date) AS bounded_start_date,IF(end_date<:end_date,end_date,:end_date) AS bounded_end_date FROM tbl_leave WHERE end_date>:start_date AND start_date<:end_date) AS qry_bounded_leave
GROUP BY staff_id;
The +1 part is because if you have a period of leave starting on 5th January and ending on 5th January, this period is 1 day long (but the difference is 0 days!) The period of leave is always 1 day longer than the difference between start-date and end-date.
WRAPPING IT ALL UP:
Now, given the above assumptions; the number of days absent without leave within a specified time-period equals the sum of:
- the number of calendar-days within a specified time-period
minus the sum of:
- number of days actually attended (see above)
- number of days of holiday within the specified time-period (see above)
- number of days of leave within the specified time-period, minus (excluding) the number of days of holiday within these periods of leave (other exclusions may need to be made, if some of my "non-overlapping" assumptions are potentially violated).
The number of calendar-days within a specified time-period is given by:
SELECT DATEDIFF(:end_date,:start_date)+1 AS days_in_range;
— You can do this in MySQL, but not in some other toy RDBMS systems like MS Access (where you have to include a dummy "FROM" clause).
Next, we'll adapt our query for periods of leave, to exclude holidays (this requires a JOIN in the subquery):
SELECT staff_id,SUM(DATEDIFF(bounded_end_date,bounded_start_date))+1-SUM(holiday_days) AS leave_days_excluding_holidays
FROM
(SELECT
staff_id,
IF(start_date>:start_date,start_date,:start_date) AS bounded_start_date,
IF(end_date<:end_date,end_date,:end_date) AS bounded_end_date,
COUNT(holiday_date) AS holiday_days
FROM tbl_leave INNER JOIN tbl_holiday ON (holiday_date>=start_date AND holiday_date>=:start_date AND holiday_date<end_date AND holiday_date<=:end_date)
GROUP BY staff_id,start_date,end_date
WHERE end_date>:start_date AND start_date<:end_date
) AS qry_bounded_leave
GROUP BY staff_id;
We're going to need to go a little further, and include in each of our queries, SQL code to take account of the Saturdays and any other excluded weekdays. As long as start-dates and end-dates never actually fall on excluded weekdays, this is actually a fairly simple calculation, where the number of weekends between start_date and end_date is given by something like this:
DATEDIFF(end_date,start_date) DIV 7 + IF(WEEKDAY(end_date)<WEEKDAY(start_date),1,0)
"DIV" here is the MySQL integer division operator, which rounds down (the operator may vary, if you are using something other than MySQL). You basically need to go over the calculation detailed under my heading, "WRAPPING IT ALL UP"; thinking about how to exclude the influence of Saturdays, and any other excluded weekdays. (I don't have time to illustrate further, how to do so at this point; but I hope this has at least given you a starting point for your solution!)
As a final warning: there is a potential conflict between my assumption that all dates/datetimes are in UTC/GMT/Zulu, and my assumption that nobody works night-shifts (imagine the case where, due to time-zone issues, your typical attendance start-time falls around midnight when expressed in UTC/GMT… This could be problematic for your calculation. So you might need to go further here, and unwind that assumption: make the queries smart enough to take account of that!) In order to suitably unwind these assumptions, and make the queries smart enough to cover all the edge-cases; it may be necessary to know more about your employment contracts, and the details of the reports you want: what exactly does it mean, to attend/ be absent? What standards are applied? Conditions on the checked field, etc.? (If you are designing these queries for a small-to-medium sized business, with daytime-only operations spanning only a few timezones in close proximity with each other, and you don't care to accurately query time worked during shifts which span transitions in daylight savings time; then you might consider UTC/GMT to be technical overkill and just use local times in your database for simplicity!)
