I have a login table that has a log_in field and a log_out field
(both datetime) to record when the user enters and exits the
application. Sometimes, the user's computer crashes (or the app) and
does not record the log_out field, ie, it's empty.
I am writing a report that needs to have all those empty log_out
fields populated. The ideal thing to do would be to use the next
log_in record on the same day as the log_out of the missing record
for a given user (if there is one of course) and subtract 1 second
from it. In other words, you assume that if they logged in again on
that same day, then the logout time of the empty entry was right
before the next log-in. Although this could be entirely wrong (ie
they went to lunch first), it's the best assumption you can make when
the data is missing.
I know how to write this logic using xbase, but I was hoping to find
a select sql example to do it all in one step.
I tried using a subquery as a field for the log_out column, but it
does not seem to work (maybe I'm doing something wrong). In the
example below I restricted the code to a couple of months and days in
early 2008 just for keeping the sample size small during testing. In
this example, the lg_out2 calculated field is always equal to the
smallest non-empty log_in record for the given user within the date
range, regardless of the value of the log_in field for that given
row. So, it will work fine for the 1st occurrence of a blank log_out
record, but if there are more than one in the same date range, it
always shows the smallest value in the range.
If this approach is not the best method, I'm happy to see a different
way to do it ( this was just the way I thought it might be handled ).
Any help greatly appreciated.
Steve
Here's the code I tried which doesn't work as I expected.
SELECT u.username, l.lg_in, l.lg_out, ;
(SELECT MIN(lg2.lg_in)-1 ;
FROM loginhis lg2 ;
WHERE u.userid = lg2.userid AND ;
!EMPTY(lg2.lg_out) AND lg2.lg_in >= loginhis.lg_in AND ;
YEAR(lg2.lg_in)=2008 AND MONTH(lg2.lg_in)<3 AND DAY(l2.lg_in) < 25 ;
) AS lg_out2;
FROM loginhis L;
LEFT OUTER JOIN users u;
ON u.userid = l.userid;
WHERE YEAR(l.lg_in)=2008 AND MONTH(l.lg_in)<3 AND DAY(l.lg_in) < 25
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
