sql query - Get the difference time between swipe

问题:

I have a table like this

swipe_details

EMPID   EVENT_TYPE   Swipe_time
1       EN           2012-06-01 12:00
1       EX           2012-06-01 12:30
2       EN           2012-06-01 12:50
1       EN           2012-06-01 13:10
2       EX           2012-06-01 13:30
2       EN           2012-06-01 14:00
2       EX           2012-06-01 19:00
1       EX           2012-06-01 19:30

which basically stores the swipe details of employees (where EN is "Swipe-IN" and EX is "Swipe OUT")

I want to find out the EMPIDs who spend maximum time in the office for a given time frame. So my input will be a start time and end time (Ex: 2012-06-01 12:30 and 2012-06-05 18:30).

Please note these 3 conditions as well:

1. There could be employees who have entered office before the time frame. In this case we have to consider the swipe in as the beginning of the time frame for that employee.

2. Same way for employees who exit after the time frame, the swipe out time would be the end of the time frame.

3. IF there are employees whose swipe in time is less than the time frame and swipe out time is greater than the time frame, then all of these empIDs should be shown as the result

What is the best way to do it? Is it possible with a single query?

回答1:

select    empid,
          sum
          (
              datediff
              (
              MINUTE, 
              case when timesheet.timein < @timeframe_start 
                  then @timeframe_start  
                  else timesheet.timein end, 
              case when timesheet.timeout > @timeframe_end 
                  then @timeframe_end 
                  else timesheet.timeout end
              )
          ) as total_duration
from     (
         select    timein.empid, 
                   timein.swipe_time as timein, 
                   timeout.swipe_time as timeout
         from      tbltest timein
         left join tblTest timeout 
         on        timein.empid = timeout.empid
         and       timeout.eventtype = 'ex'
         and       timeout.swipe_time = 
             (
             select     MIN(swipe_time) 
             from       tblTest tcheck 
             where      tcheck.empid = timeout.empid 
             and        tcheck.eventtype = 'EX' 
             and        tcheck.swipe_time > timein.swipe_time
             )
         where      timein.eventtype = 'en'
         ) timesheet
where    timesheet.timein between @timeframe_start and @timeframe_end
or       timesheet.timeout between @timeframe_start and @timeframe_end
or       (
         timesheet.timein < @timeframe_start 
         and timesheet.timeout > @timeframe_end
         )
group by empid
order by 2 desc