Does anyone have a custom list criteria for pulling people with Open CSIs? I know I can just run the report, but I our Devo department wants to see anyone with a specific CSI Activity in a Dashboard on Renewals (they track people who asked us to follow-up with them later about donating). Since CSIs aren't in Analytics, I've been pulling a list and loading that list into a widget in my Analytics Dashboard. But now that its time to follow-up, I need to be able to take off the people who have been followed up with already and have Closed CSIs.
Anyone have CSI Status = Open in List Manager ? Or have any other ideas on how to get a list of people with Open CSIs into Analytics?
Thanks!
Lily
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 aleft join cte_issue_status i on a.activity_no = i.activity_noJOIN dbo.VRS_CUST_ACTIVITY_TYPE b ON a.activity_type = b.idwhere (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')"
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).