Quality Essentials Suite

THIS IS FOR YOUR TEST AND PILOT SYSTEMS ONLY - DO NOT RUN THIS IN YOUR LIVE ENVIRONMENT AS IT WILL CAUSE RESULTS TESTING DATA LOSS!!!

A customer had setup a test incorrectly and had recorded test values against the test record which locks the test from being editable.   They wanted to fix the test instead of creating a new one.   Please note this delete and fix goes against our policy of disabling the test and recreating a new one.   As this was their test system we provided a query to delete all relationships in the database for that test which will then allow the test to be edited and changed.

Running this in your environment will delete all result documents and result detail records for which this test is related to.   It deletes ALL documents and not just the detail lines related to that test.

Simply find the TestID you want to delete and modify the @TestToFix =xx statement where xx is the ID of the test in question.  Run the query in SQL and it will delete any related records.

declare @TestToFix int

set @TestToFix = 6

--Get a list of results header records that have the test in question on it

SELECT HQMS_ResultsHeader.ResultsHeaderID,HQMS_Results.TestID, HQMS_Results.ResultsID

INTO #tempResultRecords

FROM HQMS_ResultsHeader INNER JOIN

HQMS_Results ON HQMS_ResultsHeader.ResultsHeaderID = HQMS_Results.ResultsHeaderID

WHERE (HQMS_Results.TestID = @TestToFix)

--Get a list of COA records that have that test associated with it

SELECT Test_TestID,COAPrintDetailID, COAPrintHeaderID INTO #tempCOAPrintDetail

FROM HQMS_COAPrintDetail WHERE Test_TestID = @TestToFix

DELETE FROM HQMS_COAPrintSelectedSL WHERE COAPrintHeaderID in (SELECT COAPrintHeaderID from #tempCOAPrintDetail)

DELETE FROM HQMS_COAProcessSelectedSL WHERE COAPrintHeaderID in (SELECT COAPrintHeaderID from #tempCOAPrintDetail)

DELETE FROM HQMS_COAPrintDetail WHERE COAPrintHeaderID in (SELECT COAPrintHeaderID from #tempCOAPrintDetail)

DELETE FROM HQMS_COAPrintHeader WHERE COAPrintHeaderID in (SELECT COAPrintHeaderID from #tempCOAPrintDetail)

DELETE FROM HQMS_ResultsStoredCriteria WHERE ResultsID in (SELECT ResultsID FROM HQMS_Results WHERE (TestID = @TestToFix))

DELETE FROM HQMS_ResultsText WHERE ResultsTextID in (SELECT ResultsID FROM HQMS_Results WHERE (TestID = @TestToFix))

DELETE FROM HQMS_ResultsDate WHERE ResultsDateID in (SELECT ResultsID FROM HQMS_Results WHERE (TestID = @TestToFix))

DELETE FROM HQMS_ResultsBaseData WHERE ResultsHeaderID in (SELECT ResultsHeaderID from #tempResultRecords)

DELETE FROM HQMS_Results WHERE TestID = @TestToFix

DELETE FROM HQMS_ResultsHeader WHERE ResultsHeaderID in (SELECT ResultsHeaderID from #tempResultRecords)

drop table #tempResultRecords

drop table #tempCOAPrintDetail