I did not try to re-work or figure out your query but here is one that I use to get a general list of portal users and their latest activity, maybe it will help get you going in the right direction:

DECLARE @PortalID int, @authorized bit;
SET @PortalID = 10;


SELECT U.UserID
, U.Username
--, U.FirstName
--, U.LastName
, U.DisplayName
, U.Email
, CASE UP.IsDeleted
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS IsDeleted
, (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'AccountName' AND PortalID = @PortalID)) AS BusinessName 
, (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'City' AND PortalID = @PortalID)) AS City
, (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Province' AND PortalID = @PortalID)) AS Province
, aU.LastActivityDate
--, UP.CreatedDate
--, UP.Authorised AS Authorzied
FROM USERS AS U
LEFT JOIN UserPortals AS UP 
ON (U.UserId = UP.UserId)
LEFT JOIN dbo.aspnet_Users AS aU
ON (aU.UserName = U.UserName)
WHERE ( UP.PortalId = @PortalId or @PortalId IS NULL )
--AND Authorised = ISNULL(@authorized, Authorised)
ORDER BY U.FirstName + ' ' + U.LastName;

Good luck!


Josh

DECLARE @PortalID int = 1;
DECLARE @value nVarChar(100) = N'Rio%';


 SELECT top (10) 
   U.UserID
 , U.Username
 , U.DisplayName
 , U.Email
 , CASE U.IsDeleted WHEN 1 THEN 'Yes' ELSE 'No' END AS IsDeleted
 , (SELECT PropertyValue FROM vw_Profile WHERE UserId = U.UserId AND PropertyName = 'City' AND PortalID = @PortalID) AS City
 , (SELECT PropertyValue FROM vw_Profile WHERE UserId = U.UserId AND PropertyName = 'Region' AND PortalID = @PortalID) AS Province
 --, U.CreatedDate
 FROM vw_USERS AS U
 WHERE ( U.PortalId = @PortalId or @PortalId IS NULL )
--   AND Authorised = 1
--   AND IsDeleted  = 0
   AND U.UserID IN (SELECT UserID from vw_Profile WHERE PropertyValue Like @value AND PropertyName = 'City' AND PortalID = @PortalID)
 ORDER BY U.DisplayName;

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN