I have a table called DUTY (columns: dutyid, dutyname, staffid) and a table called STAFF (columns: staffid, staffname)

In order to be fair, each of the staff will be auto assigned to each duty entry (record). So what should I do whenever I would like to insert a duty entry, it will auto assign the staffid (sequential) for it.


  • staffid : 1 staffname: Jack
  • staffid : 2 staffname: Mary

So when I insert a new entry (first entry) for duty, it will auto insert staffid = 1 for duty table. For second entry, staffid will be 2.

And for the following entry, it will keep looping the staffid sequentially.

Desired answer:

dutyid   dutyname   staffid 
  1      cleaning     1 
  2      cleaning     2 
  3      cleaning     1 
  4      cleaning     2 
  5      cleaning     1 
  6      cleaning     2 
  7      cleaning     3       new staff
  8      cleaning     1 
  9      cleaning     2 
 10      cleaning     3 

Can anyone show and explain to me what I should do in my stored procedure...

4 Years
Discussion Span
Last Post by pritaeas

First get the last dutyid record. That holds the last used staffid. You can then use that to retrieve the next staffid.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.