LastTime column NULL when request is self approved

[TeriStevenson]

I have a query that finds general catalog items that were approved the previous day and creates a report. I'm using the Authorized column and the LastTime column to determine if the request was approved. I found some requests that aren't being picked up in the report and in troubleshooting found that on the requests where the approver was also the submitted the LastTime is NULL, where the requests that were not submitted by the approver have the LastTime value of the timestamp from approval.

Is that expected behavior?  I was assuming that all requests would have the LastTime column populated if Authorized is 1 regardless of who submitted.  Any workarounds to this?  

[jdempsey · Accepted solution]

I'm guessing it's "expected" behavior, whether desirable or not.  As a workaround, you may be able to modify your query to look something like this...

SELECT RequestID
FROM WD_PackageRequests
WHERE DATEDIFF(D,ISNULL([LastTime], [DateTime]),GetDate()) <= 1

Notice that I'm using the ISNULL function to substitute the value from the [DateTime] column if the value from the [LastTime] column is NULL.  I would expect that the [DateTime] column should hold the date the request was submitted, which should also be when it was "approved" in the case of the requester=approver auto-approval scenario. 

[TeriStevenson]

Thanks 🙂  I was thinking of something along those lines and this occurs with only one user so it should be an easy workaround.