Jump to content

Update MySQL table from 2 or 3 other tables


Mahmoud

Recommended Posts

Dears,

I really need your help. I have a project for automatically assign people to exams locations and timings. I have 3 tables, 1 containing places, 1 timings, 1 people and their roles. I want a query that assign 1 person from role 1 and 1 person from role 2 with a timing and a place and to do this randomly and equally . So, it will repeat the person only if all people are assigned before. I really appreciate your help.

This is as far as I could research ... this example

UPDATE member m1

JOIN ( select id, floor(1+rand()*10) as rnd from member ) m2 on m1.id=m2.id

JOIN names n on n.id = m2.rnd

SET m1.name=n.name

 

But this doesn't do all, please accept my great apology.

 

Mahmoud

Link to comment
Share on other sites

Dears,

 

Really thanks for your replies.

I have about 100 people with 2 roles (1: phd, 2:employee)

I also have about 68 places and about 120 timings

I should select 1 phd and 1 employee in each timing

I should repeat if all people assigned before. Also, I can repeat in the same day but in another timing.

timing is like following:

image.png.9a2a4acbe8408e2512633efd081d151c.png

Roles:

image.png.6c95139f3224c47b09f669110825c51e.png

Places:

image.png.7c622f54f57352f1ea1b18bfd10ed603.png

Thanks and really appreciated.

 

Link to comment
Share on other sites

2 hours ago, Barand said:

Is the objective

  • to fill every location/time combination, or
  • just to allocate everyone to at least one location/time?

Dear Barand,

 

My objective is to allocate 1 person with role 1 and 1 person with role 2 in a timing randomly without repeating.

To fill all timings with this combination.

 

Thanks

Link to comment
Share on other sites

You have 120 times and 100 people

Assuming a ratio of php to employee of 1:4 then 80 employess will go into one timeslot each and 40 of those will go into a second remaining timeslot. Phps then then be shuffled and allocated in rotation to the timeslots (with a check that php/employee combinations are not repeated)

Only one location would be required.

Correct?

Link to comment
Share on other sites

If you can load that into a database to create a replica of your timetable data, then you are a better man than I.

It tells me nothing about the problem at hand. For example, is this a possible situation ?

location |  time
---------+---------
    1    |    1
    1    |    2
    2    |    1      
    2    |    2      
    3    |    1      
    3    |    2      
    4    |    1      
    4    |    2 

where, as you said, each location has two times. However, each time has four locations (simultaneuous exams) which greatly affects the complexity of the algorithm if we have to avoid scheduling people to be at more than one location at the same time. That is why I would like to see the actual data.

If providing the full information is too much trouble, I am quite happy to walk away.

How are you doing with the problem at your end?

Link to comment
Share on other sites

Sure I can provide you will all the data, it is no secret.

The last table to be filled is like this:

image.png.7d4577d3b2376dcdbc49875cdf92f035.png

As you see, the date and time re in 1 column, the location is already added as each location has to timings 1 starts 9:30 AM and 1 starts 1:00 PM.

As you figured out this is exam location schedule.

Link to comment
Share on other sites

If that is all the data then, no problem. Job done

   ID    |  timing                   |  location        |   PhD   |    Emp  |
---------+---------------------------+------------------+---------+---------+ 
    1    |  20/11/2018 09:30         | North Corridor   |    1    |     3   |
    2    |  20/11/2018 13:00         | South Corridor   |    2    |     4   |
---------+---------------------------+------------------+---------+---------+ 
    

Have a good life.

Link to comment
Share on other sites

34 minutes ago, Barand said:

Zzzzzzzzzzzz! Are you manually typing all the the data ?

Not really.

But only the timing table is not complete as the person assigned to fill it was off today.

That's why I'm late filling it.

I want to send the .sql file to you. How to do this ??

 

Update: Please use this link to view my .sql dumb file:

http://barbary.info/barbary.online/database.sql

Link to comment
Share on other sites

Given that you have this the algorithm I came up with is this

Create master array of Phds and shuffle
Create master array of employes and shuffle

copy master phd array to temp phd array
copy master emp array to temp emp array

From schedule table create an array keyed by datetime whose elements are an array of the places with that same time  EG
"datetimeval1" => [   [placeA, phd, emp],
                      [placeB, phd, emp],
                      [placeC, phd, emp],

                  ],
"datetimeval2" => [   [placeX, phd, emp],
                      [placeY, phd, emp],
                      [placeY, phd, emp],

                  ]
                  
foreach schedArray as time => tdata
    foreach tdata as place
        if tempEmp is empty recopy master array to it
        pop value from tempEmp and update place[emp]
        ... repeat for phd data ...
    endforeach
endforeach

So we allocate the next emp to each time/place in turn. When all staff allocated reload staff array and continue.

Having the datetime as the key avoids the problem of double-allocation of staff (provided there are more avaulable staff than there are concurrent exams)

The array ca then be used to update the schedule table with the staff.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.