Skip to main content

Number of Pending Processes for Approval of Individuals

SELECT 
OSUSERS.ID,
OSUSERS. FIRSTNAME+' '+OSUSERS. LASTNAME AS FULLNAME,
OSUSERS. EMAIL
LIVEFLOWS. PROCESS,
PROJECTS. CAPTION,
PROJECTS. DESCRIPTION
COUNT(*) AS "REQUESTCOUNT", MULTILANGUAGE, PROJECTS. COLOR, PROJECTS. ICON
FROM
FLOWREQUESTS WITH(NOLOCK)
INNER JOIN LIVEFLOWS WITH(NOLOCK) ON FLOWREQUESTS. PROCESSID = LIVEFLOWS.ID
INNER JOIN FLOWSTATUSES WITH(NOLOCK) ON FLOWSTATUSES. PROCESS=LIVEFLOWS. PROCESS AND FLOWSTATUSES. STATUS=LIVEFLOWS. STATUS AND FLOWSTATUSES. VERSION=LIVEFLOWS. FLOWVERSION
INNER JOIN PROJECTS WITH(NOLOCK) ON LIVEFLOWS. PROCESS=PROJECTS.NAME
INNER JOIN OSUSERS WITH(NOLOCK) ON FLOWREQUESTS. USERID=OSUSERS.ID OR (FLOWREQUESTS. POSITION IN (SELECT ID FROM OSPOSITIONS WHERE USERID = OSUSERS.ID AND STATUS = 1))
WHERE
LIVEFLOWS. DELETED=0 AND
LIVEFLOWS. TEST=0 AND
PROJECTS. STATUS=1 AND
(((FLOWREQUESTS. STATUS=1 AND LIVEFLOWS. FINISHED=0 AND (FLOWREQUESTS. REQUESTTYPE IN (1)) AND ((FLOWREQUESTS. OWNEDBY IS NULL) OR (FLOWREQUESTS. OWNEDBY=FLOWREQUESTS.ID))) OR (FLOWREQUESTS. STATUS=1 AND (FLOWREQUESTS. REQUESTTYPE IN (3)) AND FLOWREQUESTS. STEP```<``````>```1)))
GROUP BY
OSUSERS.ID,OSUSERS. FIRSTNAME+' '+OSUSERS. LASTNAME,OSUSERS. EMAIL,LIVEFLOWS. PROCESS, PROJECTS. CAPTION,PROJECTS. DESCRIPTION, PROJECTS. MULTILANGUAGE, PROJECTS. COLOR, PROJECTS. ICON
ORDER BY FULLNAME