How can I report connection request changes in state and status?

Question

Connection requests do not automatically record the dates that their states and statuses change. How do we report on how many connection requests were made inactive within a certain date range? How do we report on how many requests were updated from 'No Contact' to 'In Progress'?

Answer 1

I have the following queries for connection request reporting that is based on history logs.

1.) First, check the data base functions for the presence of the a 9embers_SplitStrings function. I am linking my version here. It uses an item and a row number, but I've found other versions that do not. In order to avoid breaking anything else, make sure you don't write over anything that's already there.

Add String Split sql function: https://9emberscom.sharepoint.com/:u:/s/9Embers/EaocXXKDmxxEr4lBvM7D48sBpvyJyT8HHiEN0R11yUQfTg?e=Z5gOZI

2.) Now you can run the following query in SSMS to test, or add it to a dynamic data block. As is, it requires filters for ConnectionState, ConnectionStatus, CampusId, ConnectionOpportunity, Connector and DateRange. All but the last two should be in the form of single selects that pass the entity id into the query (this is for the sake of efficiency, to reduce joins). Sample single select code is in comments at the bottom.

Connection Request History sql: https://9emberscom.sharepoint.com/:u:/s/9Embers/EUnNqCZNbDZDouXCS-y2jYEBqCQTGctqOOGms5XONFwVTQ?e=8g20Do