sql server - SQL query to match rows for a sample attendance management report -

sql server - SQL query to match rows for a sample attendance management report -

i've access log table attendance management scheme this:

id logdate employeeid direction 242 2014-05-02 07:59:20.000 2565 in 1907 2014-05-02 19:00:01.000 2565 out 3648 2014-05-03 08:08:03.000 2565 in 5611 2014-05-03 19:32:37.000 2565 out 5612 2014-05-03 19:33:44.000 2565 out 7315 2014-05-05 08:01:01.000 2565 in 9301 2014-05-05 19:37:48.000 2565 out 10857 2014-05-06 08:29:44.000 2565 in 12327 2014-05-06 18:51:56.000 2565 out 14331 2014-05-07 08:44:03.000 2565 in 16084 2014-05-07 19:09:07.000 2565 out 17608 2014-05-08 07:58:13.000 2565 in 21155 2014-05-09 08:12:26.000 2565 in 22673 2014-05-09 18:42:55.000 2565 out 24550 2014-05-10 08:13:09.000 2565 in 26455 2014-05-10 19:02:28.000 2565 out 30013 2014-05-12 19:07:53.000 2565 out 31083 2014-05-13 08:00:27.000 2565 in 31087 2014-05-13 08:02:39.000 2565 in 33084 2014-05-13 18:58:09.000 2565 out 35201 2014-05-14 08:31:13.000 2565 in 37238 2014-05-14 19:10:03.000 2565 out 39225 2014-05-15 08:47:44.000 2565 in 40636 2014-05-15 18:59:47.000 2565 out 42969 2014-05-16 09:58:17.000 2565 in 42970 2014-05-16 09:58:24.000 2565 in 42974 2014-05-16 09:58:51.000 2565 in 44114 2014-05-16 18:49:39.000 2565 out 44116 2014-05-16 18:50:43.000 2565 out 46284 2014-05-17 09:07:03.000 2565 in 47323 2014-05-17 17:56:57.000 2565 out 48719 2014-05-19 08:05:28.000 2565 in 49919 2014-05-19 18:52:35.000 2565 out 51188 2014-05-20 09:02:07.000 2565 in 52348 2014-05-20 19:01:05.000 2565 out 54231 2014-05-21 09:35:49.000 2565 in 55896 2014-05-21 19:57:48.000 2565 out

i have next conditions duration sample 12 hours shift log details:

ideal case: wherein each in , corresponding out (directions) matched , duration calculated deducting out-time in-time in, no-out, in: wherein, there no out record in , followed in. display null , hence no duration can calculated, since out missing, allow set 12:00:00 (shift duration). in, out, out: duration should equal earliest out minus latest in. in, out, in, out: duration = first out - first in + sec out - sec in.

note: 12 hr shift , note when there in on 20-july-2014 19:00:00 hours, out might happen on 21-july-2014 07:05:00 , duration 12:05:00 hours.

i've written sqls result, i'm unable handle conditions: here test data:

if exists (select * sys.objects object_id = object_id(n'[dbo].[tmpaccesslogtbl]') , type in (n'u')) drop table [dbo].[tmpaccesslogtbl] go create table tmpaccesslogtbl ( [id] [int] not null primary key, [logdate] [datetime] not null, [employeeid] [int] not null, [direction] [nvarchar](3) null ) ; insert tmpaccesslogtbl ([id], [logdate], [employeeid], [direction]) select 242, '20140502 07:59:20.000', 2565, n'in' union select 1907, '20140502 19:00:01.000', 2565, n'out' union select 3648, '20140503 08:08:03.000', 2565, n'in' union select 5611, '20140503 19:32:37.000', 2565, n'out' union select 5612, '20140503 19:33:44.000', 2565, n'out' union select 7315, '20140505 08:01:01.000', 2565, n'in' union select 9301, '20140505 19:37:48.000', 2565, n'out' union select 10857, '20140506 08:29:44.000', 2565, n'in' union select 12327, '20140506 18:51:56.000', 2565, n'out' union select 14331, '20140507 08:44:03.000', 2565, n'in' union select 16084, '20140507 19:09:07.000', 2565, n'out' union select 17608, '20140508 07:58:13.000', 2565, n'in' union select 21155, '20140509 08:12:26.000', 2565, n'in' union select 22673, '20140509 18:42:55.000', 2565, n'out' union select 24550, '20140510 08:13:09.000', 2565, n'in' union select 26455, '20140510 19:02:28.000', 2565, n'out' union select 30013, '20140512 19:07:53.000', 2565, n'out' union select 31083, '20140513 08:00:27.000', 2565, n'in' union select 31087, '20140513 08:02:39.000', 2565, n'in' union select 33084, '20140513 18:58:09.000', 2565, n'out' union select 35201, '20140514 08:31:13.000', 2565, n'in' union select 37238, '20140514 19:10:03.000', 2565, n'out' union select 39225, '20140515 08:47:44.000', 2565, n'in' union select 40636, '20140515 18:59:47.000', 2565, n'out' union select 42969, '20140516 09:58:17.000', 2565, n'in' union select 42970, '20140516 09:58:24.000', 2565, n'in' union select 42974, '20140516 09:58:51.000', 2565, n'in' union select 44114, '20140516 18:49:39.000', 2565, n'out' union select 44116, '20140516 18:50:43.000', 2565, n'out' union select 46284, '20140517 09:07:03.000', 2565, n'in' union select 47323, '20140517 17:56:57.000', 2565, n'out' ; select t.employeeid, t.attendancedate, t.intime, case when datediff(hour, t.intime, t.outtime) > 23 null else t.outtime end outtime, cast (datediff(hour, t.intime, t.outtime) varchar) + ':' + cast(datediff(minute, t.intime, t.outtime) %60 varchar) duration ( select i.employeeid, convert(varchar, i.logdate, 106) attendancedate, i.logdate intime, ( select min(o.logdate) outtime tmpaccesslogtbl o o.direction = 'out' , o.employeeid = i.employeeid , o.logdate > i.logdate ) outtime tmpaccesslogtbl i.direction = 'in' ) t order t.employeeid, t.attendancedate

