0

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.

Example

  • 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...
Thanks

2
Contributors
1
Reply
20
Views
4 Years
Discussion Span
Last Post by pritaeas
0

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.