Hey all,

 

I have a table called ClockIn, which stores various codes(letters) for employees in a couple of text fields AM and PM. Within that table, I also store the EmployeeID and the Date.

 

What I would like to do is extract the AM, PM session for 5 days in a row for a given employee. Infact I have a list of employees I want to plot in a field, but I would settle for one as Im sure you just loop through employeeID's until you get to the bottom.

 

I have a custom function which works out the date for $Monday , $Tuesday, $Wednesday... $Saturday . I know need help in constructing he sql query. I can do basic sql, but get my head messed up once I need to start to use joins and the various arguments. Infact, I can't get seem to get a simple SELECT with a WHERE clause working with a date, although the same Select works if I match a string field with a literal string.

 

Can someone help me with my joins please, Im kinda lost on the Monday to Tuesday firstly, forget Wednesday-Sat?

ExecuteSQL ( 
"SELECT Monday.AM, Monday.PM, Tuesday.AM, Tuesday.PM, Wednesday.AM, Wednesday.PM, Thursday.AM, Thursday.PM, Friday.AM, Friday.PM, Saturday.AM
FROM ClockIn AS Monday
INNER JOIN ClockIn AS Tuesday
ON (Monday.EmployeeID = ? AND Tuesday.EmployeeID = ?) AND (Monday.Date = ? AND Tuesday.Date = ?)

"
 ; " " ; "" ; GetValue($EmployeeKey;1); GetValue($EmployeeKey;1) ; Date(9;22;2014) ; Date(9;23;2014)

)

Thanks in advance

 

Jalz

What I would like to do is extract the AM, PM session for 5 days in a row for a given employee.

 

Does every employee have one AM and one PM entry for each day, and you want to find 5 codes in a row for a given employee, starting from date?

 

Or are ClockIn slots only created on demand, and you want to find out if there are 5 entries in a row for a given employee?

Hi Eos, thanks for replying. Every Employee has a record which contains AM and PM even if they are absent.

 

Jalz

 

The table structure for ClockIn is

 

EmployeeID

Date

Am

PM

OK, but you haven't confirmed the purpose of this exercise either way: any 5 entries, only 5 consecutive non-empty entries, or …?

I have the startdate which is a date on Monday in stored in a local variable $Monday. I then want the extract the following, Tuesday, Wednesday, Thursday, Friday and Saturday of that week. So its 5 consecutive days.

I then want the extract the following, Tuesday, Wednesday, Thursday, Friday and Saturday of that week. So its 5 consecutive days.

 

In that case, why make it so complicated?

ExecuteSQL ( "
  SELECT AM, PM
  FROM ClockIn
  WHERE
    EmployeeID = ? AND
    theDate >= ? AND
    theDate <= ?
  " ; " " ; "" ; GetValue ( $EmployeeKey ; 1 ) ; $Monday + 1 ; $Monday + 5
)

Note that it's generally not a good idea to use the name of data types as field names; and in SQL they are reserved names. So I renamed the field in the above sample calculation.

 

If you use CFs to protect/escape your field names for use with SQL, then it shouldn't matter.

Thank you eos,

 

That works a treat! This sql stuff is so new to me I was certain I needed a join of some sort! I'm going to use a simple filemaker loop, to loop through the number of employees and display the results in a global field. Instead of me going through the loop (65 times), for efficiency is there a way I can use GetValue ( $EmployeeKey ; 1 ) to 65 in the sql query to just output everything all at once? If not, I'll just stick to my plan of collecting the data through a fm loop.

 

Best

Jalz

There's the IN clause – WHERE numKey IN (1,2,3 …), or WHERE stringKey IN ('a','b','c' …)

so if you have a list of keys, you could try

Let (  
  myIDList = Substitute ( $EmployeeKey ; ¶ ; "," ) ;   

  ExecuteSQL ( "  
    SELECT AM, PM  
    FROM ClockIn   
    WHERE
      EmployeeID IN (" & myIDList & ") AND 
      theDate >= ? AND 
      theDate <= ?    
  " ; " " ; "" ; $Monday + 1 ; $Monday + 5  
  )
)

which will return a long list (65 *5?); not sure what you want to do with it …

Hi eos,

 

thanks again, its not working, but its because my keys are UUID's so they need to be wrapped around quotes. I'll experiment with this and try and get it working. I want a large list so I can eventually use it in a virtual list to display, something else I'm not familiar with.

 

All the best

Jalz