my results follows sql:

employeeid attendancedate intime outtime duration 2565 02 may 2014 2014-05-02 07:59:20.000 2014-05-02 19:00:01.000 12:1 2565 03 may 2014 2014-05-03 08:08:03.000 2014-05-03 19:32:37.000 11:24 2565 05 may 2014 2014-05-05 08:01:01.000 2014-05-05 19:37:48.000 11:36 2565 06 may 2014 2014-05-06 08:29:44.000 2014-05-06 18:51:56.000 10:22 2565 07 may 2014 2014-05-07 08:44:03.000 2014-05-07 19:09:07.000 11:25 2565 08 may 2014 2014-05-08 07:58:13.000 null 35:44 2565 09 may 2014 2014-05-09 08:12:26.000 2014-05-09 18:42:55.000 10:30 2565 10 may 2014 2014-05-10 08:13:09.000 2014-05-10 19:02:28.000 11:49 2565 13 may 2014 2014-05-13 08:00:27.000 2014-05-13 18:58:09.000 10:58 2565 13 may 2014 2014-05-13 08:02:39.000 2014-05-13 18:58:09.000 10:56 2565 14 may 2014 2014-05-14 08:31:13.000 2014-05-14 19:10:03.000 11:39 2565 15 may 2014 2014-05-15 08:47:44.000 2014-05-15 18:59:47.000 10:12 2565 16 may 2014 2014-05-16 09:58:17.000 2014-05-16 18:49:39.000 9:51 2565 16 may 2014 2014-05-16 09:58:24.000 2014-05-16 18:49:39.000 9:51 2565 16 may 2014 2014-05-16 09:58:51.000 2014-05-16 18:49:39.000 9:51 2565 17 may 2014 2014-05-17 09:07:03.000 2014-05-17 17:56:57.000 8:49

to summarize, set the results of above sql , expected results here:

-- current query -- -- expected results -- sl empid day intime outtime duration intime outtime duration 1 2565 02-may-2014 7:59 19:00 11:00:41 7:59 19:00 11:00:41 2 2565 03-may-2014 8:08 19:32 11:24:34 8:08 19:32 11:24:34 3 2565 05 may 2014 8:01 19:37 11:36:47 8:01 19:37 11:36:47 4 2565 06 may 2014 8:29 18:51 10:22:12 8:29 18:51 10:22:12 5 2565 07 may 2014 8:44 19:09 10:25:04 8:44 19:09 10:25:04 6 2565 08 may 2014 7:58 null null 7:58 12:00:00 7 2565 09 may 2014 8:12 18:42 10:30:29 8:12 18:42 10:30:29 8 2565 10 may 2014 8:13 19:02 10:49:19 8:13 19:02 10:49:19 9 2565 13 may 2014 8:00 18:58 10:57:42 10 2565 13 may 2014 8:02 18:58 10:55:30 8:02 18:58 10:55:30 11 2565 14 may 2014 8:31 19:10 10:38:50 8:31 19:10 10:38:50 12 2565 15 may 2014 8:47 18:59 10:12:03 8:47 18:59 10:12:03 13 2565 16 may 2014 9:58 18:49 8:51:22 14 2565 16 may 2014 9:58 18:49 8:51:15 15 2565 16 may 2014 9:58 18:49 8:50:48 9:58 18:49 8:50:48 16 2565 17 may 2014 9:07 17:56 8:49:54 9:07 17:56 8:49:54

in expected result, whenever there no out time corresponding in, duration should 12:00:00.

please help me accomplish this. in advance.

if problem code rows null outtime values in output should have duration of 12:00:00, next code should work you.

what did add together case statement logic used in outtime field calculation strings used generate duration field. wrapping calculation in isnull() function means values long of duration homecoming whatever want.

select t.employeeid, t.attendancedate, t.intime, case when datediff(hour, t.intime, t.outtime) > 23 null else t.outtime end outtime, isnull(cast ( datediff ( hour, t.intime, case when datediff(hour, t.intime, t.outtime) > 23 null else t.outtime end) varchar) + ':' + cast( datediff ( minute, t.intime, case when datediff(hour, t.intime, t.outtime) > 23 null else t.outtime end ) %60 varchar), '12:00') duration ( select i.employeeid, convert(varchar, i.logdate, 106) attendancedate, i.logdate intime, ( select min(o.logdate) outtime tmpaccesslogtbl o o.direction = 'out' , o.employeeid = i.employeeid , o.logdate > i.logdate ) outtime tmpaccesslogtbl i.direction = 'in' ) t order t.employeeid, t.attendancedate

fiddle

sql sql-server sql-server-2005