Jump to content
Josh Bridgens

Advanced Analytics, 1st Line Support Status

Recommended Posts

Good Morning All,

 

I am currently creating a Private "wallboard" for our Co-Ordinator so that they can see the information they want fairly quickly.

I am struggling with the Request "I want to see the availability status of 1st Line at all times"

 

I tried to get round this myself before coming to the forums, where I found someone had done this, but it pulls through a list of all teams.

 

The current SQL Query is:

 

SELECT a.h_name as User_Name, b.h_name as Current_Status FROM h_sys_accounts a
LEFT JOIN h_sys_accounts_status b
ON a.h_avail_status = b.h_status
WHERE a.h_class = 1
ORDER BY a.h_name ASC

image.png.21b0427d957d754485d2f7d62f6907ac.png

I have tried numerous attempts of filtering this down to just "1st Line Support" but I am not having any luck.

 

Can anyone help?

 

Josh

 

Share this post


Link to post
Share on other sites
1 minute ago, Josh Bridgens said:

I have tried numerous attempts of filtering this down to just "1st Line Support" but I am not having any luck.

@Josh Bridgens how did you try to filter this? :) I don't see any filter for teams on the above query...

You would need something like this:

SELECT a.h_name as User_Name, b.h_name as Current_Status FROM h_sys_accounts a
LEFT JOIN h_sys_accounts_status b ON a.h_avail_status = b.h_status
LEFT JOIN h_sys_account_groups c ON c.h_user_id = a.h_user_id
WHERE a.h_class = 1
AND c.h_group_id = '<First_Line_Team ID>'
ORDER BY a.h_name ASC

replace <First_Line_Team ID> with the ID of the first line team...

  • Thanks 1

Share this post


Link to post
Share on other sites
Just now, Victor said:

how did you try to filter this? :) I don't see any filter for teams on the above query...

Deleted any of the attempts I made, however After looking at yours I think I've realised my mistake....

I was attempting to pull in clauses from h_itsm_requests, with no reference to it at any point (h_fk_team_name)....Whereas yours pulls through c.h_group_id...

When you say "ID of the first line team".. I have tried adding "1st Line Support" which doesn't seem to work, so I tried "prospectsservices/corporateservicesandtransformation/ict/firstlinesupport/"

 

Which also seems to fail :(

I think the lack of sleep is having some effects on my thinking today :blink:

Share this post


Link to post
Share on other sites

@Josh Bridgens sleep is overrated :P :D 

It might be we are not using the correct ID... so, let's try this first, to see what the ID is:

SELECT a.h_name as User_Name, b.h_name as Current_Status, c.h_group_id as Team_ID
FROM h_sys_accounts a
LEFT JOIN h_sys_accounts_status b ON a.h_avail_status = b.h_status
LEFT JOIN h_sys_account_groups c ON c.h_user_id = a.h_user_id
WHERE a.h_class = 1
ORDER BY a.h_name ASC

You should now see the Team ID in the third column.. make a note of the ID displayed there (the one for the first line team) and use it in the previous SQL query...

Share this post


Link to post
Share on other sites

@Josh Bridgens our posts crossed paths :) ... Happy to hear is sorted :) ... I'll keep my reply there in case anyone else reads this and gets stuck :) 

  • Like 1

Share this post


Link to post
Share on other sites
Just now, Victor said:

our posts crossed paths :) ... Happy to hear is sorted :) ... I'll keep my reply there in case anyone else reads this and gets stuck :) 

Once again, I really appreciate it - consider this one Solved! :)

I think the forums need a Victor Signal...

 

Image result for anonymous bat signal

  • Haha 3

Share this post


Link to post
Share on other sites

Hi @Victor, I hope you don't mind me dragging up old problems like this, however I've tried applying the knowledge here to a widget I'm creating.. 

 

I need to pull through the field h_custom_22 from a Pro Cap into the widget but I'm failing miserably.

 

SELECT a.h_summary AS Summary FROM h_itsm_requests, b.h_custom_22 AS Scheduled_Date FROM h_itsm_questions

LEFT JOIN h_itsm_questions b ON h_entity_ref = h_pk_reference

WHERE h_status IN ('status.open','status.new','status.onhold')
AND h_requesttype = 'Change'

 

This is probably completely far from the mark...

Any Help? 

 

Josh

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×