SELECT O.ID AS USERID,O.FIRSTNAME + ' ' + O.LASTNAME AS 'FULLNAME',
CASE WHEN O.STATUS = 1 THEN 'Active' WHEN O.STATUS = 0 THEN 'Passive' END AS 'USERSTATUS',
(SELECT TOP 1 DESCRIPTION FROM OSPROFESSIONS WHERE ID = O.PROFESSION) AS 'PROFESSION',
(SELECT COUNT(USERID) FROM FLOWREQUESTS WHERE USERID = O.ID AND REQUESTTYPE = '5' ) AS 'FLOWSTART',
(SELECT COUNT(USERID) FROM FLOWREQUESTS WHERE USERID = O.ID AND REQUESTTYPE = '3' ) AS 'FLOWSTARTER',
(SELECT COUNT(USERID) FROM FLOWREQUESTS WHERE USERID = O.ID AND REQUESTTYPE = '1' ) AS 'FLOWREQUEST',
(SELECT COUNT(USERID) FROM FLOWREQUESTS WHERE USERID = O.ID AND REQUESTTYPE = '2' ) AS 'FLOWNOTIFICATION',
(SELECT COUNT(USERID) FROM DOCUMENTS WHERE USERID = O.ID) AS 'USERDOCUMENTS',
(SELECT COUNT(CREATORUSER) FROM FSFILES WHERE CREATORUSER = O.ID) AS 'USERCREATEDFILES',
(SELECT COUNT(FROMUSERID) FROM MESSAGES WHERE FROMUSERID = O.ID) AS 'USERMESSAGES',
(SELECT COUNT(TOUSERID) FROM MESSAGEREQUESTS WHERE TOUSERID = O.ID) AS 'USERMESSAGEREQUESTS'
FROM
(
(SELECT USERID FROM DOCUMENTS GROUP BY USERID)
UNION
(SELECT USERID FROM FLOWREQUESTS WHERE STEP ```>```1 GROUP BY USERID)
UNION
(SELECT CREATORUSER AS USERID FROM FSFILES GROUP BY CREATORUSER)
UNION
(SELECT FROMUSERID FROM MESSAGES GROUP BY FROMUSERID)
UNION
(SELECT TOUSERID FROM MESSAGEREQUESTS GROUP BY TOUSERID)
) T INNER JOIN OSUSERS O ON T.USERID = O.ID
WHERE O.ID != 'admin' AND O.ID != 'workflow' ORDER BY FULLNAME ASC