How to extract all the role information for every user from a QC/ALM project

Posted: January 24, 2014 in Uncategorized


SELECT m.Project Project,
m.usname US_USERNAME,
ISNULL(m.Admin,0) Admin,
ISNULL(m.Bus_Owner,0) Bus_Owner,
ISNULL (m.Bus_Reviewer,0) Bus_Reviewer,
ISNULL(m.Defect_Resolver,0) Defect_Resolver,
ISNULL(m.Inactive ,0) Inactive ,
ISNULL(m.Lead_Test_Mgr,0) Lead_Test_Mgr,
ISNULL(m.Quality,0) Quality,
ISNULL(m.Req_Author,0) Req_Author,
ISNULL(m.Tech_Reviewer,0) Tech_Reviewer,
ISNULL(m.Test_Analyst,0) Test_Analyst,
ISNULL(m.Test_Mgr,0) Test_Mgr,
ISNULL(m.Tester,0) Tester,
ISNULL(m.View_Only,0) View_Only

FROM
(
SELECT DB_NAME() AS Project,
u.US_USERNAME usname,
MAX(TDAdmin) Admin,
MAX(Business_Owner) as Bus_Owner,
MAX(Business_Reviewer) as Bus_Reviewer,
MAX(Defect_Resolver) as Defect_Resolver,
MAX(Inactive) Inactive ,
MAX(Lead_Test_Manager) as Lead_Test_Mgr,
MAX(Quality) Quality,
MAX(Requirement_Author) as Req_Author,
MAX(Technical_Reviewer) as Tech_Reviewer,
MAX(Test_Analyst) as Test_Analyst,
MAX(Test_Manager) as Test_Mgr,
MAX(Tester) Tester,
MAX(View_Only) as View_Only

FROM
(
SELECT DISTINCT us_username,
CASE gr_Group_Name
WHEN 'TDAdmin' THEN 1
END TDAdmin,
CASE gr_Group_Name
WHEN 'QATester' THEN 1
END QATester,
CASE gr_Group_Name
WHEN 'Project Manager' THEN 1
END Project_Manager,
CASE gr_Group_Name
WHEN 'Developer' THEN 1
END Developer,
CASE gr_Group_Name
WHEN 'Viewer' THEN 1
END Viewer,
CASE gr_Group_Name
WHEN 'Quality' THEN 1
END Quality,
CASE gr_Group_Name
WHEN 'Test Analyst' THEN 1
END Test_Analyst,
CASE gr_Group_Name
WHEN 'Test Manager' THEN 1
END Test_Manager,
CASE gr_Group_Name
WHEN 'Tester' THEN 1
END Tester,
CASE gr_Group_Name
WHEN 'Defect Resolver' THEN 1
END Defect_Resolver,
CASE gr_Group_Name
WHEN 'Inactive' THEN 1
END Inactive,
CASE gr_Group_Name
WHEN 'Business Owner' THEN 1
END Business_Owner,
CASE gr_Group_Name
WHEN 'Business Reviewer' THEN 1
END Business_Reviewer,
CASE gr_Group_Name
WHEN 'eApprove Administrator' THEN 1
END eApprove_Administrator,
CASE gr_Group_Name
WHEN 'Requirement Author' THEN 1
END Requirement_Author,
CASE gr_Group_Name
WHEN 'Lead Test Manager' THEN 1
END Lead_Test_Manager,
CASE gr_Group_Name
WHEN 'View Only' THEN 1
END View_Only,
CASE gr_Group_Name
WHEN 'Technical Reviewer' THEN 1
END Technical_Reviewer

FROM td.USERS,td.GROUPS
WHERE SUBSTRING (US_GROUP, GR_GROUP_ID+1, 1) = '1'
) u

GROUP BY u.US_USERNAME
)m

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s