Need help on database view to join sc_req_item, sc_task and task_sla

[Lisa Baremore]

Hi everyone

We have SLA's set at the RITM level but assignment group and assigned to are at the Task level. I need to provide a way for users to pull reports on the SLA stats filtered by assignment group and assigned to. 

There is not OOTB database view that provides this so I thought I could create one that joins sc_req_item and sc_task and task_sla to accomplish this.

However I am not getting any results. I am doing something wrong and I am not sure what. Can anyone look over the way I have created this and advise me? I don't get any errors, it's just that I am not getting any records returned (and I do have plenty of records that should be returned):

I have experimented with the order (swapped 300 with 200) and still no results.

Thanks in advance!!!

[Brian Lancaster · Accepted solution]

Since the SLA is on the RITM Level shouldn't your where clase on sc_task be tasksla_task=sctask_request_item as passing the sys_id of the task would yield no results as there are no SLA on the task.

[Lisa Baremore]

Hi Brian, thank you! That may have worked... having problems getting results before timing out. I did select certain fields on 2 of the tables so that it's not trying to return all fields of all tables in the view but I may not have cut that down enough.

Thanks so much, you got me past my stuck point. Now I just have to figure out how to make it more efficient!

[Brian Lancaster]

I got this to work in my PDI.  Since sc_task is already linked with sc_req_item you do not need this in your view as you can dot walk from the task to the ritm.  This is what I did and it is working for me.