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.
| Author |
Topic |
|
vani_r14
Starting Member
24 Posts |
 Posted - 2008-06-10 : 04:59:21
|
| Hi allI am currently working on a report and need some help at one point. I have a table called Staff and Staff Levels. The columns in Staff include: Staff no (6 digit), Staffname, Managerno (6digit no), Managername, Startdate.... and few other columns. The columns in stafflevels include -> manager_staff_number (same 6 digit no as above), resource_staff_number (same 6 digit staff #), manager_no (md5 encrypted staff no of the 6 digit one above) and staff_no (encrypted staff no of the above 6 digit no) and two more columns. when i do a join on these two tables I get a the managers coming under 3 levels of senior managent. e.g. If Beth was manager level 1, Tony was manager under beth in level 2, Smith was manager under tony at level 3 and then comes the last level paula manager under smith at level 4. When I do the select statement I get the Paula in 3 levels above (i.e. the result set shows her three times) under each of the above managers and not just ones under Smith which is what I need for the report. Could some one please helpThanksVani |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-10 : 05:28:53
|
| Use MAX() on the levels as you only want the highest level. |
 |
|
|
vani_r14
Starting Member
24 Posts |
Posted - 2008-06-10 : 06:01:44
|
| hi just to expand on before just noticed further requirement is that the report has to display the managers usage and their team's usage when the manager logs in. The sql query I have uses a left outer join on the two tables as there are some managers in the Stafflevels table with some null values. so eg of the select statement I have is select * from stafflevels, staffwhere stafflevels.resource_staff_no left outerjoin staff.staff_noand I get the duplicates. Could the max like mentioned in the above reply help fix this problem. please advisethanksVani |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-10 : 06:30:51
|
quote:
Originally posted by vani_r14
hi just to expand on before just noticed further requirement is that the report has to display the managers usage and their team's usage when the manager logs in. The sql query I have uses a left outer join on the two tables as there are some managers in the Stafflevels table with some null values. so eg of the select statement I have is select * from stafflevels, staffwhere stafflevels.resource_staff_no left outerjoin staff.staff_noand I get the duplicates. Could the max like mentioned in the above reply help fix this problem. please advisethanksVani
you need to group on duplicated fields and take MAX() or MIN() of others. If you want more precise solution post you DDL with some sample data. |
|
|
|
|
|
|
display once