Menu
Reply To Topic Topic: Useful DNN SQL Scripts
:
Posted By Deleted User on 10/2/2018 12:28 PM
Get the people in a list of named roles, including profile properties. DNN 8.0.4 SELECT r.Rolename AS 'Program Position', u.FirstName + ' ' + upd.MiddleName + ' ' + u.LastName AS 'Name', upd.[Street] + '<br />' + upd.[City] + ', ' + upd.[Region] + ' ' + upd.PostalCode AS Address, u.Email FROM dbo.Users as u INNER JOIN dbo.UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN dbo.Roles AS r ON ur.RoleID = r.RoleID INNER JOIN RoleGroups rg on r.RoleGroupID = rg.RoleGroupID LEFT OUTER JOIN (SELECT up.UserID, MAX(CASE WHEN ppd.PropertyName = 'Street' THEN up.PropertyValue ELSE '' END) AS 'Street', MAX(CASE WHEN ppd.PropertyName = 'City' THEN up.PropertyValue ELSE '' END) AS 'City', (SELECT L.[Value] FROM [dbo].[Lists] L WHERE L.EntryID = (SELECT MAX(CASE WHEN ppd.PropertyName = 'Region' THEN up.PropertyValue ELSE '' END))) AS 'Region', MAX(CASE WHEN ppd.PropertyName = 'PostalCode' THEN up.PropertyValue ELSE '' END) AS 'PostalCode', MAX(CASE WHEN ppd.PropertyName = 'MiddleName' THEN up.PropertyValue ELSE '' END) AS 'MiddleName' FROM dbo.UserProfile AS up INNER JOIN dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID --AND ppd.PortalID = 0 GROUP BY up.UserID) AS upd ON u.UserID = upd.UserID ORDER BY CAST(u.Lastname as varchar(3)) ASC
Username: 
Security Code:
CAPTCHA image
Enter the code shown above in the box below.
Subject:
RE: Useful DNN SQL Scripts
Message:
Smilies

Submit

Cancel
Subscribe:
Topic Review