Best way to create summary data from several existing tables
Hi, during the data load i would like to create a new field based on various user attributes, for example
Table 1 UserID, Role UserA, Admin UserA, User UserB, User UserC, Admin UserC, DBA UserC, User
Table 2
UserID, Class UserA, Base UserB, Super UserC, Super
What i would like to do is create the following additional table
UserID, Priority
Where the Priority is 1, if the total number of User Entries (regardless of role) in table 1 is > 0 AND the Table2 Class is Super,
if not, then if the User Entries table 1 > 0 but class is not super, then set priority to 2, if not then set entry to 3.
Whats the best way to accomplish something like this, the ultimate goal to have a priority assigned to each user based on the data in table 1 and 2 for that user.
