I have some tables for placing orders. Depending on the department, an
order must be approved by 1, or more, specific approvers.
create table department (
deptID int not null,
deptName varchar(32),
...
)
create table approvers (
deptID int not null,
appSeq int not null,
appName varchar(32)
)
Some data:
Department
deptID DeptName
1 Hooks
2 Lines
3 Sinkers
4 Rods
5 Reels
...
...
Approver
deptID appSeq appName
1 1 Joe Bass
1 2 Suzy Shad
2 1 Tom Trout
2 2 Suzy Shad
2 3 Joe Bass
3 1 Mike Mako
3 2 Don Dolphin
3 3 Tom Tuna
4 1 Suzy Shad
4 2 Joe Bass
...
...
Joe Bass made the mistake of insulting the boss's dog. So, the boss
decides to remove Joe's "Last Approver" status (the appSeq number is the
order in which an order must be approved). But, since the boss doesn't
like the dog, either, he didn't fire Joe and doesn't want to take away his
other approver responsibilities. So, how do I go about changing the last
approver -- i.e. those departments in which Joe Bass has the highest
appSEQ number from Joe Bass to Sting Ray? The final result should look
like
Approver
deptID appSeq appName
1 1 Joe Bass
1 2 Suzy Shad
2 1 Tom Trout
2 2 Suzy Shad
2 3 Sting Ray
3 1 Mike Mako
3 2 Don Dolphin
3 3 Tom Tuna
4 1 Suzy Shad
4 2 Sting Ray
...
...
What's got me stumped is that different departments have different numbers
of approvers and that Joe Bass isn't always the last approver.
Thanks.
Randolph "Randy" L. Chrismon
[EMAIL PROTECTED]
