sql查询如何在时间间隔之间添加 [英] sql query how add between the time only late time

问题描述

如何在时间间隔之间添加

how to add between time only late time

select distinct
E.USERID,
Convert(date,LOCALTIMESTAMP) as ATTDate,
(select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime,
(select max(Convert(TIME,TIMESTAMPS)) from access_event_logs <a href=""></a>As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) as LateTime
,(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as EarlyTime
,(select DATEDIFF(MINUTE, '8:30:00', (select min(Convert(TIME,TIMESTAMPS)) from access_event_logs As MINCE2 where CAST(MINCE2.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MINCE2.EVENTID ='In' and MINCE2.USERID=E.USERID))) +
(select DATEDIFF(MINUTE, (select max(Convert(TIME,TIMESTAMPS)) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(E.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID), '14:30')) as TotalLate
from access_event_logs As E
WHERE E.USERID='012' AND Convert(date,LOCALTIMESTAMP) between '03/2/2014' and '03/16/2014'

这样输出

http://tinypic.com/view.php?pic=29cndvm&s=8#.U0ZnS_mSwZw [ ^ ]



但我只需要延迟和Eally Outtime加入TOtal LateTime

output like this
http://tinypic.com/view.php?pic=29cndvm&s=8#.U0ZnS_mSwZw[^]

but i need only Latetime and Earily Outtime add to TOtal LateTime

Userid      Date       InTime     OutTime  LateTime  EarilyOutTime TotalLateTime
012       2014-03-02  08:47:07   14:49:57     17      -19             -2
012       2014-03-03  08:27:01   14:31:58     -3       -1             -4
012       2014-03-04  08:29:43   14:32:12     -1       -2             -3
012       2014-03-05  08:34:29   14:23:29      4        7             11
012       2014-03-06  08:16:55   14:31:12    -14       -1            -15
012       2014-03-09  08:36:04   14:36:59      6       -6              0
012       2014-03-10  08:20:11    NULL        -1      NULL          NULL
012       2014-03-11  NULL       14:31:35   NULL      -1            NULL
012       2014-03-12  08:55:45   14:30:29     25        0             25
012       2014-03-13  08:35:52   14:38:58      5       -8             -3
012       2014-03-16  08:46:06   14:27:56     16        3             19

推荐答案

如果你想要总时间...

If you want total time...

SELECT userid, date, SUM(LateTime) AS LateTime, SUM(EarilyOutTime) AS EarilyOutTime, SUM(Total) AS Total
FROM (
--here comes your query
) AS T
GROUP BY userid, date
ORDER BY userid, date

这篇关于sql查询如何在时间间隔之间添加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！