Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: mapping coordinates- suggestion needed

by jandrew (Hermit)
on Oct 15, 2010 at 03:38 UTC ( #865392=note: print w/ replies, xml ) Need Help??


in reply to mapping coordinates- suggestion needed

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


Comment on Re: mapping coordinates- suggestion needed
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://865392]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (16)
As of 2014-07-31 19:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (252 votes), past polls