SQL Match employee intime (punch time) with employ

问题:

I have a Stored Procedure that retrieves employee daily summary intime - outtime:

SELECT ads.attendancesumid,
       ads.employeeid,
       ads.date,
       ads.day, -- month day number
       ads.intime,
       ads.outtime
       --employee shift intime and outtime
       ss.intime,
       ss.outtime
  FROM employee_attendance_daily_summary ads
  JOIN employee emp
    ON emp.employeeid = ads.employeeid
  JOIN setup_shift ss
    ON ss.shiftcode = emp.shiftcode
   AND DATEPART(dw, ads.date) = ss.day
 WHERE ads.employeeid = 4 -- just to filter one employee

The result of the query is something like this:

Each day is repeated 3 times because table setup_shift (employee shifts) has:

Monday to Sunday for 3 different shift types: DAY, AFTERNOON and NIGHT.

Here is the same info but with the shift type column:

What I need is to ONLY get 1 row per day but with the closest employee shift depending on the intime and outtime.

So the desire result should looks like this:

Any clue on how to do this? Appreciate it in advance.

I have also these case where intime is 00:00:00 but outtime has a value:

UPDATE:

HERE IS THE SQL FIDDLE

http://sqlfiddle.com/#!6/791cb/7

回答1:

select ads.attendancesumid,
       ads.employeeid,
       ads.date,
       ads.day,
       ads.intime,
       ads.outtime,
       ss.intime,
       ss.outtime
  from employee_attendance_daily_summary ads
  join employee emp
    on emp.employeeid = ads.employeeid
  join setup_shift ss
    on ss.shiftcode = emp.shiftcode
   and datepart(dw, ads.date) = ss.day
 where ads.employeeid = 4
   and ((abs(datediff(hh,
                      cast(ads.intime as datetime),
                      cast(ss.intime as datetime))) between 0 and 2) or
       (ads.intime = '00:00:00' and
       ss.intime =
       (select min(x.intime)
            from setup_shift x
           where x.shiftcode = ss.shiftcode
             and x.intime > (select min(y.intime)
                               from setup_shift y
                              where y.shiftcode = x.shiftcode))))

回答2:

This would be much easier if the times were in seconds after midnight, rather than in a time, datetime, or string format. You can convert them using the formula:

select datepart(hour, intime) * 3600 + datepart(minute, intime) * 60 + datepart(second, intime)

(Part of this is just my own discomfort with all the nested functions needed to handle other data types.)

So, let me assume that you have a series of similar columns measured in seconds. You can then approach this problem by taking the overlap with each shift and choosing the shift with the largest overlap.

with t as (
      <your query here>
     ),
     ts as (
      select t.*,
             (datepart(hour, ads.intime) * 3600 + datepart(minute, ads.intime) * 60 + 
              datepart(second, ads.intime)
             ) as e_intimes,
             . . .
      from t
     ),
     tss as (
      select ts.*,
             (case when e_intimes >= s_outtimes then 0
                   when e_outtimes <= s_inttimes then 0
                   else (case when e_outtimes < s_outtimes then e_outtimes else s_outtimes end) -
                        (case when e_intimes > s_intimes then e_intimes else s_intimes end)
              end) as overlap
      from ts
     )
select ts.*
from (select ts.*,
             row_number() over (partition by employeeid, date
                                order by overlap desc
                               ) as seqnum
      from ts
     ) ts
where seqnum = 1;

回答3:

Try this man,I just take the minimum time difference of the each set datediff(mi,intime,shift_intime)

Select * from 
(select 
row_number() over(partition by employeeid 
                   order by datediff(mi,intime,shift_intime) asc) as id,
attendance,employeeid,date,day,intime,outime,shiftintime,shiftoutime from table
 )
where id=1