# Time spent by product in process represented by clockin and clock out

This is the code I wrote to find out the time spent by product in process represented by clockin and clock out. Time stamps X and Y (for example, 7/23/2013 7:00 AM and 7/23/2013 8:00 AM) reflect the time period where in I am calculating the time.

X, Y, CLOCK_IN and CLOCK_OUT are all timestamps.

I wrote this code, but I am not a programmer. Is there a better way to do it? Please explain step by step.

It runs against a huge set of data. How can I reduce its runtime?

Here is a calculation I'm running in MS Access 2010:

Sum(

IIf([CLOCK_IN]<[X] And [X]<[CLOCK_OUT] And [CLOCK_OUT]<[Y],([CLOCK_OUT]-[X])*24*60,0)+
IIf([CLOCK_IN]<[X] And [Y]=[CLOCK_OUT],60,0)+
IIf([CLOCK_IN]=[X] And [Y]<[CLOCK_OUT],60,0)+
IIf([X]<[CLOCK_IN] And [CLOCK_IN]<[Y] And [Y]<[CLOCK_OUT],([Y]-[CLOCK_IN])*24*60,0)+
IIf([X]<[CLOCK_IN] And [CLOCK_IN]<[CLOCK_OUT] And [CLOCK_OUT]<[Y],([Y]-   [CLOCK_IN])*24*60,0)+
IIf([CLOCK_IN]<[X] And [X]<[Y] And [Y]<[CLOCK_OUT],60,0)+
IIf([X]=[CLOCK_IN] And [Y]=[CLOCK_OUT],60,0)+
IIf([X]=[CLOCK_IN] And [CLOCK_OUT]<[Y],24*60*([CLOCK_OUT]-[X]),0)+
IIf([X]<[CLOCK_IN] And [CLOCK_OUT]=[Y],24*60*([CLOCK_OUT]-[X]),0))/60)

)

• This is vague, are you able to provide information about your data set, maybe some context of what this code is part of? Very difficult to suggest improvements without context... – Phrancis May 22 '14 at 4:13
• This may help you a little bit.. – Mark Jun 10 '14 at 13:50

I'll attempt to provide something helpful, even though this is old.

1. To start with, just syntax-wise, [brackets] are only required if your column name contains a reserved character, such as a space or apostrophe. It looks less cluttered if you don't use them unless needed, and I don't see any need for it in your columns.

2. Line breaks and tabs make code much easier to read. I know Access is quite limited in the amount of formatting you can do and doesn't allow comments, but every bit helps.

3. I notice a lot of redundancy in your Iif statements. 3 statements result in ([CLOCK_OUT]-[X])*24*60. 2 statements result in ([Y]-[CLOCK_IN])*24*60. And 4 statements all result in 60 if true. My thought is to group these together as 3 subqueries and use WHERE instead of Iif to sort out the results.

Here is how I would write this. Note I used Your_Table_Name since your code doesn't say, make sure you replace this with the actual table name.

SUM(
(
SELECT ((CLOCK_OUT - X) * 24) * 60
FROM Your_Table_Name
WHERE CLOCK_IN < X AND X < CLOCK_OUT AND CLOCK_OUT < Y
OR X < CLOCK_IN AND CLOCK_OUT = Y
OR X = CLOCK_IN AND CLOCK_OUT < Y
)
+
(
SELECT ((Y - CLOCK_IN) * 24) * 60
FROM Your_Table_Name
WHERE X < CLOCK_IN AND CLOCK_IN < Y AND Y < CLOCK_OUT
OR  X < CLOCK_IN AND CLOCK_IN < CLOCK_OUT AND CLOCK_OUT < Y
)
+
(
SELECT 60
FROM Your_Table_Name
WHERE CLOCK_IN < X AND Y = CLOCK_OUT
OR CLOCK_IN = X AND Y < CLOCK_OUT
OR CLOCK_IN < X AND X < Y AND Y < CLOCK_OUT
OR X = CLOCK_IN AND Y = CLOCK_OUT
)
)