Mssql Server 2008- Query used to model data

Hi, I am trying to drill down to the department level of this query.

The data is modelled on what is called a tree.

At the top of the tree is level 8000, then two subleveles 8300 and 8100. Under 8300 and 8100, there are other parent node names or divisions. These hold department ID. So by doing the following query I am able to get all the people belonging to divisions. That is they own everything under that division.

So if a person is at level 8000, I know he has everything under that division.  If a person is under 8100, then the person owns everything under that. Now say a person is under 8040, he owns everyhting under that.  The latter is defined as having at least 9 departments or more.  Now, if the person is under 8040 and has only 2 departments, I want the latter to appear.

So the following query gets every user that is under 8000 and 8100 and 8300 and any division under these.  That said I do not want to see any users reappear when I go to find the ones that have control of a few deparments.  

Here is the query :
SELECT COUNT(OPR.OPRID+C.PARENT_NODE_NAME)
   ,B.SETID
 , OPR.OPRID
 , C.PARENT_NODE_NAME
 , RL.ROLENAME

  FROM SECURITY_DEPT B
  , TREENODE C
  , DEPARTMENT_TBL D
  , ROLEUSERRL RL
  , TST.dbo.PSOPRDFN OPR
 WHERE D.DEPTID = C.TREE_NODE
   AND C.TREE_NAME = 'DEPT_SECURITY'
   AND RL.ROLEUSER = OPR.OPRID
   AND B.OPRID = OPR.OPRID
   AND C.TREE_NODE_NUM BETWEEN B.TREE_NODE_NUM AND B.TREE_NODE_NUM_END
   AND C.EFFDT = (
 SELECT MAX(C1.EFFDT)
  FROM TREENODE C1
 WHERE C1.TREE_NAME = C.TREE_NAME
   AND C1.EFFDT <= GetDate())
   AND D.EFFDT = (
 SELECT MAX(D1.EFFDT)
  FROM DEPARTMENT_TBL D1
 WHERE D1.DEPTID = D.DEPTID
   AND D1.EFFDT <= GetDate())
   AND B.ACCESS_CD = 'Y'
   AND RL.ROLENAME=EMPLOYEE
   AND RL.ROLEUSER NOT IN (‘EMPL1’, ‘EMPL2’, ‘EMPL3’, ‘EMPL4’, ‘EMPL5’)  
  AND PARENT_NODE_NAME ='8100'
  GROUP BY B.SETID, C.PARENT_NODE_NAME, OPR.OPRID, RL.ROLENAME
  HAVING (COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 6
 
  UNION
 SELECT COUNT(OPR.OPRID+C.PARENT_NODE_NAME)
 , B.SETID
 , OPR.OPRID
 , C.PARENT_NODE_NAME
 , RL.ROLENAME
  FROM SECURITY_DEPT B
  , TREENODE C
  , DEPARTMENT_TBL D
  , ROLEUSERRL RL
  , TST.dbo.PSOPRDFN OPR
 WHERE D.DEPTID = C.TREE_NODE
   AND C.TREE_NAME = 'DEPT_SECURITY'
   AND RL.ROLEUSER = OPR.OPRID
   AND B.OPRID = OPR.OPRID
   AND C.TREE_NODE_NUM BETWEEN B.TREE_NODE_NUM AND B.TREE_NODE_NUM_END
   AND C.EFFDT = (
 SELECT MAX(C1.EFFDT)
  FROM TREENODE C1
 WHERE C1.TREE_NAME = C.TREE_NAME
   AND C1.EFFDT <= GetDate())
   AND D.EFFDT = (
 SELECT MAX(D1.EFFDT)
  FROM DEPARTMENT_TBL D1
 WHERE D1.DEPTID = D.DEPTID
   AND D1.EFFDT <= GetDate())
   AND B.ACCESS_CD = 'Y'
   AND RL.ROLENAME=EMPLOYEE
   AND TREE_LEVEL_NUM=0
   AND RL.ROLEUSER NOT IN (‘EMPL1’, ‘EMPL2’, ‘EMPL3’, ‘EMPL4’, ‘EMPL5’)   /* these are the people who have global access at 8000) */
   AND  PARENT_NODE_NAME ='8100'
  GROUP BY B.SETID, C.PARENT_NODE_NAME, OPR.OPRID, RL.ROLENAME
  HAVING (COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 11
  UNION
 SELECT COUNT(OPR.OPRID+C.PARENT_NODE_NAME)
 , B.SETID
 , OPR.OPRID
 , C.PARENT_NODE_NAME
 , RL.ROLENAME
  FROM SECURITY_DEPT B
  , TREENODE C
  , DEPARTMENT_TBL D
  , ROLEUSERRL RL
  , TST.dbo.PSOPRDFN OPR
 WHERE D.DEPTID = C.TREE_NODE
   AND C.TREE_NAME = 'DEPT_SECURITY'
   AND RL.ROLEUSER = OPR.OPRID
   AND B.OPRID = OPR.OPRID
   AND C.TREE_NODE_NUM BETWEEN B.TREE_NODE_NUM AND B.TREE_NODE_NUM_END
   AND C.EFFDT = (
 SELECT MAX(C1.EFFDT)
  FROM TREENODE C1
 WHERE C1.TREE_NAME = C.TREE_NAME
   AND C1.EFFDT <= GetDate())
   AND D.EFFDT = (
 SELECT MAX(D1.EFFDT)
  FROM DEPARTMENT_TBL D1
 WHERE D1.DEPTID = D.DEPTID
   AND D1.EFFDT <= GetDate())
   AND B.ACCESS_CD = 'Y'
   AND RL.ROLENAME=EMPLOYEE
   AND TREE_LEVEL_NUM=0
   AND RL.ROLEUSER NOT IN (‘EMPL1’, ‘EMPL2’, ‘EMPL3’, ‘EMPL4’,    ‘EMPL5’)   AND PARENT_NODE_NAME ='8300'
  GROUP BY B.SETID, C.PARENT_NODE_NAME, OPR.OPRID, RL.ROLENAME
  HAVING (COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 15
 

UNION   /* this part gives me all divisions under 8100 or 8300)
 SELECT COUNT(OPR.OPRID+C.PARENT_NODE_NAME)
 , B.SETID
 , OPR.OPRID
 , C.PARENT_NODE_NAME
 , RL.ROLENAME
  FROM SECURITY_DEPT B
  , TREENODE C
  , DEPARTMENT_TBL D
  , ROLEUSERRL RL
  , TST.dbo.PSOPRDFN OPR
 WHERE D.DEPTID = C.TREE_NODE
   AND C.TREE_NAME = 'DEPT_SECURITY'
   AND RL.ROLEUSER = OPR.OPRID
   AND B.OPRID = OPR.OPRID
   AND C.TREE_NODE_NUM BETWEEN B.TREE_NODE_NUM AND B.TREE_NODE_NUM_END
   AND C.EFFDT = (
 SELECT MAX(C1.EFFDT)
  FROM TREENODE C1
 WHERE C1.TREE_NAME = C.TREE_NAME
   AND C1.EFFDT <= GetDate())
   AND D.EFFDT = (
 SELECT MAX(D1.EFFDT)
  FROM DEPARTMENT_TBL D1
 WHERE D1.DEPTID = D.DEPTID
   AND D1.EFFDT <= GetDate())
   AND B.ACCESS_CD = 'Y'
   AND RL.ROLENAME=EMPLOYEE
   AND TREE_LEVEL_NUM=0
   AND RL.ROLEUSER NOT IN (‘EMPL1’, ‘EMPL2’, ‘EMPL3’, ‘EMPL4’, ‘EMPL5’
  GROUP BY B.SETID, C.PARENT_NODE_NAME, OPR.OPRID, RL.ROLENAME
  HAVING (COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 4
   AND PARENT_NODE_NAME ='8020'
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 9
   AND PARENT_NODE_NAME ='8040')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 4
   AND PARENT_NODE_NAME ='8110')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 8
   AND PARENT_NODE_NAME ='8190')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 2
   AND PARENT_NODE_NAME ='8250')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 2
   AND PARENT_NODE_NAME ='8279')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 1
   AND PARENT_NODE_NAME ='8302')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 3
   AND PARENT_NODE_NAME ='8310')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 5
   AND PARENT_NODE_NAME ='8400')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 5
   AND PARENT_NODE_NAME ='8450')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 3
   AND PARENT_NODE_NAME ='8490')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 3
   AND PARENT_NODE_NAME ='8520')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 5
   AND PARENT_NODE_NAME ='8574')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 6
   AND PARENT_NODE_NAME ='8614')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 7
   AND PARENT_NODE_NAME ='8690')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 7
   AND PARENT_NODE_NAME ='8700')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 2
   AND PARENT_NODE_NAME ='8720')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 9
   AND PARENT_NODE_NAME ='8730')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 18
   AND PARENT_NODE_NAME ='8810')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 35
   AND PARENT_NODE_NAME ='8850')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 3
   AND PARENT_NODE_NAME ='8859')
    OR ((COUNT(OPR.OPRID+C.PARENT_NODE_NAME)) >= 22
   AND PARENT_NODE_NAME ='8880')


 Union ??????  Now here I want everyone who has control of deparments that is there counts are less then above.  Ie Someone in 8040 might only have department nodes 8050 and 8080 but the latter are not devisions.  The deparments are referred to as Tree_Nodes

How can I model this ?

Sincerely,
Nigluc

BrockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
That sounds like a case for a recursive Common Table Expression (CTE).

Recursive Queries Using Common Table Expressions
http://msdn.microsoft.com/en-us/library/ms186243.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
please have another go at describing what you problem is....

showing us some example data would assist.

it is not at all clear what output you want...

so tell us what you "expect" from the example data...

We know what a tree is... its a very common data structure...

as thelearnedone suggests often  recursive sql is used to navigate a tree structure...

your own sql is confusing.. please explain why all the user exclusions and what the having counts are attempting to achieve..
also please clarify how the date processing is supposed to work.

 

0
BrockAuthor Commented:
Hi,

The data is based on a tree. [people are not attached to this structure, we have a custom bolton that assign people to nodes]

Node 8000 - level 0 - person gets access to everything
Node 8100- level 1 - person gets acess to everything under this
Node 8300 - level 1- person gets access to everyhting under this
Node 8040- level 1 person gets access to 8050,8041,8060,8080

There is a tree-node-num and tree-node-end.

It is not a true recursive model where you would have the level 1, 2, 3. And the you can perform the technique suggested here. This is know as a peoplesoft tree. You can look up the queries for this.

So what I was doing was tying to get the people way down on the tree who were a couple of departments and not incharge of a division. So people that were at a division level, I did a count of the departments, so that people who were at the count or exceeded it were at the division.  Then to get the people at the department level, there I added another union at the very end that asked for tree nodes instead of parent nodes and excluded the all the people that were at the divisional level.

I was assigned this task because our in-house gurus could not do it. So I was under the gun to load tables behind the scene.  I automated the load and I verified the data. The sad thing is that they are simply going to move this data (done in  test ) to production instead of running the program in live.

Well, thank you for inquiring further,

Happy Holidays,

Nigluc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.