Detailed License Inquiry

SELECT O.ID AS USERID,O.FIRSTNAME + ' ' + LASTNAME AS FULLNAME, O.STATUS AS USERSTATUS,

(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 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.STATUS=1 AND O.ID != 'admin' AND O.ID != 'workflow'