SQL Date Time scheduling question

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Date Time scheduling question

Author

Topic

SQLSlave
Starting Member

8 Posts

Posted - 2006-05-30 : 21:25:51

Hi All,

I am having a major problem with time scheduling logic.

There are two tables.

The first table has the starting and ending times of a workers
schedule.

Example Table 1:
ID EmpNum StartTime EndTime
123 695 May 22 2006 7:00 AM May 22 2006 3:00 PM
124 695 May 23 2006 7:00 AM May 23 2006 3:00 PM
125 695 May 24 2006 7:00 AM May 24 2006 3:00 PM
126 695 May 25 2006 7:00 AM May 25 2006 3:00 PM
127 695 May 26 2006 7:00 AM May 26 2006 3:00 PM

The second table is the schedule of the jobs the worker is running.
Any job not completed that day will startup the next day.

Example Table 2:
JobID Part TimeInMinutes
234 Job1 300
235 Job2 100
236 Job3 134
237 Job4 54
238 Job5 345
239 Job6 754
240 Job7 357
241 Job8 755
242 Job9 456
243 Job10 387

How do I combine the tables to get the work schedule
of the employee for the week?

1. What jobs the employee is running
2. The start and stop times of the jobs

Thank you in advance for any help.
This has me stumped.

SQLSlave

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 21:31:12

What is the expected result ? Can you post it here ?

And what is the relationship between the 2 tables.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-30 : 21:33:12

if id and jobid refer to the same field then...

select empnum,jobid, jobpart, starttime,endtime
from table1 t1
inner join table2 t2
on t1.id=t2.jobid

--------------------
keeping it simple...

SQLSlave
Starting Member

8 Posts

Posted - 2006-05-30 : 21:56:06

Khtan,

Expected:

EmpNum - Job - StartTime - EndTime
695 Job1 May 22 2006 7:00 AM May 22 2006 12:00 PM
695 Job2 May 22 2006 12:00 PM May 22 2006 01:40 PM
695 Job3 May 22 2006 01:40 PM May 22 2006 03:00 PM
695 Job3 May 23 2006 07:00 PM May 23 2006 07:54 AM
695 Job4 May 23 2006 07:54 AM May 23 2006 08:48 AM
695 Job5 May 23 2006 08:48 AM May 23 2006 02:33 PM
695 Job6 May 23 2006 02:33 PM May 23 2006 03:00 PM

Etc....

The relationship would be the time the worker is available to the work that is scheduled during that period.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-30 : 22:00:56

how do you know which job in table2 belongs to empnum in table1? what if job1 for empnum1 is the same time as with job1 of empnum2?

--------------------
keeping it simple...

SQLSlave
Starting Member

8 Posts

Posted - 2006-05-30 : 22:06:50

I did not want to complicate example :)

Assume all the jobs belong to EmpNum1

The master program will include all the employees and all the jobs they are assigned for all three shifts and gaps for time off.

Just trying to get the logic down.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 23:13:19

Sorry, I can't think of any solution right now except the old cursor way. Hang in there. I am sure somebody will come along and give you a hand.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-31 : 04:52:48

then use that in your join fields...

--------------------
keeping it simple...

yonabout
Posting Yak Master

112 Posts

Posted - 2006-05-31 : 05:56:54

Hi,

I don't think you can do it as a select with the tables you supplied. Are there any other tables that you use to join the jobs to the employees?

If there aren't I think you're down to using cursors, but I really think the database could use a redesign.

Cheers,

Yonabout

SQLSlave
Starting Member

8 Posts

Posted - 2006-05-31 : 08:15:14

Ok, For a redesign I added a field for the EmpNum on the second table.

Is it possible to do it without a cursor?

Example Table 1:
ID - EmpNum - StartTime EndTime
123, 695, May 22 2006 7:00 AM, May 22 2006 3:00 PM
124, 695, May 23 2006 7:00 AM, May 23 2006 3:00 PM
125, 695, May 24 2006 7:00 AM, May 24 2006 3:00 PM
126, 695, May 25 2006 7:00 AM, May 25 2006 3:00 PM
127, 695, May 26 2006 7:00 AM, May 26 2006 3:00 PM

The second table is the schedule of the jobs the worker is running.
Any job not completed that day will startup the next day or the next week.

Example Table 2:
JobID - EmpNum - Part - TimeInMinutes
234, 695, Job1, 300
235, 695, Job2, 100
236, 695, Job3, 134
237, 695, Job4, 454
238, 695, Job5, 345
239, 695, Job6, 754
240, 695, Job7, 357
241, 695, Job8, 755
242, 695, Job9, 456
243, 695, Job10, 387

Results Expected:

EmpNum - Job - StartTime - EndTime
695 Job1 May 22 2006 7:00 AM May 22 2006 12:00 PM
695 Job2 May 22 2006 12:00 PM May 22 2006 01:40 PM
695 Job3 May 22 2006 01:40 PM May 22 2006 03:00 PM
695 Job3 May 23 2006 07:00 PM May 23 2006 07:54 AM
695 Job4 May 23 2006 07:54 AM May 23 2006 08:48 AM
695 Job5 May 23 2006 08:48 AM May 23 2006 02:33 PM
695 Job6 May 23 2006 02:33 PM May 23 2006 03:00 PM

Etc...

SQLSlave
Starting Member

8 Posts

Posted - 2006-06-07 : 21:57:00

Got it figured out - It was not pretty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources