CSI Status List Criteria / CSIs in Analytics

I'm curious about this too. I looked into a CSI closed/open list criteria a while back but wasn't totally successful.

As far as I could determine, the res_ind via lvs_cust_activity indicates a "Y" when closed by a user.

The issue I encountered, if I recall, is a NULL value seemed to be present both when a CSI is open or when auto-closed. 

You can pinch code from RP_CUST_SERVICE_ISSUE_TRACKING and make your own view then replicate the CSI Activity Type List Manager element using this instead of vs_cust_activity.

If an open action (not marked closed) is added after a closed action, it opens the CSI, the CTE retrieves the most recent action:

with cte_issue_status(activity_no, issue_status)
as
(
Select
a.activity_no,
issue_status =
(Select y.res_ind
From [dbo].t_issue_action y
where y.customer_no = a.customer_no
and y.activity_no = a.activity_no
and y.IsAc_no = (Select Top 1 x.IsAc_no
from [dbo].t_issue_action x
where y.customer_no = x.customer_no
and y.activity_no = x.activity_no
order by x.action_dt desc, IsAc_no desc))
from vs_cust_activity a
)
select a.*, b.category, i.issue_status from dbo.T_CUST_ACTIVITY a
left join cte_issue_status i on a.activity_no = i.activity_no
JOIN dbo.VRS_CUST_ACTIVITY_TYPE b ON a.activity_type = b.id
where (i.issue_status = 'N' or i.issue_status is null)

Kevin, I like your logic that you used in your query. Especially, how you use the top 1 IsAc_no to only look at the last change to a CSI. However, it does not address the issue when a CSI is auto-closed.

Unknown said:

a NULL value seemed to be present both when a CSI is open or when auto-closed

When I used your query, I get nearly 1 million rows (we must use auto-close a lot). All the result-set rows have NULL for the issue_status column.

I also looked at RP_CUST_SERVICE_ISSUE_TRACKING yesterday and realized that the auto-closing is the biggest issue.

I'm glad that you attempted it because I did find a few CSI's (3) that fell through the cracks using your method. 

I did add cag.description so you can see the Category selected in the CSI using:

join TR_CUST_ACTIVITY_CATEGORY cag on cag.id = b.category

Kevin, I like your logic that you used in your query. Especially, how you use the top 1 IsAc_no to only look at the last change to a CSI. However, it does not address the issue when a CSI is auto-closed.

Unknown said:

a NULL value seemed to be present both when a CSI is open or when auto-closed

When I used your query, I get nearly 1 million rows (we must use auto-close a lot). All the result-set rows have NULL for the issue_status column.

I also looked at RP_CUST_SERVICE_ISSUE_TRACKING yesterday and realized that the auto-closing is the biggest issue.

I'm glad that you attempted it because I did find a few CSI's (3) that fell through the cracks using your method. 

I did add cag.description so you can see the Category selected in the CSI using:

join TR_CUST_ACTIVITY_CATEGORY cag on cag.id = b.category

Well picked-up, Neil, I overlooked that, sorry. You can exclude them by adding "and b.category not in (select  id from TR_CUST_ACTIVITY_CATEGORY where auto_close = 'Y')"

Kevin,

Thanks again for posting pinched code for RP_CUST_SERVICE_ISSUE_TRACKING! I was able to use part of it to show the CSI status for responses to a recent WordFly email. I combined it with the query from a List (Show Query tab) to help track any open CSI's. It only took me a few minutes because of your post.

Wes, maybe you did what I did.

Kevin Madeira said:

select a.*, b.category, i.issue_status

Kevin Madeira said:

left join cte_issue_status i

 Initially not notice in the query results that the actual issue status column in the result-set is the farthest column to the right (i.issue_status); not the second column a.issue_status with all of the NULLs. I added a Case statement case when i.issue_status = 'Y' then 'Closed' else 'Open' end (for the NULLs).