Add Actual Date to 10-Year-Old Interactions

What Does It Do?

The OUD-DS3 best practice is for interactions which have been completed to have the actual date field populated.

This integrity check identifies interactions that have an expected date of 10 or more years ago but do not have an actual date so that the actual date can be populated using the expected date field. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-INTERACTION-Add Actual Date to 10YO Interactions(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries interactions with an expected date of more than ten years ago which do not have an actual date. It excludes interactions which are more than five-years-old and have a status of ‘Planned’ or ‘Pending’, because those are reviewed by PDA. It adds an actual date using the expected date value.

The query removes records with inactive interaction categories or sub-categories because they can not be fixed via batch. Translating inactive code values is on the IDS data quality backlog.

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\INTERACTION

Snapshot GUID: BBDW.FACT_INTERACTION.INTERACTIONSYSTEMID