Link to home
Start Free TrialLog in
Avatar of Brock
BrockFlag for Canada

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
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.

 

Avatar of Brock
Brock
Flag of Canada image

ASKER

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