SM_FindActiveSampleRecords stored procedure
This SM_FindActiveSampleRecords stored procedure identifies records that are erroneously in the ACTIVE queue and stores them in a new ActiveSampleRecords table in the mrInterviewData database. A record is defined as erroneously in the ACTIVE queue when the stored procedure finds consecutive records in the ACTIVE queue, without the record being returned between instances. The system returns the sample record each time the respondent finishes an interview, which results in updates to the ReturnTime sample field. The ReturnTime check avoids identifying restarted records as erroneously ACTIVE.
The stored procedure must be run at least twice before erroneously ACTIVE records are identified. All projects that are in the ACTIVE status are checked by the stored procedure. Projects that are in an inactive or test status are ignored.
Process for identifying an erroneously ACTIVE record
1 For projects that are currently active, and have an associated sample, create a list of records that are currently in the ACTIVE queue.
2 If the record does not exist in the ActiveSampleRecords table, add it to the table with the current UTC time as the CheckTime.
3 Otherwise, the record exists in the ActiveSampleRecords table.
▪If ReturnTime is later than CheckTime, update theCheckTime to the current UTC time.
▪Otherwise, no updates are required.
4 Delete records from the ActiveSampleRecords table when any of the following conditions are in place.
▪The records are no longer in the ACTIVE queue.
▪The associated project was deleted.
▪The associated project status was changed to Test or Inactive.
▪The associated project was reactivated without sample.
ActiveSampleRecords table
The ActiveSampleRecords table contains the list of records that are identified as ACTIVE during the stored procedure's last run and the original CheckTime. The record also contains additional information that is used to determine whether records are still ACTIVE.
Fields
Id
The primary key.
DataSource
The sample management data source server name.
Database
The sample management database name.
Table
The sample management data table name.
TableOwner
The sample management data table owner.
SampleId
The sample management record’s Id from the original sample table.
CheckTime
The time that the first check found the record to be ACTIVE, but not restarted.
See also