Grant writes "Assume you have an assembly process that builds a "job" every "x" seconds. You have the following two basic tables to work with:Table: JOBS Table: SCHEDULEKey : JOB Key : PROD_START JOB COLOR PROD_START PROD_STOP--- ----- -------------------- -------------------100 RED 2004-04-26 08:00:00 2004-04-26 12:00:00101 RED 2004-04-26 12:30:00 2004-04-26 16:00:00102 BLUE 2004-04-27 08:00:00 2004-04-27 12:00:00103 RED 2004-04-27 12:30:00 2004-04-27 16:00:00
Given any job and a time it would be easy to determine at what time the following jobs would be built. My question is how can you determine the time a job will be built it you must also take into account the start and stop times? For example, if the build time was 5 minutes, or 300 seconds, and job 100 was built at 11:52, I would want to get back the following in a table:JOB COLOR BUILD_TIME--- ----- -------------------100 RED 2004-04-26 11:52:00101 RED 2004-04-26 11:57:00102 BLUE 2004-04-26 12:32:00103 RED 2004-04-26 12:37:00
Notice the long time between jobs 101 and 102. This caused by the break in the production schedule. In real life this would probably be a lunch break. However, job 102 still took 5 minutes to build when you take into account the 30 minute break. Ideally this would all be done in a single, probably rather complicated, select statement. The next option would be a stored procedure.What I need to decide is would it be better to do this on the server side or should I just send all the jobs back to the client and deal with it there in a higher level language?This would be running on a Windows2000 Server with SQL2000 and all the latest service packs."
Assigning times in SQL with allowances for breaks 
Posted - 2004-05-03 : 10:42:06
