DanielShanahan

Finding records that do not exist

Discussion created by DanielShanahan on Aug 8, 2013
Latest reply on Aug 12, 2013 by DanielShanahan

I need to find a list of employees who do not have appointments for a date range. In other words, I have a list of people and a list of dates and I want to find instances where the two do not match.

 

I have in mind something like this:

 

$eList = List ( EMPLOYEE::employee )

$c = ValueCount ( $eList )

$i = 1

 

Loop

$e = GetValue ( $list ; $i )

 

ExecuteSQL ( " SELECT " & $e & "FROM appointments WHERE employee1 NOT IN ( 2013-08-08, 2013-08-09, 2013-08-10, 2013-08-11 ) " ; "" ; "" )

 

$i = $i + 1

End Loop

 

However, this is not quite complete because I need the SELECT statement to return a date as well as the employee name. I'd like the ExecuteSQL () to return something like this:

 

Jane Doe, 2013-08-08

Jane Doe, 2013-08-09

Jane Doe, 2013-08-11

Tom Jones, 2013-08-08

Tom Jones, 2013-08-11

Bill Hailey, 2013-08-11

 

Any insight?

Outcomes