4
\$\begingroup\$

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)

)
\$\endgroup\$
2
  • 1
    \$\begingroup\$ 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... \$\endgroup\$ – Phrancis May 22 '14 at 4:13
  • \$\begingroup\$ This may help you a little bit.. \$\endgroup\$ – Mark Jun 10 '14 at 13:50
2
\$\begingroup\$

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
    )
)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.