How to query request item variables

[chrish5]

I have a catalog that allows our employees to check out a USB storage device from the service desk.  The form is pretty simple with a Requested For, Checkout Date and Expected Check in Date variables. There is also an Actual Check in Date variable that is hidden on the form from the requester and a Device Password workflow variable that is passed into the task that is assigned to the service desk..  When the employee submits the request, the service desk is assigned the task.  The service desk will then enter the Device Password and close the task.  The workflow takes that password and sends it to the employee and the request is closed out.  When the USB device is eventually returned to the service desk, the service desk will update the closed request by populating the Actual Check in Date.  

Here is what I need some help with.  Once a month we would like to query these USB Storage requests and if the Expected Check in Date has passed AND the Actual Check in Date is blank, we want to email the employee to let them know they have not turned in the USB Device.  Can this be done and if so how?  I don’t think we could query the sc_req_item table since the two variables do not reside there.  Could we query the sc_item_option_mtom table?  Open to suggestions.  Thanks,

[chrish5]

Rajashekhar, 

How would I script it to only trigger the email when the Expected Check in Date had passed AND the Actual Check in Date is blank?  I'm thinking this needs to be a scheduled job, that if true, inserts an event which fires off the email?  

Thanks,
Chris 

[donnadavis]

Did you ever get this to work?  I am looking to do something similar.  Thanks Donna Davis