topic Re: Help with calculating time ??? in DAX Commands and Tips

Help with calculating time ???

rf27 — Fri, 03 Jan 2020 20:01:18 GMT

I'm very new to this.. I tried a couple of things...

Re: Help with calculating time ???

kentyler — Sat, 04 Jan 2020 04:13:29 GMT

DAX does not have a built in concept like "previous row".

To compare a time in one row to a time in the previous row you need to do 3 things

Create an index for the table in power query, using "add column"

Store the index for the current row in a variable

Use lookupvalue() to get the value you want from the previous row by looking for the index -1

In your case you would also have to check that the day in the previous row was the same day. If it was you would have the 2 datetime values you needed to use DATEDIFF() since it can compare by minute if you like.

If you need help with the DAX code, paste in a copy of your data (rather than using an image) and I'll copy and paste it into a power bi file and write some sample code for you.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
Store all your intermediate calculations in VARs when you’re writing measures. You can return the VARs to check on your steps along the way.

Re: Help with calculating time ???

Jimmy801 — Sat, 04 Jan 2020 18:23:01 GMT

Hello rf27

What should the expected output be?

These are time, and having total of times doesn't seem to be of any sense to me.

To me this seems more a topic for power query rather then DAX

All the best

Jimmy

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 13:02:00 GMT

Hi Jimmy,

Thank you for your reply.

What I'm looking for is two things,

Total hours worked per date

Then Total hours per days worked .

Ex: day one- 6 hrs, day 2- 8hrs, day 3- 10hrs, and day 4- 8hrs

Total 32 hours

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 13:03:17 GMT

Hi Ken,

Thanks for your reply... I'll give it a go..

Richard

Re: Help with calculating time ???

Jimmy801 — Mon, 06 Jan 2020 15:04:43 GMT

Hello rf27

how the start and how the end time is defined? A was not able to identify it. What I did already mention is that in one day i saw 3 times. So how to identify what is the start and what is the end date?

What would also be important to now, is what exactly is you expected output. I can think of transforming date (poviting start and end) and with this a easy measure in DAX could be created.

Jimmy

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 15:24:25 GMT

Hey Jimmy,

The first transaction , and the last transaction of the day counts for the time duration... I cut out the important piece.. I have an 1 and 0 the represents my first in and last transaction. 1 = IN 0= OUT I'm looking to tabulate the IN & OUT of every day... I hope this brings clarity to my questions..

Re: Help with calculating time ???

Jimmy801 — Mon, 06 Jan 2020 15:45:32 GMT

Hello rf27

are they connected in any way? meaning.. how to belong the first 1 with the first 0?

Possible logics to create your solution could be like this:

- split always every second row

- use the date (if the table exactly contains 2 rows per otherwise - otherwise logic would fail)

Another remark.. so you are doing already a trasforming of your data, keeping only first and last time of a day?

Could you please post a real table, that I can use it to build your solution

Jimmy

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 17:27:00 GMT

Hey Jimmy,

Yes, I have two unique identifiers.

empid, cardnum this connects me to the 1&0 transactions per person.. I also have a filter to get the 1&0 based on the reader desc.

Let me clean up the data set it's rather large

Richard

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 18:26:28 GMT

