如何匹配开始日期到最近的结束日期，以获得时差

Question

SELECT 
  OUT.EMP_ID, 
  OUT.DT_TM "DateTimeOut",  
  IN.DT_TM "DateTimeIn", 
  cast(timestampdiff( 4, char(timestamp(IN.DT_TM) - timestamp(OUT.DT_TM))) as decimal(30,1))/60 "Duration Out" 
FROM ( 
  select  
    e1.EMP_ID,   
    e1.DT_TM    
  from 
    hr.timeout e1   
  WHERE      
    month(e1.DT_TM)=09   
    and year(e1.DT_TM)=2016   
    AND e1.CD='OUT'
) OUT 
LEFT JOIN (  
  select   
    e2.EMP_ID,   
    e2.DT_TM   
  from   
    hr.timeout e2   
  WHERE      
    month(e2.dt_tm)=09   
    and year(e2.dt_tm)=2016   
    AND e2.CD='IN'  
) IN   
on out.EMP_ID=in.EMP_ID

尝试获得与DateTimeOut最匹配的DateTimeIn。目前它多次重复相同的DateTimeOut和DateTimeIn

Answer 1

我认为这是正常的，因为你的表没有唯一的约束emp_id, dt_tm和cd，但如果你想要唯一的结果试试这个:

    With Period as (
    select  e1.EMP_ID,  e1.DT_TM  from hr.timeout e1   
    WHERE month(e1.DT_TM)=09 and year(e1.DT_TM)=2016
    )
    Select  distinct OUT.EMP_ID,  IN.DT_TM  as DateTimeIn, OUT.DT_TM as DateTimeOut,
    TIMESTAMPDIFF(2 , CAST(timestamp(IN.DT_TM) - timestamp(ifnull(OUT.DT_TM, current date)) AS CHAR(22)) ) as DurationSecond
    from Period in left outer join Period out 
    on out.EMP_ID=in.EMP_ID and out.CD='OUT' and in.CD='IN'
    order by 1, 2, 3

就像你看到的，我使用timestampdiff与'2'像第一个参数秒(你除以60)，我使用ifnull，因为你做了一个左外连接(out)。DT_TM可以为null)，我对唯一的结果