Bad Enrollment Transactions¶
This script runs from the OrgSearch BlueToolbar, and should be run by Admins only.
Finally, as with all SqlScript reports, you have a Download to Excel button.
The history link takes you to a page showing the details of the transactions for the person/org row. The transactions that are out of the permissible patterns of a valid history timeline, are highlighted in red. From there you can delete transactions to bring the history back into compliance.
The rules for a valid history are:
- The first transaction must be a Join.
- A change can only follow another Change or a Join.
- A Drop can only follow a Change or a Join.
- A Join can only follow a Drop.
- There should never be two Drops in a row.
- There should never be two Joins in a row.
- The transactions are linked together by a NextTranDate which points to the next transaction in date order.
- Each transaction after the first should point back to an associated Join transaction through the Enroll Id.
The recommended name is
Use the following code to Create the SQL Script. See How to create a SQL Script.
--Roles=Admin WITH e AS ( SELECT PeopleId, OrganizationId FROM dbo.EnrollmentTransaction WHERE OrganizationId IN (SELECT Value FROM dbo.SplitInts(@orgids)) GROUP BY PeopleId, OrganizationId ) SELECT p.PeopleId, Name2, o.OrganizationId, o.OrganizationName, linkfornext = '/TransactionHistory/Enrollment/' + CONVERT(VARCHAR(25), p.PeopleId) + '/' + CONVERT(VARCHAR(25), o.OrganizationId), 'history' History FROM e JOIN dbo.People p ON p.PeopleId = e.PeopleId JOIN dbo.Organizations o ON o.OrganizationId = e.OrganizationId WHERE EXISTS(SELECT NULL FROM dbo.EnrollmentHistory(e.PeopleId, e.OrganizationId) WHERE isgood = 0) ORDER BY o.OrganizationName, p.Name2
Modify image link with secure protocol.