Breaking down status based on a hierarchy

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

General SQL Server Forums

New to SQL Server Programming

Breaking down status based on a hierarchy

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2012-01-23 : 18:38:14

I want to show a third-level overview of how many to-do items are in various statuses (pending declined and completed) for the third level individuals.

In this example there's 2 regional leaders (level3), each with local leaders (level2) and individual team members (level1)

Status "1" is a new or pending to-do item
Status "2" is a declined item
Status "3" is a completed item

Here's what I want the output to look like (I may need to add columns for the final report but it should look as follows):


LEVEL3 | Status 1 | Status 1 | Status 2 | Status 3 |
LEADER | TOTAL    | 2012     | TOTAL    | TOTAL    |
-------|----------|----------|----------|----------|
paul   | 3        | 1        | 1        | 0        |
roger  | 1        | 1        | 1        | 0        |

Here's some sample data similar to my live DB:

CREATE TABLE #items (id int, datestamp datetime, userid int, status int)
INSERT INTO #items (id,datestamp,userid,status) VALUES (1,'12/26/2011',1,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (2,'12/26/2011',2,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (3,'1/2/2012',4,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (4,'1/3/2012',1,2)
INSERT INTO #items (id,datestamp,userid,status) VALUES (5,'1/4/2012',2,1)
INSERT INTO #items (id,datestamp,userid,status) VALUES (6,'1/6/2012',4,2)

CREATE TABLE #users ([id] int, [reportsto] int, [name] varchar(15) )
INSERT INTO #users (id, reportsto, name) VALUES (1, 3, 'jason')
INSERT INTO #users (id, reportsto, name) VALUES (2, 3, 'richard')
INSERT INTO #users (id, reportsto, name) VALUES (3, 6, 'tammy')
INSERT INTO #users (id, reportsto, name) VALUES (4, 5, 'steven')
INSERT INTO #users (id, reportsto, name) VALUES (5, 7, 'gina')
INSERT INTO #users (id, reportsto, name) VALUES (6, 0, 'paul')
INSERT INTO #users (id, reportsto, name) VALUES (7, 0, 'roger')

I have a view called hierarchy that shows me the hierarchy for each level 1 user. Here's the source for the view:

SELECT 	
a.id 'level1',
b.id 'level2',
c.id 'level3'
FROM #users a
INNER JOIN #users b ON a.reportsto = b.id
INNER JOIN #users c ON b.reportsto = c.id

What's the best way to accomplish this? I've run into table/view limits when using a straight query and timeouts when trying to create a cursor that looks through all level3 users and populates a temporary table with an INSERT and multiple UPDATE statements to get the individual counts for each column for each user.

(Just a note on the actual database in case it's relevant: It has about 1000 LEVEL1 users, 50 Level2, and 15 Level 3 users. It will generate about 1400 to-do items per week.)

Thanks in advance! This forum has been a great resource in the past!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 19:51:17

If you have only 3 levels, your existing query can be modified to get the result you are looking for. See my changes in red:

SELECT DISTINCT
	[name],
	Stat1Total,
	Stat2Total,
	Stat3Total
FROM
(
	SELECT a.id 'level1',
		   b.id 'level2',
		   c.id 'level3',
		   c.[name],
		   SUM(CASE WHEN i.STATUS = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY c.id) Stat1Total,
		   SUM(CASE WHEN i.STATUS = 2 THEN 1 ELSE 0 END) OVER(PARTITION BY c.id) Stat2Total,
		   SUM(CASE WHEN i.STATUS = 3 THEN 1 ELSE 0 END) OVER(PARTITION BY c.id) Stat3Total
	FROM   #users a
		   INNER JOIN #users b
				ON  a.reportsto = b.id
		   INNER JOIN #users c
				ON  b.reportsto = c.id
		   LEFT JOIN #items i
				ON  i.userid = a.id OR i.userid = b.id OR i.userid = c.ID
) S

If you could have many levels then you probably would need a recursive query.

BTW, it was a joy to read your posting - you very clearly defined what you are looking for, and included test script that can be copied. Thank you for that.

Now if this query does not give you the right answers, or is too inefficient, then I disown it, I didn't write it!! :)

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2012-01-24 : 13:47:16

I'm glad you liked my post, though I think I left out a critical point... The server is running SQL Server 2000. Doesn't seem to like OVER()

Incorrect syntax near the keyword 'OVER'.

Also, there are more levels to the hierarchy in the actual data. I created the view so that knowing someone's level in the hierarchy, I could pull up everyone who is underneath them. i.e. if I know Paul is a regional leader, I could query him as level3 and return all level1's that match.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-25 : 07:04:32

I am not familiar enough with SQL 2000 to be able to suggest something, especially since you have many levels in your hierarchy - hopefully someone else will.

Joe Celko has a whole book on the topic ( http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202 ) I don't have that book, but it is likely that he has algorithms in it that can map hierarchies without the recursive features available in SQL 2005 and above.

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-11-10 : 15:16:53

I think the issue is resolved. It looks like I only needed to add "SET NOCOUNT ON" to the procedure and it's running fine now.
(Mods Please delete this. I replied to my wrong thread)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources