Query the number of user of a given space

Ramy

Hi All,

Is there a way to get the mount of user(s) of a given space within one SQL query? some of the spaces they have only users and some of them they have user plus group.

Thanks

Ramy

Here is my solution in case if someone facing the same problem. Any improvement are very welcome!

This query will return a list of all spaces and relevant information...

SELECT DISTINCT
c.SPACEID AS ID
, s.SPACENAME AS 'Space Name'
, ISNULL(cus.first_name, 'UnkownUser') AS 'Admin [First Name]'
, ISNULL(cus.last_name, ums.username) AS 'Admin [Last Name]'
, ISNULL(cus.lower_email_address, 'UnkownEmail') AS 'Admin [Email]'
, CONVERT(varchar, s.CREATIONDATE, 104) AS 'Space Created'
, CONVERT(varchar, s.LASTMODDATE, 104) AS 'Space Last Update'
, c.TITLE AS 'Last Mod [Content]'
, ISNULL(cuc.first_name, 'UnkownUser') AS 'Last Mod [First Name]'
, ISNULL(cuc.last_name, umc.username) AS 'Last Mod [Last Name]'
, cuc.lower_email_address AS 'Last Mod [Email]'
, CONVERT(varchar, clmd.contentLastMod, 104) AS 'Last Mod [Content]'
, YEAR(clmd.contentLastMod) AS 'Last Mod [Year]'
, spuc.MemeberCount AS 'No Memeber'
, ISNULL(gmc.GroupMemberCount, '0') AS 'No Group Memeber'
FROM dbo.CONTENT AS c
LEFT JOIN dbo.SPACES AS s ON c.SPACEID = s.SPACEID
LEFT JOIN dbo.user_mapping AS ums ON s.CREATOR = ums.user_key
LEFT JOIN dbo.cwd_user AS cus ON ums.lower_username = cus.lower_user_name
LEFT JOIN dbo.user_mapping AS umc ON c.LASTMODIFIER = umc.user_key
LEFT JOIN dbo.cwd_user AS cuc ON umc.lower_username = cuc.lower_user_name
INNER JOIN (
SELECT MAX(ca.LASTMODDATE) AS contentLastMod, ca.SPACEID
FROM dbo.CONTENT AS ca
GROUP BY ca.SPACEID
) AS clmd ON c.SPACEID = clmd.SPACEID AND c.LASTMODDATE = clmd.contentLastMod
INNER JOIN (
SELECT COUNT(spuc.PERMUSERNAME) AS MemeberCount, spuc.SPACEID
FROM (
SELECT DISTINCT PERMUSERNAME, sp.SPACEID
FROM dbo.SPACEPERMISSIONS AS sp
WHERE sp.PERMUSERNAME IS NOT NULL
) AS spuc
GROUP BY spuc.SPACEID
) AS spuc ON c.SPACEID = spuc.SPACEID
LEFT JOIN (
SELECT ccms.GroupMemberCount, sp.SPACEID
FROM dbo.cwd_group AS cg
LEFT JOIN SPACEPERMISSIONS AS sp ON sp.PERMGROUPNAME = cg.group_name
LEFT JOIN (
SELECT COUNT(*) AS GroupMemberCount, parent_id
FROM dbo.cwd_membership
GROUP BY parent_id
) AS ccms ON cg.id = ccms.parent_id
WHERE cg.directory_id = <directory_id>
) AS gmc ON c.SPACEID = gmc.SPACEID
WHERE s.SPACETYPE = 'global'
AND s.SPACESTATUS = 'CURRENT'
ORDER BY c.SPACEID;