Click here to Skip to main content
15,884,099 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
this query give intime and outtime but i want total time and late time aslo....

SQL
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'


CSS
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



i want between the row (intime and outtime) calculate total time and late time
my office timing 7:30 to 2:30
Posted
Updated 6-Apr-14 20:56pm
v2
Comments
Schatak 7-Apr-14 3:17am    
you want to have count of Late and early arrivals?
akhil.krish 7-Apr-14 5:33am    
Thanks For Reply Schatak.....

s iwant another column late and earily late time
Schatak 7-Apr-14 5:43am    
can you show your table definition?
akhil.krish 7-Apr-14 8:31am    
hi Schatak,

my table [access_event_logs ]
,[USERID]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[LOCALTIMESTAMP]datetime

out put like this

[USERID] [TIMESTAMPS] [EVENTID]

1 019 2014-03-06 07:50:48.000 IN
2 019 2014-03-06 17:02:39.000 OUT
3 019 2014-03-09 07:43:37.000 IN
4 019 2014-03-09 14:34:59.000 OUT
5 019 2014-03-10 07:43:34.000 IN
6 019 2014-03-10 14:30:24.000 OUT

eventid (in and out ) only single row...

check the 1st sql query...

akhil.krish 7-Apr-14 16:19pm    
any updates.....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900