Deleting Records from the AKI Database

The DMs may ask you to delete records from the database so they can load an improved version of the file.

In brief the steps are -

SELECT DATE_LOADED
FROM FILES_LOADED
WHERE
    FILE_LABCODE = 'XXXXX' AND
	FILENAME = 'XXXXX_20160701_20160731_ALERTS.csv'

Copy the value and then do -

DELETE FROM ALERT_RAW
WHERE
	DATE_LOADED = CAST('2020-09-15 08:11:29.000' AS DATETIME2)

The reason for doing it this way, rather than using DATE_OF_ALERT is that sometimes alerts are submitted outside the month. This ensures you've deleted everything that was loaded when that file was loaded.

And finally -

DELETE FROM FILES_LOADED
WHERE
	DATE_LOADED = CAST('2020-09-15 08:11:29.000' AS DATETIME2)

If you don't delete the record from FILES_LOADED then the Validation will stop the replacement file from being loaded.

There is a Jira ticket about adding this as a function in the Validation to save doing it manually. https://jira.renalregistry.nhs.uk/projects/AKI/issues/AKI-125