ABPN IT

If it is reported that a precert user can't get a CSE evaluator to be found in the drop down, the reason is usually due to their certificate history.  For diplomate physicians who we've retired and then subsequently reactivated, there is a gap of time in CertificateHistory where they are not certified.  The evaluator feature looks at certificate history to confirm that the evaluator was indeed certified on the date of the CSE.  If this is the case, we need to alter the certificate history to remove the inactive portion of the history.

Example:

BEFORE:

AFTER:

SQL:

--- FYI TRANs don't work for this. all statements must get run individually, begin and rollback just for protection

BEGIN TRAN

ALTER TABLE [dbo].[Certificate] SET ( SYSTEM_VERSIONING = OFF )

DELETE FROM [dbo].[CertificateHistory] WITH (TABLOCKX)  WHERE [CertificateId] = 44386 and ValidFrom = '2023-04-05 01:03:31.9361525'

UPDATE [dbo].[CertificateHistory] set ValidTo = '2023-08-28 18:04:07.7943019' WHERE [CertificateId] = 44386 and ValidFrom = '2021-07-13 17:02:36.3513000'

ALTER TABLE [Certificate] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CertificateHistory], DATA_CONSISTENCY_CHECK = OFF))

COMMIT TRAN

ROLLBACK TRAN

SQL statement to find certificates with timegaps between most recent history record and current Certificate record:

WITH cte_CertHistory AS (

    SELECT CertificateId, MAX(ValidFrom) AS MaxValidFrom, Max(ValidTo) AS MaxValidTo 

    FROM CertificateHistory 

    GROUP BY CertificateId

)

SELECT ch.CertificateId 

FROM cte_CertHistory ch

JOIN Certificate c ON ch.CertificateId = c.CertificateId

WHERE ch.MaxValidTo <> c.ValidFrom

SQL statement to find certificates with timegaps between history records:

SELECT ch.CertificateId, ch.ValidTo as datestart, 

ISNULL(

    (SELECT TOP 1 ch.ValidFrom

    FROM CertificateHistory c

    WHERE c.CertificateId = ch.CertificateId

    AND c.ValidFrom > ch.ValidTo

    ORDER BY c.ValidFrom), GETDATE()) dateend

FROM CertificateHistory ch

WHERE NOT EXISTS(

    SELECT NULL

    FROM CertificateHistory b

    WHERE ch.CertificateId = b.CertificateId

    AND ch.ValidTo = b.ValidFrom)

AND EXISTS(

SELECT NULL

FROM CertificateHistory c

WHERE c.CertificateId = ch.CertificateId

AND c.ValidFrom > ch.ValidTo)