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

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.