This procedure is used for closing multiple samples at the same time that have fallen off of the LabCal calendar. It uses a SQL query.
Warning: This should only be done by very advanced users. Any changes made are irreversible. It is recommended that you back up your database before beginning.
This SQL query will close samples in the Sample table (LC_SAMPLE). You could also modify the query to delete the samples instead of closing them.
Before you begin, you need to know about what the various values represent in the SampleStatus field in the LC_SAMPLE table.
0 |
Pending |
4 |
Received |
5 |
Analyzed |
8 |
Skipped |
9 |
Closed |
You also need to know that RECTYPE of 0 means the sample is Open and RECTYPE of 1 means it is Closed.
Let's say you want to close all Received samples that were due before 3/1/10 and you want to set the DateComplete field to today's date which for this example is 3/31/10.
From Hach WIM'S main screen press the F9 Function Key, this will bring up the SQL Console.
First of all lets take a look at the open received samples that were due before 3/1/10. Type the following SELECT query into the SQL console and then click the Execute button.
SELECT LC_SAMPLE.RECTYPE,LC_SAMPLE.SAMPLESTATUS, LC_SAMPLE.DATECOLLECTIONDUE FROM LC_SAMPLE where DATECOLLECTIONDUE < '2010-03-01' and RECTYPE = 0 And SampleStatus = 4
Now we can modify the existing SELECT query to an UPDATE query. We want to update the RECTYPE, SAMPLESTATUS and DATECOMPLETE fields. RECTYPE = 1 and SampleStatus = 9 means it's Closed.
Update LC_SAMPLE Set RECTYPE = 1, SampleStatus = 9, DateComplete = '2010-03-31' where DATECOLLECTIONDUE < '2010-03-01' and RECTYPE = 0 And SampleStatus = 4
Then click on the Execute button. Warning: These changes will be irreversible.
If you wanted to delete these samples instead of closing them, you would use the following query:
Delete LC_SAMPLE Where DATECOLLECTIONDUE < '2010-03-01' and RECTYPE = 0 And SampleStatus = 4
Then click on the Execute button. Warning: These changes will be irreversible.