2 Replies Latest reply on Aug 12, 2013 2:32 PM by DanielShanahan

    Finding records that do not exist

    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?

        • 1. Re: Finding records that do not exist
          erolst

          DanielShanahan wrote:

           

          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?

           

          You can build the list yourself:

           

          Put the dates in a variable, too, then perform ExecuteSQl within a nested loop through this date list, where you use the dates one by one, instead of the entire list; if there's a match, now you have a name AND a specific date to add a new entry to your list variable ( like List ( $myList ; $mySQLHit ) )

          • 2. Re: Finding records that do not exist
            DanielShanahan

            Thanks.  That works.