Weigthing/Grading - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Weigthing/Grading

Author

Topic

neilr
Starting Member

3 Posts

Posted - 2011-10-27 : 05:25:29

Hi All,

Hopefully this is in the correct place as this is my first post on this forum.

I'm having a bit of an issue with some work I am trying to do which requires that I seperate some data using a persons role. The problem here is that because of the way the system is written each user has all roles that fall 'underneath' their level plus the role for the current level.

Consider the following data;

Name Roles
Stuart Manager
Stuart Assistant
Stuart Associate
Phil Assistant
Phil Associate
Mark Associate

As you can see the manager has all the roles below that level. I need some way of differentiating between the manager(Stuart), the assistant(Phil) and the associate (Mark).

Ideally I would like to do something like this...
Add a new column and give it a value where
Everyone with the Manager role is value 1
Everyone with the assitant role (but not the manager role) has 2
Everyone with the associate role (but not the assitant role) has 3.

I've thought of using exists but that won't work... currently my bright idea is to write an individualy query for each role/ use temporary tables to store the ids of the staff members at each level and exclude those from the query looking for those at the level below.

Is there an easier way to do this all in one query by saying if a certain role is present then this is hierarchically greater than the rest and therfore that persons actual role?!?!

Sorry for the waffle... it probabaly makes no sense. But any questions/ideas would be most welcome.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-27 : 05:36:38

Imaging you have a new column and it is populated with the values you have mentioned by using an update statement...

What will happen if a new name and/or a new role is added?
Do you have to run that update statement all the time a new entry is added?

Why do you need that stuff / what is the underlying problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

neilr
Starting Member

3 Posts

Posted - 2011-10-27 : 05:55:27

webfred,

The underlying issue is that I have been asked to produce a report ordered by 'Real World' job type. So in the above example Stuart is a amanger so I need him to appear in the 'managers' section of the report, Phil in the 'Assistants' section.

Because of the way the roles are given there isn't a one to one map... so I can't just say if the assistant role is present then they are an assitant.

I'm just trying to come up with a way of doing this in sql rather than excel.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 06:07:20

i think you may be better off adding a mapping table like


Role    RoleID
Manager  1
Assistant 2
Associate 3 
...

then use a join like


SELECT t.Name, MIN(m.Roles) AS Role
FROM YourTable t
INNER JOIN AboveMappingTable m
ON m.Role = t.Roles
GROUP BY t.Name

this will give correct role of person and you can use it for filtering in your reports

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neilr
Starting Member

3 Posts

Posted - 2011-10-27 : 06:20:16

Visakh.

You are a hero... so simple it hurts (though that could be the cafeine headache i've given myself thinking about this).

I will give it a crack and let you know... will need some slight modification as there are multiple roles at the same level (but I can group these as they all have a similar role name).

Cheers

Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 06:22:39

ok... no probs
try it and come back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-27 : 07:29:02

So it was a good idea to tell us the underlying issue

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources