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
