Help with inner joins to the same table 6 times

[Jalz]

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

[eos]

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?

[Jalz]

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

[eos]

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

[Jalz]

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.

[eos]

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.

[Jalz]

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

[eos]

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 …

[Jalz]

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