Forum Discussion
Access control reports
Would dearly appreciate a set of fresh eyes. I've been tasked with building a report that provides some insights as based on data from an access control db (sample data attached) The idea is to prov...
Hi jacosmit ,
What is your desired reuslt for your formula? Could you please post your desired result if possible?
Regards,
Daniel He
Hi Daniel
Something similar to the attached, per division.
| MONTH | DIVISION | USER_ID | TIME ON SITE | RANKING |
| Apr-19 | Division 01 | Joe Soap | 198:13:02 | 1 |
| Apr-19 | Division 01 | Frank Noble | 198:11:00 | 2 |
| Apr-19 | Division 01 | Stella Lange | 195:05:12 | 3 |
| Apr-19 | Division 01 | Frank Niemand | 185:00:23 | 4 |
| Apr-19 | Division 01 | Terry Crews | 156:00:15 | 5 |
| Apr-19 | Division 01 | Tom Cruise | 154:00:14 | 6 |
| Apr-19 | Division 01 | Rob Nelson | 150:10:15 | 7 |
| Apr-19 | Division 01 | Ted Mosley | 149:02:45 | 8 |
| Apr-19 | Division 01 | Sean Els | 148:00:15 | 9 |
| Apr-19 | Division 01 | Tom Brady | 147:15:32 | 10 |
Hi jacosmit ,
So could you mean the TIME ON SITE and RANKING column is your desired column? How the logic about the TIME ON SITE column?
Regards,
Daniel He
Hi Daniel
Yes, TIME ON SITE and RANKING would be what is required as output.
Logic with regards to the TIME ON SITE...... Conceptually I wanted to work out the earliest and latest time per person per day. Then subtract latest with earliest to get to the amount of time spent on site per day. Then add all the daily totals to get to the monthly total.
Any ideas?