Click here to Skip to main content
14,635,895 members
Rate this:
Please Sign up or sign in to vote.
See more: , +
this query give intime and outtime but i want total time and late time aslo....

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



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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100