Advanced Analytics, 1st Line Support Status

[Josh Bridgens]

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

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

 

[Victor]

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...

[Josh Bridgens]

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

[Josh Bridgens]

@Victor Nevermind, its working! I think I need some sleep.

 

I Really appreciate the help.

 

Josh

[Victor]

@Josh Bridgens sleep is overrated   

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...

[Victor]

@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  

[Josh Bridgens]

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...

 

[Josh Bridgens]

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

[Josh Bridgens]

@Victor Nevermind - figured out what I was doing wrong, the Custom Fields are in the h_itsm_requests table, got it working

 

Josh

[Victor]

@Josh Bridgens - sorry, just catching up on forums now, sorry I missed this. Happy to hear you figured it out though