时间范围之间的过滤器和组结果 [英] Filter and Group Result Between Time Ranges
问题描述
-
timesetup
| 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 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
标准化版本：
dateperiode 表格：
dateperiode table :
| period_id | date_from | date_to |
|-----------|------------|------------|
| 1 | 10/09/2015 | 11/09/2015 |
| 2 | 12/09/2015 | 13/09/2015 |
|-------------------------------------|
sessionrange 表格：
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 表格：
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表，此表包含指纹扫描器的数据（tapping_time字段）
|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 |
|-------------------------------|
我试图创建一个Access查询，将日期/时间分组到 timesetup 表中。另外，用户点击会话之外的时间（ session_from 和 session_to ）字段之间的时间间隔强>从不计算或出现在结果表中。
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 |
|--------------------------------------------|
推荐答案
您可能在归一化方面有点过分，因为dateperiode和sessionrange之间的关系看起来像是一对多关系，所以不需要联结表。
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.
有了以下数据，我相信以下查询应该可以工作，但不幸的是我没有准备好体面的测试设置：
$ b
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
这篇关于时间范围之间的过滤器和组结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
