About stored procedures for watch management

Stored Procedures
A BT director's request on duty management requirements are as follows

11 Group of Queues n Individuals, there are 4 roles, leaders, men, aunts, drivers. n Individuals line up in order according to their roles.

2 on duty: Monday to Sunday a leader on duty 1 drivers on duty; Monday to Sunday 1 men on duty per night; Saturday Sunday morning 2 aunt on duty; the day 1 leaders 1 drivers 1 men morning 2

3 Requirements queue can be deleted and checked, the personnel order can be adjusted, the queue changes, the watch automatically updated

4 require the queue personnel can be redeployed at any time to the people in the column do not participate in this round of the order (travel or leave) next to the queue, continue to order, the staff drawn, the queue automatically forward replacement

5 shifts, etc...

Build 2 Tables

1 watching

[DateTime] Date [weekday] week [Leaderid] leader ID [Maleid] man ID [female1] Aunt 1id [Female2] Aunt 2id [DriverID] driver ID [Mark] remark

2 Watching_person

[Ordercode] Personnel number [PersonID] Person ID [part] personnel role [leave] leave [mark] remark

Part for the personnel role 1 leader 2 man 3 Aunt 4 driver

When the new queue is generated, it needs to be more granular from tomorrow's duty schedule (30 days here), and then pass the starting position of the role queued to the stored procedure (that is, to get the driver from the beginning of the first row of men from the first row of men's aunt ... ）

Create proc Proc_watchingsetup--parameter is the starting position of four roles @leader int, @Male int, @Female int, @Driver intas

DECLARE @i INT--counter declare @j intdeclare @PersonID intdeclare @weekday INTDECLA Re @InsertPoint datetimedeclare @msg char (20)

Set @i=1set @j=1

--Transaction start begin Tran rechange--delete tomorrow's record (queue changed delete previous) delete from watching where [Datetime]>getdate ()

if (@ @error <>0) Begin rollback tran Set @msg = ' Error1 ' return end

--Reinsert the 30 day date and week while @i<=30 begin insert Watching (Datetime,weekday) VALUES (DATEADD (DAY,@I,{FN curdate ()), Datep Art (Weekday,dateadd (Day,@i,{fn curdate ()))) Set @i=@i+1 end

if (@ @error!=0) Begin rollback tran Set @msg = ' Error2 ' return end

--Start using cursors

Set @j=1

--////first read the queue of leaders in a queue order

DECLARE cur_watchingperson scroll cursor for select Personid to Watching_person where part=1 order by ordercode ASC

Open Cur_watchingperson

--Move to start fetch absolute @leader from Cur_watchingperson to @PersonIDif @ @fetch_status =-1 fetch first from cur_watching Person into @PersonID

Set @i=1

While @i<=30 begin

     while @j<=7--the maximum may be 1 people inserted 7 days        begin                 Update watching set leaderid= @PersonID where [ Datetime]= (DateAdd (Day,@i,{fn curdate ()))                  if (@ @error!=0)           Begin             rollback tran            Set @msg = ' Error3 '           return         end       --if less than 7 days to the weekend exit cycle substitution          Select @weekday =datepart (Weekday,dateadd (Day,@i,{fn curdate ()))          set @i=@i+1        if (@weekday =1)           break                  End

Set @j=1 fetch NEXT from Cur_watchingperson into @PersonID--if the back queue is out of bounds the first if @ @fetch_status =-1 Fetch the Cur_watchingperson into @PersonID end

Close Cur_watchingpersondeallocate Cur_watchingperson

--////////////driver very leader exactly like declare cur_watchingperson4 scroll cursor for select Personid from Watching_person where part=4 ORDER BY Ordercode ASC

Open Cur_watchingperson4

--Move to start fetch absolute @driver from Cur_watchingperson4 to @PersonIDif @ @fetch_status =-1 fetch first from Cur_watchi Ngperson4 into @PersonID

Set @i=1

While @i<=30 the begin while @j<=7--the longest could be 1 people inserting 7-day begin update watching set driverid= @PersonID where [Datetime]= (DateAdd (Day,@i,{fn curdate ()})) if (@ @error!=0) Begin--rollback tra N Set @msg = ' Error3 ' return end

Select @weekday =datepart (Weekday,dateadd (Day,@i,{fn curdate ())) Set @i=@i+1 if (@weekday =1) break End

Set @j=1 fetch NEXT from Cur_watchingperson4 into @PersonID--if the back queue is out of bounds the first if @ @fetch_status =-1 fetc H Cur_watchingperson4 into @PersonID end

Close Cur_watchingperson4deallocate Cur_watchingperson4

--///////////

--Men's night shifts are relatively easy to declare cur_watchingperson2 scroll cursor for select Personid from Watching_person where part=2 order b Y ordercode ASC

Open Cur_watchingperson2

--Move to start fetch absolute @male from Cur_watchingperson2 to @PersonIDif @ @fetch_status =-1 fetch first from cur_watching Person2 into @PersonID

Set @i=1

While @i<=30 begin update watching set maleid= @PersonID where [datetime]= (DateAdd (Day,@i,{fn curdate ()}))             if (@ @error!=0) Begin rollback tran Set @msg = ' Error3 ' return end Set @i=@i+1

FETCH NEXT from Cur_watchingperson2 into @PersonID--if the back queue is out of bounds @fetch_status =-1 fetch first F Rom Cur_watchingperson2 into @PersonID end

Close Cur_watchingperson2deallocate Cur_watchingperson2

--Aunt every Saturday Sunday 2 people day declare cur_watchingperson3 scroll cursor for select Personid from Watching_person where part=3 order by Ordercode ASC

Open Cur_watchingperson3

Fetch absolute @female cur_watchingperson3 into @PersonIDif @ @fetch_status =-1 fetch the FROM Cur_watchingperson3 Into @PersonID

Set @i=1

While @i<=30 begin

     Select @weekday =[weekday] from watching where [Datetime]= (DateAdd (Day,@i,{fn curdate ()))          --to determine that only a half-day of the weekend is scheduled for 2 people      if @weekday =7 or @ weekday=1        begin                       --insert First          Update watching set female1= @PersonID where [datetime]= (DateAdd (Day,@i,{fn curdate ()}))                       if (@ @error!=0)           Begin            Rollback tran           Set @msg = ' Error3 '            return        End

        fetch NEXT from Cur_watchingperson3 into @PersonID         -If you exceed the border, turn back queue first         if @ @fetch_status =-1                fetch the Cur_watchingperson3 into @ personid               --insert second position          Update watching set female2= @PersonID where [datetime]= DateAdd (DAY,@I,{FN Curdate ()))                       if (@ @error!=0)          Begin           --rollback tran           Set @msg = ' Error3 '           RETURN&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBsp End

The end set @i=@i+1 the fetch next from Cur_watchingperson3 into @PersonID--if the first bit of the back-end queue exceeds the boundary @@f Etch_status=-1 fetch the Cur_watchingperson3 to @PersonID end

Close Cur_watchingperson3deallocate Cur_watchingperson3

Commit Tran



These are the stored procedures that generate the new Watch schedule when the queue changes

Others, such as the provision of holiday adjustment personnel are very similar welcome criticism