Finding Consecutive Workdays with Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, April 18, 2008

Finding Consecutive Workdays with Query

Introduction.

How do we determine whether the date values within a certain date range are consecutive or intermittent? To bring some clarity to 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 weekend breaks they will be rewarded with special incentives besides their daily wages.

The employees joined to 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.

A Table with Sample Data.

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 the workdate fields combined as Primary Key to avoid entry of duplicate date-values.

Solution

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 a break for eligibility for special incentives.
Query1 SQL :
```
SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 Min([workdate])+6 AS Day7Date
FROM Table1
GROUP BY Table1.emp;
```
Query2: Join Table1 with Query1 on the emp field and select records using the Day7Date as a 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]));
```
Query3: Create a Total Query using Query2 as a 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 the 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 a large number of records for various periods, and the record pertains to a specific period needs to be subjected to this method, then data filtering must be done. Create a Query using Table1 and use the 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 a 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.

Do you have different ideas? Please share them with me by posting them in the Comments Section.

LEARN MS-ACCESS TIPS AND TRICKS

Friday, April 18, 2008