In case someone else is looking, we got a response back from WHMCS support and here's the MySQL query I'm using:
SELECT IF(cr.type = 'End of Billing Period', h.nextduedate, cr.date) effectivedate, cr.type canceltype, cr.reason
FROM whmcs.tblhosting h
LEFT JOIN whmcs.tblcancelrequests cr ON h.id = cr.relid
WHERE cr.id IS NOT NULL
AND h.userid = <whmcsuserid>
;
Obviously substituting the whmcs user id for <whmcsuserid>
