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 |
|
hariharanpalani
Starting Member
2 Posts |
 Posted - 2007-07-10 : 13:31:49
|
| I am having an employee table. It's data are hierarchial in manner. This hierarchy is based employee and manager relation.Each level in the hierarchy is defined as layer. i.e.Root node is defined as Layer 1, followed by layer 2 and so on. Level is another factor here. This was defined using some criteria like salary in which salary between 1000 to 2000 is defined as level 1 and 2000 to 30000 as level 2 and so on. As a result, my table will look like this----------------------------------------------------------------------------------------Employee ID Manager Employee ID Layer Level salary-------------------------------------------------------------------------------------------10000 NULL 1 1 100010001 10000 2 2 200010002 10000 2 3 300010003 10000 2 4 400010004 10001 3 5 500010005 10001 3 6 600010006 10002 3 7 700010007 10002 3 8 800010008 10002 3 9 900010009 10004 4 10 1000010010 10004 4 10 10000From this, i have to create a layer/level metrix output whose output looks like this--------------------------------------------------------------------------------------------------------------------Layer 1 2 3 4 5 6 7 8 9 10 Median/Level---------------------------------------------------------------------------------------------------------------------1 1 0 0 0 0 0 0 0 0 0 32 0 1 1 1 0 0 0 0 0 0 2.53 0 0 0 0 1 1 1 1 1 0 24 0 0 0 0 0 0 0 0 0 2 0In the resultant table, finally we have a median Column. This should be calculate as follows.1. For each employee in each layer, calculate number of employees under him whose count greater than zero. For e.g. for layer 2(10001,10002,10003) under each managernumber of employees whose count greater than zero are(10001 - 2, 10002 - 3).2. Count the records for each layer. If count is in even number, take the average of middle to records. For layer 2, (2+3)/2 = 2.5 If count is odd, take the middle value.Can anyone provide me the SP for this.hariharanpalani |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-10 : 13:43:41
|
| Yes, I could provide a SP for that, but I think you will benefit more from learning yourself.Have a look at CTE in books online, especially recursive ones. With the flat output, you can group and average the data you want.Please remember this is a forum where we help out for free. If you need immediate help, consider hiring a professional.Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
|
|
Need help on SP