Member Avatar for taekiewzz

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
  • 250 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.