select distinct CE.USERID, CONVERT(Date,Localtimestamp) as LocalTime, (select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime, (select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime from access_event_logs As CE WHERE USERID='019' AND Convert(date,LOCALTIMESTAMP) between '3/1/2014' and '3/18/2014'
019 2014-03-02 2014-03-02 07:56:01.000 2014-03-02 14:45:47.000 019 2014-03-03 2014-03-03 07:58:58.000 2014-03-03 14:31:31.000 019 2014-03-04 2014-03-04 07:49:09.000 2014-03-04 14:43:01.000 019 2014-03-05 2014-03-05 07:50:08.000 2014-03-05 14:51:01.000 019 2014-03-06 2014-03-06 07:50:48.000 2014-03-06 17:02:39.000 019 2014-03-09 2014-03-09 07:43:37.000 2014-03-09 14:34:59.000 019 2014-03-10 2014-03-10 07:43:34.000 2014-03-10 14:30:24.000 019 2014-03-11 2014-03-11 07:45:31.000 2014-03-11 14:33:05.000 019 2014-03-12 2014-03-12 07:47:59.000 2014-03-12 14:30:55.000 019 2014-03-13 2014-03-13 07:37:54.000 2014-03-13 14:38:10.000 019 2014-03-16 2014-03-16 07:46:48.000 2014-03-16 14:29:25.000 019 2014-03-17 2014-03-17 07:53:28.000 2014-03-17 14:26:46.000 019 2014-03-18 2014-03-18 08:29:18.000 2014-03-18 14:34:29.000
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)