How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into this problem and solution I will present an example below.

Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking week-end breaks they will be rewarded with special incentives besides their daily wages.

The employees joined for work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.

After completion of the Project the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.

I know it sounds like a Question in an Examination Hall. But, we are faced with difficult Questions very often, while considering data processing tasks and most of them we have to ask ourselves to find solutions.

This is not that hard to find. With three simple Queries we can solve this problem. Interested? Read on.

First we need a table with some sample data. Create a Table with the name Table1 with the following Table Structure and enter sample data into it from the image given below. You may add more employee Codes and Date Values on similar lines for more accurate testing. Define emp and workdate fields combined as Primary Key to avoid entry of duplicate date values.

Solution

1. Query1: Create a Total Query Grouped by emp, take the minimum value from workdate to find the work-start date of each employee and create a new column adding 6 days to the work-start date to calculate and store the 7th Day Date of work for each employee. They must work from the work-start date to this date without break for eligibility of special incentive.

    

   Query1 SQL :

    

   SELECT Table1.emp,
    Min(Table1.workdate) AS min_date,
    Min([workdate])+6 AS Day7Date
   FROM Table1
   GROUP BY Table1.emp;
   

 

2. Query2: Join Table1 with Query1 on emp field and select records using the Day7Date as criterion on workdate that falls on or before the Day7Date.

    

   Query2 SQL :

    

   SELECT Table1.*
   FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
   WHERE (((Table1.workdate)<= [Day7Date]));
   

 

3. Query3: Create a Total Query using Query2 as source. Group on emp field, take Count of workdate and filter the output with workdate Count =7.

 

Query3 SQL:

SELECT Query2.emp,
 Count(Query2.workdate) AS [count]
FROM Query2
GROUP BY Query2.emp
HAVING (((Count(Query2.workdate))=7));

Note: The continuity of dates is checked at employee level based on their work-start dates. Not necessary that all of them should work within the same month or start work on a specific Date.

But when the data file contains large amount of records for various periods and records pertains to a specific period need to be subjected to this method then data filtering must be done. Create a Query using Table1 and use Start-Date and End-Date range as criteria to pick records within that period. Use that Query as source data for Query1 and Query2 above, instead of using Table1 directly.

As the saying goes – "There is always more than one way to skin a Cat" (did I say it correctly), we can take a different approach to solve this problem with slight deviation to the definition of the above Queries. The Query SQL strings of this new method are also given below. You may copy them and create Queries to try them out too.

Query 1_1 SQL

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 [min_date]+0 AS day1,
 [min_date]+1 AS day2,
 [min_date]+2 AS day3,
 [min_date]+3 AS day4,
 [min_date]+4 AS day5,
 [min_date]+5 AS day6,
 [min_date]+6 AS day7
FROM Table1
GROUP BY Table1.emp;

Query 2_1 SQL

SELECT Table1.emp,
 Table1.workdate
FROM Query1_1 INNER JOIN Table1 ON Query1_1.emp = Table1.emp
WHERE (((Table1.workdate) In ([day1],[day2],[day3],[day4],[day5],[day6],[day7])));

Query 3_1 SQL

SELECT Query2_1.emp,
 Count(Query2_1.workdate) AS CountOfworkdate
FROM Query2_1
GROUP BY Query2_1.emp
HAVING (((Count([Query1_2].[workdate]))=7));

I have not tested these methods extensively to eliminate side effects, if any. You may use them at your own risk.

You have different ideas? Please share it with me by posting them into the Comments Section.