• State Verified Answer
  • Replies 5 replies
  • Subscribers 93 subscribers
  • Views 2034 views
  • Users 0 members are here
Options
Related

Employees for whom a request can be placed

paul love
over 4 years ago

Hi All,

Getting myself a little stuck here - just wondering if someone can help with a quick solution.

I am trying to update the configuration in IT Shop for "Employees for whom a request can be placed" - in theory my requirements are simple:

  • If you are a manager, then you can place a request for your direct reports
  • If you are in AERole = 'helpdesk', then you can place a request for anyone

For some reason, I don't seem to be able to get the SQL quite right.  Tried a few different CASE statements, but it's not working.... does anyone have a simple SQL that I can use?

Thanks

Paul

  • 0 over 4 years ago

    Hi,

    I was looking into how this can be done, but because it returns a list of Employees, it might be best to return the list from a view, as it does for direct reports currently.

    E.g. query this view using %useruid%.  That would creating the view based on PersonInAERole.

    But that idea in itself would not be the most efficient...I'll keep working on it.

    Trevor

  • 0 over 4 years ago in reply to Trevor Pike

    Thanks - not sure how I would do that in a view - I want to have helpdesk being able to request for EVERYONE... but managers able to request for direct reports... so I need some kind of case or if statement in there somewhere?!

  • 0 over 4 years ago in reply to paul love

    What I was referring to is the default:

    "uid_person in ( select uid_person from QER_VEditEmployee where uid_personhead = '%useruid%')"

    That uses a view, QER_VEditEmployee.  Returns all Employees that the logged in user is manager for.

    For an AERole, you would need to query PersonInAERole to determine whether the logged in user is a member of the Helpdesk role.  That's one part of it.  But then how to display all Employees in that case?  But a view wouldn't really help here, because it wouldn't be a one to one relationship.

    The query would just need a join or two.

    Trevor

  • +1 over 4 years ago

    I do not know if this is the optimal solution but it should do the trick (Assuming that the original condition including the mangers but not restricted to is fine).

    (1=1 AND Exists (Select 1 from PersonInAERole Where UID_Person = '%useruid%' and UID_AERole = 'AOB-AEROLE-ADMIN'))
OR
(uid_person in ( select uid_person from QER_VEditEmployee where uid_personhead = '%useruid%'))

  • 0 over 4 years ago in reply to Markus Weiss-Ehlers

    That's great! Thanks Markus.... I think I was trying to overthink things, this works like a charm