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)

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

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')"

This is really helpful, thanks for sharing. 

The other issue I'm encountering is getting results based on NULL values via List criteria. I'm sure I'm overlooking some really obvious.

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