I Hate Loops....But - SQL Server Forums

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)

I Hate Loops....But

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-04 : 14:05:40

I could not come up with a way around it on this one.

Background
I pull from a master table that contains the whole year in half hour increments logged as shift 1,2,3 with holidays marked shift (0 = no production) and drop a two week period into a table.
The supervisor then gets to lock out shifts,days or hours that the machine will not be crewed or available by changeing the shift to 0. The result is dropped into another table with only active times showing.

This procidure applies the correct job number from the schedule into the half hour incrments in the table and tests for gaps (these require the machine to be prepped again and add more time).

The min and max give the job start time and stop time.

If someone has a better Idea I would love to hear it as this will create quite a load when done for all machines in the plant.

---------------------------------------------- Import active shifts

SELECT IDENTITY (int, 1, 1) AS Shid , CAst(CalID AS int) AS CalID, CLdate, Shift, Schday, Workdate, Job_number_link
INTO dbo.[#Cal5C]
FROM dbo.Cal5C
WHERE (Shift > 0)

Declare @shid int ,@stepcount int,@maxShid int,@startCalID int,@StopCalID int
Select @shid = 1
Select @stepcount =1
Select @maxShid = Max(shid) from #Cal5C

While @shid < @maxShid
Begin

Select @Job_number_link = Job_number_link, @Timeblock = Timeblock
From #TempSch
Where stepcount = @stepcount

Select @startCalID = CalID
From #Cal5C
Where Shid = @shid
------------------------------------------Check for break in shift
Select @StopCalID = CalID
From #Cal5C
Where Shid = @shid + (@Timeblock -1)

If (@StopCalID - (@Timeblock -1)) > @startCalID
begin
Select @Timeblock = @Timeblock + 3
end
-------------------------------------
Update #Cal5C
Set Job_number_link = @Job_number_link
Where Shid between @shid and @shid + (@Timeblock -1)

Update dbo.Cal5C
Set Job_number_link = dbo.#cal5c.Job_number_link
From dbo.Cal5C inner join dbo.#cal5c on dbo.Cal5C.CalID = dbo.#Cal5C.CalID

select @stepcount = @stepcount + 1
Select @Shid = @shid + @Timeblock

end

SELECT dbo.Job_Number_Master_List.job_priority_number, dbo.Job_Number_Master_List.job_due_date, dbo.Job_Number_Master_List.job_number,
dbo.Job_Number_Master_List.job_name, MIN(dbo.Cal5C.CLdate) AS Starttime, DATEADD(mi, 30, MAX(dbo.Cal5C.CLdate)) AS Endtime
FROM dbo.Job_Number_Master_List INNER JOIN
dbo.Cal5C ON dbo.Job_Number_Master_List.Job_Number_Link = dbo.Cal5C.Job_number_link
GROUP BY dbo.Job_Number_Master_List.job_priority_number, dbo.Job_Number_Master_List.job_due_date, dbo.Job_Number_Master_List.job_number,
dbo.Job_Number_Master_List.job_name
ORDER BY MIN(dbo.Cal5C.CLdate)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-04 : 14:39:09

can you provide some sample data, dml and ddl?

Go with the flow & have fun! Else fight the flow

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-04 : 15:29:05

Bit Long for the samples so we will link.

ftp://calumetcarton.com/Autoschedule.doc

I only did 6 jobs. There are currently 66 for this machine alone.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources