Consulta de usuarios activos

select userid, name, status, fecha last_log, Vigencia_Ini, groupname, description, emailaddress, location

FROM

(select maxuser.userid as userid, pr.displayname as name, maxuser.status as status, mxlogin.fecha as fecha, pr.location as location, maxstatus.Vigencia_Ini , case when groupuser.groupname is null then 'EVERYONE' else groupuser.groupname end as groupname, case when groupuser.description is null then 'SR - Solicitante de Requerimientos' else groupuser.description end as description, email.emailaddress

from maxuser

left join (select userid, fecha from (select userid, max(ATTEMPTDATE) as Fecha from logintracking where attemptresult = 'LOGIN' group by userid) group by userid, fecha) mxlogin on mxlogin.userid=maxuser.userid

left join PERSON pr on maxuser.PERSONID = pr.PERSONID

left join (select min(changedate)Vigencia_Ini, userid from MAXUSERSTATUS where status = 'ACTIVE' group by userid) maxstatus on maxuser.userid = maxstatus.userid

left join (select gu.groupname, gu.userid, mxg.description from GROUPUSER gu left join MAXGROUP mxg on mxg.groupname = gu.groupname where gu.groupname not in ('EVERYONE', 'LABAPPR','Stop')) groupuser on  maxuser.userid = groupuser.userid

left join (select emailaddress, personid from email) email on email.personid = maxuser.userid

)

where status in ('ACTIVE', 'BLOCKED') and userid not in ('USER','MAXADMIN','DEFLT', 'DEFLTREG', 'ADMIN', 'SUPERU', 'GUEST', 'SYSADM')

order by userid