CODEWITHSUNDEEP
sqlms-access
codefighter
codefighter

Reputation: 19

Grouping Result By Time Range

  1. dateperiod table
|period_id | date_from  | date_to       |
|----------|------------|---------------|
| 1        | 16/11/2018 | 17/11/2018    |
|---------------------------------------|
  1. sessionrange table
| sessionrange_id | session | session_from | session_to |
|-----------------|---------|--------------|------------|
| 1               | A       | 04:00:00     | 05:10:00   |
| 2               | B       | 12:00:00     | 12:45:00   |
| 3               | C       | 15:00:00     | 15:30:00   |
| 4               | D       | 18:00:00     | 18:30:00   |
| 5               | E       | 19:00:00     | 19:45:00   |
--------------------------------------------------------
  1. timsetup table
| period_id | sessionrange_id |
|-----------|-----------------|
| 1         | 1               |
| 1         | 2               |
| 1         | 3               |
| 1         | 4               |
| 1         | 5               |
------------------------------
  1. checktime table, containing data from fingerprint scanner
|userid   | tapping_time         |
|---------|----------------------|
|2120     | 16/11/2018 04:20:04  | 
|2120     | 16/11/2018 12:26:06  |
|2120     | 16/11/2018 12:26:10  |
|2120     | 16/11/2018 15:35:21  |
|2120     | 16/11/2018 18:29:11  | 
|2120     | 16/11/2018 19:40:10  |
|2120     | 17/11/2018 05:05:15  |
|2120     | 17/11/2018 12:11:53  |
|2120     | 17/11/2015 15:24:42  |
|2121     | 16/11/2015 04:18:47  |
|2121     | 16/11/2015 12:21:27  |
|2121     | 16/11/2015 15:45:30  |
|2121     | 16/11/2015 15:45:38  |
|2121     | 16/11/2015 18:27:37  |
|2121     | 16/11/2015 19:32:15  |
|2121     | 17/11/2015 05:09:08  |
|2121     | 17/11/2015 12:24:08  |
|2121     | 17/11/2015 15:13:08  |
|2121     | 17/11/2015 19:32:08  |
|2121     | 17/11/2015 19:32:15  |

current query already filtering from more than one tapping_time on a user on a date by session and filtering from checktime not in sessionrange

SELECT userid, Format(datevalue, 'yyyy-mm-dd') AS d, Format(TimeValue(Min(t.CHECKTIME))) As timevalue, session
FROM (
    SELECT session_from, session_to, date_from, date_to, session 
    FROM ((timesetup i LEFT JOIN dateperiod d ON i.period_id = d.period_id) 
        LEFT JOIN sessionrange q ON i.sessionrange_id = q.sessionrange_id)
    ) As s 
INNER JOIN (SELECT Format(DateValue(CHECKTIME)) As datevalue, Format(TimeValue(CHECKTIME)) As tapping, u.userid, CHECKTIME 
    FROM CHECKINOUT c 
    LEFT JOIN USERINFO u ON c.userid = u.userid 
    WHERE (Format(DateValue(c.CHECKTIME), 'yyyy-mm-dd')  BETWEEN '$from' AND '$to')) t ON ((t.datevalue BETWEEN s.date_from AND s.date_to) AND (t.tapping BETWEEN s.session_from AND s.session_to)) 
GROUP BY userid, datevalue, session, u.Badgenumber 
ORDER BY userid, datevalue, Format(TimeValue(Min(t.CHECKTIME)))

result from above query :

| userid  | d          | tapping_on | session |
|---------|------------|------------|---------|
| 2120    | 16/11/2018 | 04:20:04   | A       |
| 2120    | 16/11/2018 | 12:26:06   | B       |
| 2120    | 16/11/2018 | 18:29:11   | D       |
| 2120    | 16/11/2018 | 19:40:10   | E       |
| 2120    | 16/11/2018 | 05:05:15   | A       |
| 2120    | 17/11/2018 | 12:11:53   | B       |
| 2120    | 17/11/2018 | 15:24:42   | C       |
| 2121    | 16/11/2015 | 04:18:47   | A       |
| 2121    | 16/11/2015 | 12:21:27   | B       |
| 2121    | 16/11/2015 | 18:27:37   | D       |
| 2121    | 16/11/2015 | 19:32:15   | E       |
| 2121    | 17/11/2015 | 05:09:08   | A       |
| 2121    | 17/11/2015 | 12:24:08   | B       |
| 2121    | 17/11/2015 | 15:13:08   | C       |
| 2121    | 17/11/2015 | 19:32:08   | E       |
|---------------------------------------------|

but i need different result of group

Desired result

------------------------------------------------------------------------------------
| userid | date         | A         | B         | C         | D         | E
|--------|--------------|-----------|-----------|-----------|-----------|---------- |
| 2120   | 16/10/2018   | 04:20:04  | 12:26:06  | NULL      | 18:29:11  | 19:40:10  |
| 2120   | 17/10/2018   | 05:05:15  | 12:11:53  | 15:24:42  | NULL      | NULL      |
| 2121   | 16/10/2018   | 04:18:47  | 12:21:27  | NULL      | 18:27:37  | 19:32:15  |
| 2121   | 17/10/2018   | 05:09:08  | 12:24:08  | 15:13:08  | NULL      | 19:32:08  |
------------------------------------------------------------------------------------

filtering is same, but it's different way in grouping (by userid, date, session). how to customize the current query to produce desired result in Ms.Access?

Upvotes: 2

Views: 33

Answers (1)

Gutzy
Gutzy

Reputation: 92

One way would be to do 5 case when statements in your select. You can do something like

(Case When session_from > 04:00:00 and session_to < 05:10:00 Then tapping_on End) A
(Case When session_from > 05:00:00 and session_to < 06:10:00 Then tapping_on End) B

etc.

You essentially make the columns and then display whatever value you want in them, tapping_on in this case.

Upvotes: 1

Related Questions