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 BadEnrollmentTransactions

https://i.tpsdb.com/2016-09-27_20-23-38.png

Sample Report

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


Latest Update 11/13/2020

Modify image link with secure protocol.

© 2020 TouchPoint Software, LLC.  All rights reserved.