events.event_time_utc event.evdescr reader.readerdesc empid events.cardnum buenos_aires DATE TIME badgetyp.r_name in_out 12/3/19 8:21 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 24808 20600609 12/3/19 5:21 PM 12/03/19 17:21:42 Contractor 0 12/3/19 10:38 PM Access Granted 1493 ROMULUS T/S 7 EAST OUT -C23-P2-R2 26792 20302725 12/3/19 7:38 PM 12/03/19 19:38:52 Contractor 0 12/3/19 1:07 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/3/19 10:07 AM 12/03/19 10:07:08 Contractor 1 12/3/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/3/19 5:28 PM 12/03/19 17:28:14 Contractor 0 12/3/19 12:28 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 27983 20632009 12/3/19 9:28 AM 12/03/19 9:28:23 Contractor 0 12/3/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 28038 315970 12/3/19 9:28 AM 12/03/19 9:28:20 Contractor 0 12/3/19 8:29 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28064 314473 12/3/19 5:29 PM 12/03/19 17:29:19 Contractor 0 12/3/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/3/19 5:33 PM 12/03/19 17:33:20 Contractor 0 12/3/19 7:29 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/3/19 4:29 PM 12/03/19 16:29:36 Contractor 0 12/3/19 10:51 AM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 28188 20302713 12/3/19 7:51 AM 12/03/19 7:51:46 Contractor 1 12/3/19 11:56 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/3/19 8:56 AM 12/03/19 8:56:41 Contractor 0 12/3/19 2:30 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/3/19 11:30 AM 12/03/19 11:30:54 Contractor 0 12/3/19 2:31 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/3/19 11:31 AM 12/03/19 11:31:34 Contractor 1 12/3/19 11:57 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/3/19 8:57 AM 12/03/19 8:57:12 Contractor 1 12/3/19 2:06 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/3/19 11:06 AM 12/03/19 11:06:45 Contractor 0 12/3/19 9:07 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/3/19 6:07 PM 12/03/19 18:07:22 Contractor 0 12/3/19 10:04 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 40386 20322132 12/3/19 7:04 PM 12/03/19 19:04:22 Unknown 0 12/3/19 6:39 PM Access Granted 5014 ENT CSS CORREDIZA 3 C14-P6-R1 41691 301497 12/3/19 3:39 PM 12/03/19 15:39:40 Contractor 1 12/3/19 8:10 PM Access Granted 5014 ENT CSS TOR 4 RA C12-P3-R2 41691 301497 12/3/19 5:10 PM 12/03/19 17:10:54 Contractor 1 12/3/19 1:17 PM Access Granted 5014 ENT CSS CORREDIZA 2 C14-P7-R1 41691 301497 12/3/19 10:17 AM 12/03/19 10:17:22 Contractor 1 12/3/19 6:38 PM Access Granted 5014 ENT CSS CORREDIZA 2 C14-P7-R1 41691 301497 12/3/19 3:38 PM 12/03/19 15:38:38 Contractor 1 12/3/19 1:16 PM Access Granted 5014 ENT CSS TOR 1 RA C12-P0-R2 41691 301497 12/3/19 10:16 AM 12/03/19 10:16:42 Contractor 1 12/3/19 8:23 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/3/19 5:23 PM 12/03/19 17:23:22 Contractor 0 12/3/19 3:19 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:19 PM 12/03/19 12:19:40 Contractor 0 12/3/19 5:47 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 2:47 PM 12/03/19 14:47:27 Contractor 0 12/3/19 6:22 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:22 PM 12/03/19 15:22:12 Contractor 0 12/3/19 6:50 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:50 PM 12/03/19 15:50:59 Contractor 0 12/3/19 6:51 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:51 PM 12/03/19 15:51:13 Contractor 0 12/3/19 2:46 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 11:46 AM 12/03/19 11:46:26 Contractor 0 12/3/19 3:14 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:14 PM 12/03/19 12:14:04 Contractor 0 12/3/19 3:25 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:25 PM 12/03/19 12:25:06 Contractor 0 12/3/19 3:34 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:34 PM 12/03/19 12:34:19 Contractor 0 12/3/19 3:37 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:37 PM 12/03/19 12:37:40 Contractor 0 12/3/19 3:45 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:45 PM 12/03/19 12:45:31 Contractor 0 12/3/19 3:47 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:47 PM 12/03/19 12:47:14 Contractor 0 12/3/19 3:56 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:56 PM 12/03/19 12:56:52 Contractor 0 12/3/19 4:00 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:00 PM 12/03/19 13:00:00 Contractor 0 12/3/19 4:13 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:13 PM 12/03/19 13:13:27 Contractor 0 12/3/19 4:44 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:44 PM 12/03/19 13:44:50 Contractor 0 12/3/19 5:42 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 2:42 PM 12/03/19 14:42:50 Contractor 0 12/3/19 6:03 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:03 PM 12/03/19 15:03:18 Contractor 0 12/3/19 6:38 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:38 PM 12/03/19 15:38:19 Contractor 0 12/3/19 6:50 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:50 PM 12/03/19 15:50:34 Contractor 0 12/3/19 10:53 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 41838 1116381 12/3/19 7:53 PM 12/03/19 19:53:19 Contractor 0 12/3/19 8:18 AM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 42601 320316 12/3/19 5:18 AM 12/03/19 5:18:12 Contractor 0 12/3/19 11:08 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/3/19 8:08 AM 12/03/19 8:08:48 Contractor 0 12/3/19 4:07 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 44085 410614 12/3/19 1:07 PM 12/03/19 13:07:15 Contractor 0 12/3/19 10:48 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/3/19 7:48 AM 12/03/19 7:48:42 Contractor 1 12/4/19 8:25 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 24808 20600609 12/4/19 5:25 PM 12/04/19 17:25:29 Contractor 0 12/4/19 10:23 PM Access Granted 1493 ROMULUS T/S 7 EAST OUT -C23-P2-R2 26792 20302725 12/4/19 7:23 PM 12/04/19 19:23:54 Contractor 0 12/4/19 1:16 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/4/19 10:16 AM 12/04/19 10:16:18 Contractor 1 12/4/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/4/19 5:28 PM 12/04/19 17:28:04 Contractor 0 12/4/19 4:33 AM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27915 20616794 12/4/19 1:33 AM 12/04/19 1:33:38 Contractor 0 12/4/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27983 20632009 12/4/19 9:28 AM 12/04/19 9:28:52 Contractor 0 12/4/19 12:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28038 315970 12/4/19 9:28 AM 12/04/19 9:28:52 Contractor 0 12/4/19 8:30 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 28064 314473 12/4/19 5:30 PM 12/04/19 17:30:01 Contractor 0 12/4/19 3:02 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 28064 314473 12/4/19 12:02 PM 12/04/19 12:02:32 Contractor 0 12/4/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/4/19 5:33 PM 12/04/19 17:33:02 Contractor 0 12/4/19 7:29 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/4/19 4:29 PM 12/04/19 16:29:46 Contractor 0 12/4/19 4:40 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/4/19 1:40 PM 12/04/19 13:40:04 Contractor 0 12/4/19 10:55 AM Access Granted 1493 ROMULUS T/S 5 EAST IN -C23-P1-R5 28188 20302713 12/4/19 7:55 AM 12/04/19 7:55:23 Contractor 1 12/4/19 4:52 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 28188 20302713 12/4/19 1:52 PM 12/04/19 13:52:17 Contractor 1 12/4/19 2:08 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/4/19 11:08 AM 12/04/19 11:08:34 Contractor 0 12/4/19 2:09 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/4/19 11:09 AM 12/04/19 11:09:21 Contractor 1 12/4/19 10:02 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 40386 20322132 12/4/19 7:02 PM 12/04/19 19:02:21 Unknown 0 12/4/19 7:29 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/4/19 4:29 PM 12/04/19 16:29:07 Contractor 0 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 1 EXIT C21-P0-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:32 Contractor 0 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 2 EXIT C21-P1-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:35 Contractor 0 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 2 EXIT C22-P1-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:22 Contractor 0 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 3 EXIT C22-P2-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:19 Contractor 0 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 3 EXIT C21-P2-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:38 Contractor 0 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 1 EXIT C22-P0-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:27 Contractor 0 12/4/19 1:28 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/4/19 10:28 AM 12/04/19 10:28:50 Contractor 0 12/4/19 1:24 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/4/19 10:24 AM 12/04/19 10:24:23 Contractor 0 12/4/19 3:15 PM Access Granted 1443ASM BODY SHOP T/S 2 EXIT C20-P2-R2 41838 1116381 12/4/19 12:15 PM 12/04/19 12:15:32 Contractor 0 12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS2 EXIT C01-P0-R2 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:40 Contractor 0 12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS1 EXIT C01-P0-R1 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:41 Contractor 0 12/4/19 3:15 PM Access Granted 1443ASM BODY SHOP T/S 1 EXIT C20-P1-R2 41838 1116381 12/4/19 12:15 PM 12/04/19 12:15:35 Contractor 0 12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 2 EXIT C18-P2-R1 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:03 Contractor 0 12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 1 EXIT C18-P1-R2 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:01 Contractor 0 12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 3 EXIT C18-P3-R1 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:07 Contractor 0 12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:36 Contractor 0 12/4/19 7:00 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41838 1116381 12/4/19 4:00 PM 12/04/19 16:00:05 Contractor 0 12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS4 EXIT C11-P3-R1 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:35 Contractor 0 12/4/19 6:38 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 41838 1116381 12/4/19 3:38 PM 12/04/19 15:38:32 Contractor 0 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 1 ENT C22-P0-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:05 Contractor 1 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 3 ENT C22-P2-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:10 Contractor 1 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 3 ENT C21-P2-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:17 Contractor 1 12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 2 ENT C22-P1-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:07 Contractor 1 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 2 ENT C21-P1-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:20 Contractor 1 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 1 ENT C21-P0-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:23 Contractor 1 12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH ADA ENT C21-P3-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:26 Contractor 1 12/4/19 11:00 AM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 42601 320316 12/4/19 8:00 AM 12/04/19 8:00:22 Contractor 0 12/4/19 7:31 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 44085 410614 12/4/19 4:31 PM 12/04/19 16:31:35 Contractor 0 12/4/19 10:17 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/4/19 7:17 AM 12/04/19 7:17:23 Contractor 1 12/5/19 8:22 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 24808 20600609 12/5/19 5:22 PM 12/05/19 17:22:12 Contractor 0 12/5/19 11:35 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 26792 20302725 12/5/19 8:35 PM 12/05/19 20:35:47 Contractor 0 12/5/19 1:58 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/5/19 10:58 AM 12/05/19 10:58:03 Contractor 1 12/5/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/5/19 5:28 PM 12/05/19 17:28:05 Contractor 0 12/5/19 4:33 AM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27915 20616794 12/5/19 1:33 AM 12/05/19 1:33:38 Contractor 0 12/5/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27983 20632009 12/5/19 9:28 AM 12/05/19 9:28:23 Contractor 0 12/5/19 12:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28038 315970 12/5/19 9:28 AM 12/05/19 9:28:28 Contractor 0 12/5/19 8:28 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 28064 314473 12/5/19 5:28 PM 12/05/19 17:28:45 Contractor 0 12/5/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/5/19 5:33 PM 12/05/19 17:33:33 Contractor 0 12/5/19 10:54 AM Access Granted 1493 ROMULUS T/S 7 EAST IN -C23-P2-R1 28188 20302713 12/5/19 7:54 AM 12/05/19 7:54:49 Contractor 1 12/5/19 7:29 PM Access Granted 1493 ROMULUS T/S 5 EAST OUT -C23-P1-R6 28188 20302713 12/5/19 4:29 PM 12/05/19 16:29:28 Contractor 0 12/5/19 2:14 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/5/19 11:14 AM 12/05/19 11:14:30 Contractor 0 12/5/19 2:39 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/5/19 11:39 AM 12/05/19 11:39:01 Contractor 0 12/5/19 2:14 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/5/19 11:14 AM 12/05/19 11:14:59 Contractor 1 12/5/19 2:39 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/5/19 11:39 AM 12/05/19 11:39:25 Contractor 1 12/5/19 1:09 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/5/19 10:09 AM 12/05/19 10:09:13 Contractor 0 12/5/19 8:53 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/5/19 5:53 PM 12/05/19 17:53:03 Contractor 0 12/5/19 10:03 PM Access Granted 1443ASM POST 1 TS4 EXIT C11-P3-R1 40386 20322132 12/5/19 7:03 PM 12/05/19 19:03:06 Unknown 0 12/5/19 7:14 PM Access Granted 5014 ENT CSS TOR 3 RA C12-P2-R2 41691 301497 12/5/19 4:14 PM 12/05/19 16:14:39 Contractor 1 12/5/19 7:08 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/5/19 4:08 PM 12/05/19 16:08:23 Contractor 0 12/5/19 11:10 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/5/19 8:10 AM 12/05/19 8:10:09 Contractor 0 12/5/19 11:13 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/5/19 8:13 AM 12/05/19 8:13:19 Contractor 0 12/5/19 7:25 PM Access Granted 1493 ROMULUS T/S 5 EAST OUT -C23-P1-R6 44085 410614 12/5/19 4:25 PM 12/05/19 16:25:36 Contractor 0 12/5/19 10:50 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/5/19 7:50 AM 12/05/19 7:50:27 Contractor 1

