library(RMySQL)

#
# ***Find maximum respondable item ID for each ACASI survey (respondable items have answer types other than 'none'...
#

db  = dbConnect(MySQL(), group='HND')
sql = "select s.id as surveyID, s.name as surveyName, max(q.id) as maxQuestion from Wave04surveys.questions q, Wave04surveys.surveys s where q.survey_id=s.id and q.answer_type <> 'none' and s.name like 'ACASI%' group by q.survey_id"
maxQ = dbGetQuery(db, sql)

maxQ

   surveyID         surveyName maxQuestion
1         1       ACASI04rev01        7391
2        11       ACASI04rev01       14791
3        21       ACASI04rev01       22191
4        31       ACASI04rev01       29591
5        41       ACASI04rev01       36651
6        51       ACASI04rev01       43721
7        61       ACASI04rev02       47161
8        71       ACASI04rev02       51681
9        81       ACASI04rev02       56211
10      111       ACASI04rev03       68151
11      121       ACASI04rev03       72761
12      131       ACASI04rev03       77371
13      151 ACASI04rev03Westat       82221

#
# ***For each ACASI survey, find the maximum item ID for each participant & create indicator for whether participant completed...
#

statusACASI = data.frame()

for (k in seq_along(maxQ$surveyID)) {
#   cat(maxQ$maxQuestion[k], ' questions in ', maxQ$surveyName[k], '-', maxQ$surveyID[k], ':\n', sep='')
    sql = paste('select cast(HNDid as char) as HNDid, survey_id as surveyID, max(question_id) as maxResponse from Wave04surveys.responses where HNDid between 8030000000 and 8229999999 and survey_id=', maxQ$surveyID[k], 'group by HNDid')
    maxR = dbGetQuery(db, sql)
    maxR$Complete = maxR$maxResponse == maxQ$maxQuestion[k]
    statusACASI = rbind(statusACASI, maxR)
    }

statusACASI$Complete = factor(statusACASI$Complete, levels=c(F,T), labels=zQ(Incomplete,Complete))

Numbers of incomplete and complete ACASIs for each survey ID

Proportion of incomplete and complete ACASIs for each survey ID

#
# ***Match ACASIstatus with tests & forms; tabulate ACASI completion by final status...
#

w04TF = HNDdbGet('Wave04.tandf')

w04TF$review = factor(w04TF$review, levels=0:5, labels=zQ(Missing,noShow,Reschedule,needsReschedule,Incomplete,Complete))

zKtable(table(w04TF$review), 'Final review status from tests & forms')

Final review status from tests & forms

statusACASI = merge(statusACASI, zVsel(w04TF, HNDid, review), 'HNDid', all=T)

levels(statusACASI$Complete) = zQ(Incomplete,Complete,Missing)

statusACASI$Complete[is.na(statusACASI$Complete)] = 'Missing'
statusACASI$review[is.na(statusACASI$review)] = 'Missing'

tbl = with(statusACASI, table(review, Complete, useNA='ifany'))

zKtable(tbl, 'Numbers of incomplete and complete ACASIs for each survey ID')

Numbers of incomplete and complete ACASIs for each survey ID

zKtable(prop.table(tbl, 1), 'Proportion of incomplete and complete ACASIs for each survey ID')

Proportion of incomplete and complete ACASIs for each survey ID