Re: mapping coordinates- suggestion needed

[jandrew]

Have you considered loading the two tables to a database and running DBI with the following SQL embedded? I used the test data for two tables one containing Jobs and one containing Users with headers for the tables being Start, Stop, and User or Job

SELECT 
Jobs.Job, 
Users.User, 
Count(Users.User) AS CountOfUser

FROM Jobs LEFT JOIN Users 
     ON (
          (
              Jobs.Start <= Users.Start and 
              Jobs.Stop >= Users.Start
                                              ) or 
           (
              Jobs.Start <= Users.Stop and 
              Jobs.Stop >= Users.Stop
                                              ) or
           (
              Users.Start <= Jobs.Start and 
              Users.Stop >= Jobs.Start
                                              ) or
           (
              Users.Start <= Jobs.Stop and
              Users.Stop >= Jobs.Stop         )
       )

GROUP BY Jobs.Job, Users.User

HAVING Count(Users.User) = 1;

I haven't put this into DBI syntax I just drummed it up quickly in Access but the SELECT .. FROM .. syntax should copy over. My results on your test data are.

Job       User     CountOfUser
job_id1   uname1   1
job_id2   uname2   1
job_id3   uname2   1
job_id4   uname2   1