Skip to Content
avatar image
Former Member

Problem with record selection formula

I am in the process of writing a report that shows clock in and clock out information for our employees. The problem I am encountering is the fact that being that we are a 24 hour operation, we have some employees that punch in on Saturday which is the last day of our pay period and punch out on Sunday which is the first day of a new pay period. Originally I had this, which executed very quickly, but didnt display any punches where the punch in was on Saturday and the punch out was on Sunday:

{Jobcode_Master.jobcode_id} = {?Jobcode ID} and

{Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} and

{Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to {?Week End Date}

So to include those missing punches, I came up with this which does work, but takes nearly 5 minutes to execute:

{Jobcode_Master.jobcode_id} = {?Jobcode ID} and

{Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} or

{Jobcode_Master.jobcode_id} = {?Jobcode ID} and

{Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to {?Week End Date}

I have been racking my brain on this for several days now and just cant figure out what makes the second record selection formula take so long. Does anyone have any ideas? Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Oct 06, 2008 at 02:15 PM

    Try this:

    {Jobcode_Master.jobcode_id} = {?Jobcode ID} and

    {Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} and

    ( isnull({Emp_Clock_Data.adjusted_clockout_dttime}) or

    {Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to {?Week End Date} )

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 06, 2008 at 02:25 PM

    Hi Roger,

    I believe this is the problem with braces..Please check the following :

    ({Jobcode_Master.jobcode_id} = {?Jobcode ID} and

    {Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} )

    or

    ({Jobcode_Master.jobcode_id} = {?Jobcode ID} and

    {Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to {?Week End Date})

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Try this

      {Jobcode_Master.jobcode_id} = {?Jobcode ID} and
      ({Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} 
      or
      {Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to {?Week End Date})

      Regards,

      Raghavendra

  • Oct 06, 2008 at 04:05 PM

    Roger,

    Would it work if the clock out parameter date was increased by 1 day to allow for this rather than create an or statement? For Example;

    {Jobcode_Master.jobcode_id} = {?Jobcode ID} and

    {Emp_Clock_Data.adjusted_clockin_dttime} in {?Week Start Date} to {?Week End Date} and

    {Emp_Clock_Data.adjusted_clockout_dttime} in {?Week Start Date} to dateadd('d',1,{?Week End Date} )

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 06, 2008 at 06:57 PM

    I believe the short answer to your question "why is this taking so long?" is the 'or' statement.

    Generally speaking, ORs are expencive, where ANDs are cheap.

    If you can find a way (as suggested by the SAP chap) to achieve the same results without the 'OR' you should see a performance improvement.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 06, 2008 at 07:55 PM

    Thank you for all the help. All of your solutions worked to sort the records, unfortunately none of them sped up the process. I am just going to have to look for ways to sort the records more efficiently without the use of an or statement. Thanks again for all of the assistance.

    Add comment
    10|10000 characters needed characters exceeded