Re: Help with calculating time ???

Jimmy801 — Mon, 06 Jan 2020 18:59:34 GMT

Hello rf27

i tried to analyse your data withou success. Grouped by the two columns i got this examples (only with nulls)

03.12.2019 20:21:00	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	24808	20600609	03.12.2019 17:21:00	03.12.2019	17:21:42	Contractor
04.12.2019 20:25:00	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	24808	20600609	04.12.2019 17:25:00	04.12.2019	17:25:29	Contractor
05.12.2019 20:22:00	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	24808	20600609	05.12.2019 17:22:00	05.12.2019	17:22:12	Contractor

for others only 1

03.12.2019 18:39:00	Access Granted	5014 ENT CSS CORREDIZA 3 C14-P6-R1	41691	301497	03.12.2019 15:39:00	03.12.2019	15:39:40	Contractor	1
03.12.2019 20:10:00	Access Granted	5014 ENT CSS TOR 4 RA C12-P3-R2	41691	301497	03.12.2019 17:10:00	03.12.2019	17:10:54	Contractor	1
03.12.2019 13:17:00	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	03.12.2019 10:17:00	03.12.2019	10:17:22	Contractor	1
03.12.2019 18:38:00	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	03.12.2019 15:38:00	03.12.2019	15:38:38	Contractor	1
03.12.2019 13:16:00	Access Granted	5014 ENT CSS TOR 1 RA C12-P0-R2	41691	301497	03.12.2019 10:16:00	03.12.2019	10:16:42	Contractor	1
05.12.2019 19:14:00	Access Granted	5014 ENT CSS TOR 3 RA C12-P2-R2	41691	301497	05.12.2019 16:14:00	05.12.2019	16:14:39	Contractor	1

Jimmy

Re: Help with calculating time ???

rf27 — Mon, 06 Jan 2020 19:14:33 GMT

How did you use the Group by ? What Columns ?

Re: Help with calculating time ???

Jimmy801 — Mon, 06 Jan 2020 19:18:26 GMT

Hello rf27

I grouped by the columns stated by you "empid" and "cardnum"

Jimmy

Re: Help with calculating time ???

rf27 — Thu, 09 Jan 2020 18:31:30 GMT

Hi Ken,

Is it possible you could send me an example ? I have created the index, but I'm still having issues with this..

Any help is greatly appreciated ....

Richard

Re: Help with calculating time ???

kentyler — Thu, 09 Jan 2020 18:35:47 GMT

How about doing a screen share ? Its easier to look into complex problems that way. Just email me a good date and time and I'll send you a meeting invitation. ken@8thfold.com