Maximo Open Forum

I am trying to set up a KPI that tracks the number of logins for the past 7 days for all users that are part of the persongroup 'HVAC'. I have been trying to figure it out but I keep getting errors. I have been attempting the following SELECT & WHERE clauses, can you see where it goes wrong?

SELECT:

SELECT COUNT(*) session_count FROM logintracking

WHERE:

WHERE logintracking.attemptdate >= TRUNC(SYSDATE)-7 AND EXISTS(SELECT 1 FROM maxuser JOIN person ON person.personid = maxuser.personid JOIN persongroupteam ON persongroupteam.personid = person.personid WHERE maxuser.userid = logintracking.userid AND persongroupteam.persongroup = 'HVAC')

Happy to hear any feedback, thanks in advance!

It looks like the persongroupteam table does not have a personid attribute. Try using persongroupteam.respparty

This seems to have fixed the problem, thank you so much!

I use SQLServer, and the following works, this might be able to be written more efficiently though?
select attemptdate,attemptresult,userid,name from logintracking
where attemptdate > getdate()-7 and attemptresult = 'LOGIN'
and userid in
(select userid from maxuser where personid in
(select resppartygroup from persongroupteam where persongroup = 'HVAC'));