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'
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
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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Submit an article or tip