Database View Right Join

[Scott Parrish]

I need to join Incidents with the Metric Instances to get the on hold times of resolved tickets, but also need to get all other incidents that are resolved or closed that did not have an on hold time. I was hoping there was a way to create a database view right join where I joined the On Hold instances that matched Incidents and returned all other Incidents. Any ideas on how I can accomplish this?

[-O-]

That's done with a left join where the main table (with lowest order no.) is Incident.

[Scott Parrish]

Unfortunately a left join in this instance will not work. Every Incident has at least one, if not multiple, matching record in the Metric Instance table for the Incident State Duration metric definition. Therefore, a left join will simply return multiples of the same record, which I do not want, or just filtered matching records. This is why I need a right join.

[-O-]

Have you tried it?

It sure seems to me it works*:

*) Though left join or right join, one must craft a metric instance so that if the On Hold State is applied several times, there is still gonna be just one metric instance record per incident, per metric definition. The simple definition of a metric that measures field value duration will produce as many metric instance records as many times the On Hold State was set.

[Scott Parrish]

What version are you on? We are on San Diego and I do not have a "State Duration" metric, only an Incident State Duration metric, and, as I said, there is an entry for every state of an incident. A left join will not work since it finds a match for every incident in the metric table. I guess I could create a separate "State Duration" that only measures on hold, copies the current On Hold entries from Incident State Duration and use the new state duration.