SQL Query to get Completed tasks of particular resource for User:
| SELECT MIL_NAME as
TASKS
FROM USR, ORC, MIL,
PKG, TOS, SCH, OSI
WHERE
OSI.MIL_KEY=MIL.MIL_KEY
AND
OSI.ORC_KEY=ORC.ORC_KEY
AND
ORC.USR_KEY=USR.USR_KEY
AND
OSI.TOS_KEY=TOS.TOS_KEY
AND
OSI.PKG_KEY=PKG.PKG_KEY
AND
OSI.SCH_KEY=SCH.SCH_KEY
AND PKG.PKG_NAME='[Insert Resource Name here]'
AND SCH.SCH_STATUS
='C'
AND USR.USR_LOGIN='[Insert User Login here]'
ORDER BY
OSI.OSI_UPDATE;
Example: To get Completed tasks of AD User resource for TESTUSER user:
SELECT MIL_NAME as
TASKS
FROM USR, ORC, MIL, PKG,
TOS, SCH, OSI
WHERE
OSI.MIL_KEY=MIL.MIL_KEY
AND
OSI.ORC_KEY=ORC.ORC_KEY
AND
ORC.USR_KEY=USR.USR_KEY
AND
OSI.TOS_KEY=TOS.TOS_KEY
AND
OSI.PKG_KEY=PKG.PKG_KEY
AND
OSI.SCH_KEY=SCH.SCH_KEY
AND PKG.PKG_NAME='AD
User'
AND SCH.SCH_STATUS ='C'
AND USR.USR_LOGIN='TESTUSER'
ORDER BY OSI.OSI_UPDATE;
|
SQL Query to get Rejected tasks of particular resource for User:
| SELECT MIL_NAME as
TASKS
FROM USR, ORC,MIL,
PKG, TOS, SCH, OSI
WHERE
OSI.MIL_KEY=MIL.MIL_KEY
AND
OSI.ORC_KEY=ORC.ORC_KEY
AND
ORC.USR_KEY=USR.USR_KEY
AND
OSI.TOS_KEY=TOS.TOS_KEY
AND
OSI.PKG_KEY=PKG.PKG_KEY
AND
OSI.SCH_KEY=SCH.SCH_KEY
AND PKG.PKG_NAME='[Insert Resource Name here]'
AND SCH.SCH_STATUS
='R'
AND USR.USR_LOGIN='[Insert User Login here]'
ORDER BY
OSI.OSI_UPDATE;
Example: To get Rejected tasks of AD User resource for TESTUSER user:
SELECT MIL_NAME as
TASKS
FROM USR, ORC,MIL,
PKG, TOS, SCH, OSI
WHERE
OSI.MIL_KEY=MIL.MIL_KEY
AND
OSI.ORC_KEY=ORC.ORC_KEY
AND
ORC.USR_KEY=USR.USR_KEY
AND
OSI.TOS_KEY=TOS.TOS_KEY
AND
OSI.PKG_KEY=PKG.PKG_KEY
AND
OSI.SCH_KEY=SCH.SCH_KEY
AND PKG.PKG_NAME='AD
User'
AND SCH.SCH_STATUS
='R'
AND USR.USR_LOGIN='TESTUSER'
ORDER BY
OSI.OSI_UPDATE;
|
No comments:
Post a Comment