Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrajakrishnan
Starting Member
3 Posts |
 Posted - 2009-10-28 : 00:14:42
|
| There is a table as follows .Work |Days required |Employee who all can do this work.------------------------------------------------------------------------work1 |2 |Employee1 and employee 2work2 |3 |Employee3 and employee4work3 |2 |Employee1 and employee 2work4 |1 |Employee3 and employee4work5 |2 |Employee1 and employee 2work6 |2 |Employee3 and employee4work7 |2 |Employee1 and employee 2work8 |2 |Employee3 and employee4work9 |2 |Employee1 and employee 2work10 |2 |Employee3 and employee4work11 |2 |Employee1 and employee 2work12 |2 |Employee3 and employee4work13 |2 |Employee1 and employee 2work14 |2 |Employee3 and employee4work15 |2 |Employee1 and employee 2work16 |2 |Employee3 and employee4work17 |2 |Employee1 and employee 2work18 |2 |Employee3 and employee4work19 |2 |Employee1 and employee 2work20 |2 |Employee3 and employee4This table will tell how many days required to do a work and who all can do this work.Now the result I need through a join isFor example today is 28-octOutput |Output |Output |Reason(Just to explain you)-----------------------------------------------------------------------------------------work1 |28,29 Oct |Employee1 |Emp1 or Emp2 can do as both are free I can assign any onework2 |28,29,30 Oct |Employee3 |Emp3 and Emp4 are free so I can assign work any onework3 |28,29 Oct |Employee2 |emp1 is busy on work1 and emp2 is free so assigning work to emp2work4 |28-Oct |employee4 |emp3 is busy on work2 and emp4 is free so assigning work to emp4work5 |30,31 |employee1 |emp1 and emp2 will be free on 30th onwards,so any one I can put for this workwork6 |29,30 Oct |employee4 |emp3 will get free on 31st but emp4 will be free on 29th, so will provide this work to emp4 so that he can start work from 29thwork7 |30,31 Oct |employee2 | work8 |31oct,1st nov |employee3 |work9 |30,31oct |employee2 | work10 | | |............ | | |............ | | |............ | | |............ | | |............ | | |............ | | |........... | | | | | | | | |Similarly all the work should get assigned.The ouput of join should give me the date and Employee details.Date is based on starting from today date, then how many required to do this work and the availability of the EmployeeCurrently I am doing the same using cursor, but as work and employee list is high it is taking hell lot of time, can any one suggest me how to get this same result using joins instead of cursor.Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-28 : 09:51:53
|
Question1. in your table, the column "Employee who all can do this work" will contain multiple value in the same column like "Employee1 and employee 2" ? Is it always 2 Employee in there separated by "AND" ? Will you have more than 2 Employee in that column ?2. in the reason, how do you determine that an employee is free ?3. is there any sequence of processing of assigning employee to work ? Is it in the order of work column ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
mrajakrishnan
Starting Member
3 Posts |
Posted - 2009-10-31 : 02:39:56
|
| Thanks in advance for the resposeBasically there will be a table saying who all can do that work and how much time required to do the work. The number of person who can do the work can be more than 2 ,it will not be in saame column, it will be in differnt rowsFor eg)Work| (Employee who all can do that work)| Time required to complete this workWork1| Employee1| 16 hoursWork1| Employee2|16 hourswork1| Employee3|16 hoursWork2| Employee2|16 hourswork2| Employee5|16 hourswork2| Employee6| 16 hoursetcThe above says work1 can be done eithere Employee2 or Employee2 or Employee3 depends who is free. One work will be always by one person, no sharing work.Employee table says working hours of each employee per day, For example Employee 1 working hours might be 8 hours per day,Employee2 standard working time might be 16hours per days. It says Work1 can be completed by Employee1 in 2 days if Employee2 do the same work he will complete in one day.So If employee1 take this job next employee1 will be free is after 2 daysBut If employee2 take this job next employee2 will be free is after 1 days, so that he can take next work on 2nd day/Employ |
|
|
|
|
|
|
|
|
replace a cursor using join? challenging Question