Active Users

Overview

Business occasionally request a list of all active QUAD users for specific role types. Scripts may need to be modified on a case by case basis depending on their needs.

Script 1

General script for selecting all active users

Modify RoleTypeNo on c p11_TeamMember join to get the specified RoleType

USE [Core_CEM]
GO

SELECT DISTINCT d.FullName AS FullName, e.LookupTitle AS RoleType, 
    d.NTID AS LANID, d.EmployeeID AS EEID, f.LookupTitle AS [Location], 
    g.LookupTitle AS [Site], b.TeamName
FROM smart.p11_Sample a
    INNER JOIN smart.p11_Team b ON a.SampleNo = b.SampleNo
    INNER JOIN smart.p11_TeamMember c ON b.TeamNo = c.TeamNo AND RoleTypeNo IN (1,9) -- QR, Business Owner
    INNER JOIN smart.p11_UserList d ON c.UserNo = d.UserNo
    INNER JOIN smart.p11_CodeList e ON c.RoleTypeNo = e.LookupNo AND e.LookupCategory = 'RoleType'
    INNER JOIN smart.p11_CodeList f ON b.LocationNo = f.LookupNo AND f.LookupCategory = 'Location'
    INNER JOIN smart.p11_CodeList g ON b.SiteNo = g.LookupNo AND g.LookupCategory = 'Site'
WHERE a.CurrentStatus = 1 AND a.Deleted = 0
    AND b.CurrentStatus = 1 AND b.Deleted = 0
    AND c.CurrentStatus = 1 AND c.Deleted = 0
    AND d.CurrentStatus = 1 AND d.Deleted = 0
ORDER BY FullName

Script 2

Example script used to select users in a specific group of samples

Modify RoleTypeNo on c p11_TeamMember join to get the specified RoleType
Modify SampleGroupNo, SampleNo in the where clause to get active userlist from specified Samples.

USE [Core_CEM]
GO

SELECT DISTINCT d.FullName AS FullName, e.LookupTitle AS RoleType, 
    d.NTID as LANID, d.EmployeeID AS EEID, f.LookupTitle AS [Location], 
    g.LookupTitle AS [Site], b.TeamName, h.LookupTitle AS SampleGroup, a.SampleTitle
FROM smart.p11_Sample a
    INNER JOIN smart.p11_Team b ON a.SampleNo = b.SampleNo
    INNER JOIN smart.p11_TeamMember c ON b.TeamNo = c.TeamNo AND RoleTypeNo IN (9,8,2) -- Business Owner, BO Manager, Back Up BO Manager
    INNER JOIN smart.p11_UserList d ON c.UserNo = d.UserNo
    INNER JOIN smart.p11_CodeList e ON c.RoleTypeNo = e.LookupNo AND e.LookupCategory = 'RoleType'
    INNER JOIN smart.p11_CodeList f ON b.LocationNo = f.LookupNo AND f.LookupCategory = 'Location'
    INNER JOIN smart.p11_CodeList g ON b.SiteNo = g.LookupNo AND g.LookupCategory = 'Site'
    INNER JOIN smart.p11_CodeList h ON a.SampleGroupNo = h.LookupNo AND h.LookupCategory = 'Sample Group'
WHERE SampleGroupNo IN (1,2) -- Inspection, Measurement
    AND a.SampleNo IN 
        (9, 1, 2, 4, 10, 3, 8, 62,
        41, 6, 5, 7, 17, 14, 18, 15, 16)
    AND a.CurrentStatus = 1 AND a.Deleted = 0
    AND b.CurrentStatus = 1 AND b.Deleted = 0
    AND c.CurrentStatus = 1 AND c.Deleted = 0
    AND d.CurrentStatus = 1 AND d.Deleted = 0
ORDER BY FullName

Script 3

Example script used to select users that do not have Location, Vendor Site fiel selection in Setup User table

USE [Core_CEM]
GO
SELECT DISTINCT i.LookUpTitle AS SampleTitle, d.LastName, d.FirstName,
    d.EmployeeID AS EEID, d.NTID AS LANID, e.LookUpTitle AS RoleType,
    b.TeamName, d.LocationNo, d.VendorNo, d.VendorSiteNo
FROM smart.p11_Sample a
    INNER JOIN smart.p11_Team b ON a.SampleNo = b.SampleNo
    INNER JOIN smart.p11_TeamMember c ON  b.TeamNo = c.TeamNo AND RoleTypeNo IN (1,9) -- QR, Business Owner
    INNER JOIN smart.p11_UserList d ON c.UserNo = d.UserNo
    INNER JOIN smart.p11_CodeList e ON c.RoleTypeNo = e.LookUpNo AND e.LookupCategory = 'RoleType'
    INNER JOIN smart.p11_CodeList f ON a.SampleGroupNo = i.LookUpNo AND i.LookUpCategory = 'Sample Group'
WHERE a.CurrentStatus = 1 AND a.Deleted = 0
    AND b.CurrentStatus = 1 AND b.Deleted = 0
    AND c.CurrentStatus = 1 AND c.Deleted = 0
    AND d.CurrentStatus = 1 AND d.Deleted = 0 AND d.LocationNo IS NULL
ORDER BY LastName