Database Views - Incident SLA and Change SLA in one view

[richard_selby]

I'm having a spot of bother making a database view.
I would like to see Incident and Change tickets together with SLA fields all in one view, with the number column to show both INC000123 and CHG00123 tickets.

The system has two built in views which sound like good starting point.
Incident SLA - which joins task_sla to incident
Change SLA - which joins task_sla to change

I want
Ticket SLA - joins task_sla to incident_sla and change_sla

Making the Incident SLA view my starting point, I have tried adding in a third table, Change Request, to the view linked with a where clause

So
task_sla table joins incident where taskslatable_task = inc_sys_id
then
task_sla table joins change_request where taskslatable_task = chg_sys_id

(using table aliases as you would expect: taskslatable, inc, chg)


But this gaves 0 results. I have tried with a left join, but this gives too many results. And has the added disadvantage that I get multiple columns for Number (Service Request) and Number (Incident).

So is there any where of getting an all in one inc/chg/sla view in SN? Or am I barking up the wrong tree?

[PeterWiles]

Hi Richard,

I haven't tested this as I am just leaving the office but my guess would be to do the following:

From the task_sla table
Join the task table
then limit the task table where task_type = incident OR change.

Pete

[richard_selby]

You are right, and this works for getting the task number in one field. But there are some fields on the subsidiary tables I want to drag in, such as business service. So can we then pull in both the Change and Incident tables into the one view.

[PeterWiles]

I assume that the fields are not shared off the task table?
You would probably have to do two left joins, one for the change_sla and incident_sla.

This would give you the list then drag the relevant fields. I assume it will be blank where the fields are not on the other table.

I will have a play after this coffee.

Pete

[richard_selby]

Thanks, Pete. You are correct - I wanted to bring in some fields which are not shared off the task table. And to make matters worse, I also had a third (custom) table to bring in, called Service Requests. One of the columns I wanted from each subsidiary table is Business Service. Yes we have a Business Service column on all three subsidiary tables: change request, incident and service request. I wish it was up a level on Task but cést la vie.

Let me sum up what I found when trying to get an all-in-one Ticket SLA view for anyone else trying to figure out views. I have attached a screen shot which helps you to see what I did in the view definition.

As per Peter's suggestion, start with Task SLA. Join to Task where task_sys_id = taskslatable_task
Then I left joined to incident, change_request and service_request (our custom table, other people won't have this, but illustrates the point) to give me the subsidiary table fields. However, when I tried the results, it wasn't pretty. Even though all 3 child tables had a col called Business Service, the data is not flattened into one column. I had three cols available, all called Business Service.

In the end, to get the report I need, I will be exporting our data into a 3rd party BI reporting tool, and merging there.