Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Sql Query for user profileSql Query for user profile
Previous
 
Next
New Post
10/20/2011 11:58 AM
 
I'm using the below query for retriving users profile from DNN database,I'm not getting any error but not returning any values also.Any help plz.

SELECT CONVERT(varchar(11), SiteLog.DateTime, 13) As [Date],
vw_Users.FirstName + ' ' + vw_Users.LastName + ' (' + vw_Users.DisplayName + ')' As [Client Name / Display Name], 
Tabs.TabName As [Visited page], UserProfile.PropertyValue AS Country
FROM vw_Users INNER JOIN
SiteLog ON vw_Users.UserId = SiteLog.UserId INNER  JOIN
Tabs ON SiteLog.TabId = Tabs.TabID INNER  JOIN
UserProfile ON vw_Users.UserId = UserProfile.UserID
WHERE (UserProfile.PropertyDefinitionID = 2)
And DateAdd(d,-30,Getdate()) < SiteLog.DateTime
Order By SiteLog.DateTime Desc
 
New Post
10/21/2011 6:08 PM
 
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
 
New Post
2/2/2015 3:41 PM
 
What is the proper way to add a where city = "Value" clause?
 
New Post
2/3/2015 5:05 AM
 

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

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Sql Query for user profileSql Query for user profile


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out