Filter and Group Result Between Time Ranges

Question

timesetup table (Unnormalized), this table is for time conditioning that used for filtering result

| time_id | period_from | period_to  | session1_from | session1_to | session2_from | session2_to | session3_from | session3_to | session4_from | session4_to | session5_from | session5_to |
|---------|-------------|------------|---------------|-------------|---------------|-------------|---------------|-------------|---------------|-------------|---------------|-------------|
| 1       | 10/09/2015  | 11/09/2015 | 04:00:00      | 05:00:00    | 12:00:00      | 13:00:00    | 15:00:00      | 16:00:00    | 18:00:00      | 18:35:00    | 19:00:00      | 20:00:00    |
| 2       | 12/09/2015  | 13/09/2015 | 04:10:00      | 05:10:00    | 12:10:00      | 13:10:00    | 15:10:00      | 16:10:00    | 18:10:00      | 18:45:00    | 19:10:00      | 20:10:00    |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

Normalized Version :

dateperiode table :

| period_id | date_from  | date_to    |
|-----------|------------|------------|
| 1         | 10/09/2015 | 11/09/2015 |
| 2         | 12/09/2015 | 13/09/2015 |
|-------------------------------------|

sessionrange table :

| sessionrange_id | session   | session_from | session_to  |
|-----------------|-----------|--------------|-------------|
| 1               | 1         | 04:00:00     | 05:00:00    |
| 2               | 2         | 12:00:00     | 13:00:00    |
| 3               | 3         | 15:00:00     | 16:00:00    |
| 4               | 4         | 18:00:00     | 18:35:00    |
| 5               | 5         | 19:00:00     | 20:00:00    |
| 6               | 1         | 04:10:00     | 05:10:00    |
| 7               | 2         | 12:10:00     | 13:10:00    |
| 8               | 3         | 15:10:00     | 16:10:00    |
| 9               | 4         | 18:10:00     | 18:45:00    |
| 10              | 5         | 19:10:00     | 20:10:00    |
|-----------------|-----------|--------------|-------------|

timsetup table :

| period_id | sessionrange_id |
|-----------|-----------------|
| 1         | 1               |
| 1         | 2               |
| 1         | 3               |
| 1         | 4               |
| 1         | 5               |
| 2         | 6               |
| 2         | 7               |
| 2         | 8               |
| 2         | 9               |
| 2         | 10              |
|-----------|-----------------|

checktime table, this table is containing data from fingerprint scanner (tapping_time field)

|userid  | tapping_time         |
|--------|----------------------|
|234     | 10/09/2015 04:20:04  |
|234     | 10/09/2015 04:20:06  |
|234     | 10/09/2015 12:15:35  |
|234     | 10/09/2015 15:31:11  |
|234     | 10/09/2015 18:19:10  |
|234     | 10/09/2015 18:19:15  |
|234     | 10/09/2015 19:37:53  |
|234     | 11/09/2015 04:38:42  |
|234     | 11/09/2015 04:38:47  |
|234     | 11/09/2015 12:21:27  |
|234     | 11/09/2015 15:45:30  |
|234     | 11/09/2015 15:45:37  |
|234     | 11/09/2015 18:27:15  |
|234     | 11/09/2015 19:55:08  |
|234     | 11/09/2015 19:55:12  |
|234     | 12/09/2015 04:45:10  |
|234     | 12/09/2015 04:45:13  |
|234     | 12/09/2015 13:12:55  |
|234     | 12/09/2015 16:35:08  |
|234     | 12/09/2015 18:49:10  |
|234     | 12/09/2015 20:20:57  |
|234     | 13/09/2015 05:11:56  |
|234     | 13/09/2015 05:12:05  |
|234     | 13/09/2015 12:45:13  |
|234     | 13/09/2015 15:47:25  |
|234     | 13/09/2015 18:31:27  |
|234     | 13/09/2015 18:31:30  |
|234     | 13/09/2015 20:01:18  |
|-------------------------------|

I'm trying to create an Access query that groups the date/time into the periods in the timesetup table. tapping_time should have only one result per session (even when user tapping more than once at a time) based on sessions condition in timesetup table. Also users tap times outside of sessions (time between session_from and session_to) field should never counted or appear in result table.

Desired Result:

| userid | date       | tapping_on | session |
|--------|------------|------------|---------|
| 234    | 10/09/2015 | 04:20:04   | 1       |
| 234    | 10/09/2015 | 12:15:35   | 2       |
| 234    | 10/09/2015 | 15:31:11   | 3       |
| 234    | 10/09/2015 | 18:19:10   | 4       |
| 234    | 10/09/2015 | 19:37:53   | 5       |
| 234    | 11/09/2015 | 04:38:42   | 1       |
| 234    | 11/09/2015 | 12:21:27   | 2       |
| 234    | 11/09/2015 | 15:45:30   | 3       |
| 234    | 11/09/2015 | 18:27:15   | 4       |
| 234    | 11/09/2015 | 19:55:08   | 5       |
| 234    | 12/09/2015 | 04:45:10   | 1       |
| 234    | 12/09/2015 | 16:35:08   | 3       |
| 234    | 12/09/2015 | 18:49:10   | 4       |
| 234    | 13/09/2015 | 12:45:13   | 2       |
| 234    | 13/09/2015 | 15:47:25   | 3       |
| 234    | 13/09/2015 | 18:31:27   | 4       |
| 234    | 13/09/2015 | 20:01:18   | 5       |
|--------------------------------------------|

Can you add your sample data in text (instead of an image) or else a downloadable link with data? I'd think I can get this working for you but I don't want to re-type the data to test it. (You can include `monospace` data in your question just like code, by intending the lines with 4 spaces or by highlighting the text and clicking the `{ }` code block button. — ashleedawg, Mar 06 '18 at 04:22
have you tried grouping by hour and min - https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes — Dawood Awan, Mar 06 '18 at 06:46
If you'd normalize that timesetup table, I could answer this. Currently, my first step would be to normalize that, but it really should be stored as such — Erik A, Mar 06 '18 at 07:47
@ErikvonAsmuth is right. Normalize the _timesetup_ table, and the task should be easy to solve. — Gustav, Mar 06 '18 at 07:56

score 0 · Answer 1 · answered Mar 06 '18 at 10:15

You might have gone a little bit overboard on the normalization, since the relation between dateperiode and sessionrange looks like a one to many relationship to me, thus doesn't require a junction table.

With the following data, I believe the following query should work, but unfortunately I don't have a decent test setup at the ready:

SELECT userid, tDatepart As [date], Min(t.tapping_time) As tapping_on, session FROM 
    (
        SELECT session_from, session_to, date_from, date_to, session
        FROM timsetup i
        INNER JOIN dateperiode d ON i.period_id = d.period_id
        INNER JOIN sessionrange q ON t.sessionrange_id = q.sessionrange_id
    ) As s
INNER JOIN 
    (SELECT Int(tapping_time) As tDatepart, CDate(tapping_time - Int(tapping_time)) As tTimepart, userid, tapping_time
    FROM checktime) t
ON (t.tDatePart >= s.date_from AND t.tDatePart <= s.date_to AND t.tTimePart >= s.session_from AND t.tTimePart <= s.session_to)
GROUP BY userid, tDatepart, session

Filter and Group Result Between Time Ranges

1 Answers1

Linked