sql - change the output -

sql - change the output -

sorry new sql world statement checkin time different checkout time , remove checkinorout statement see :

select userinfo.badgenumber userid, checkinout.checktime checkin, checkinout.checktime checkout, machines.machinealias, checkinout.checkinorout checkinout inner bring together userinfo on checkinout.userid = userinfo.userid inner bring together machines on checkinout.sensorid = machines.machinenumber (checkinout.checktime > '2014-09-25 00:00:00.000') order checkin

output:

userid checkin checkout machines checkinorout 32 2014-09-25 09:12:57.000 2014-09-25 09:12:57.000 hq checkin 32 2014-09-25 12:58:51.000 2014-09-25 12:58:51.000 hq checkout 32 2014-09-26 18:03:33.000 2014-09-26 18:03:33.000 hq checkin 32 2014-09-26 22:03:11.000 2014-09-26 22:03:11.000 hq checkout 32 2014-09-27 12:57:55.000 2014-09-27 12:57:55.000 hq checkin 32 2014-09-27 17:01:32.000 2014-09-27 17:01:32.000 hq checkout 32 2014-09-28 13:05:03.000 2014-09-28 13:05:03.000 hq checkin 32 2014-09-28 17:35:29.000 2014-09-28 17:35:29.000 hq checkout 32 2014-09-29 09:18:12.000 2014-09-29 09:18:12.000 hq checkin 32 2014-09-29 18:10:43.000 2014-09-29 18:10:43.000 hq checkout 32 2014-09-30 09:12:13.000 2014-09-30 09:12:13.000 hq checkin

i need output

userid checkin checkout machines 32 2014-09-25 09:12:57.000 2014-09-25 12:58:51.000 hq 32 2014-09-26 18:03:33.000 2014-09-26 22:03:11.000 hq 32 2014-09-27 12:57:55.000 2014-09-27 17:01:32.000 hq 32 2014-09-28 13:05:03.000 2014-09-28 17:35:29.000 hq 32 2014-09-29 09:18:12.000 2014-09-29 18:10:43.000 hq

thanks mr @sgeddes when edit statement have result

with cte ( select *, row_number() on (partition userid, checkinorout order checktime) rn checkinout ) select userid, max(case when checkinorout = 'checkin' checktime end) checkin, max(case when checkinorout = 'checkout' checktime end) checkout cte userid=15 , checktime between '2014-9-21 00:00:00.000' , '2014-10-25 00:00:00.000' grouping userid , rn userid checkin checkout 15 null 2014-09-21 18:50:24.000 15 null 2014-09-22 18:06:15.000 15 null 2014-09-23 18:01:30.000 15 null 2014-09-24 16:52:36.000 15 null 2014-09-25 12:58:51.000 15 null 2014-09-26 22:03:11.000 15 null 2014-09-27 17:01:32.000 15 null 2014-09-28 17:35:29.000 15 null 2014-09-29 18:10:43.000 15 null 2014-09-30 18:11:19.000 15 null 2014-10-01 17:52:49.000 15 null 2014-10-12 20:13:10.000 15 2014-09-21 10:17:24.000 2014-10-13 22:13:11.000 15 2014-09-22 09:18:29.000 2014-10-14 21:49:28.000 15 2014-09-23 09:10:15.000 2014-10-15 10:14:09.000 15 2014-09-24 09:43:27.000 2014-10-16 17:55:06.000 15 2014-09-25 09:12:57.000 2014-10-17 23:17:00.000 15 2014-09-26 18:03:33.000 2014-10-20 12:38:22.000 15 2014-09-27 12:57:55.000 2014-10-21 07:31:39.000 15 2014-09-28 13:05:03.000 2014-10-22 05:51:47.000 15 2014-09-29 09:18:12.000 2014-10-24 11:26:06.000 15 2014-09-30 09:12:13.000 null 15 2014-10-01 10:16:59.000 null 15 2014-10-02 10:13:52.000 null 15 2014-10-03 00:59:18.000 null 15 2014-10-11 22:07:06.000 null 15 2014-10-12 09:58:34.000 null 15 2014-10-13 05:09:34.000 null 15 2014-10-14 11:42:58.000 null 15 2014-10-15 04:48:27.000 null 15 2014-10-15 15:49:06.000 null 15 2014-10-16 09:14:21.000 null 15 2014-10-16 21:14:18.000 null 15 2014-10-17 00:59:57.000 null 15 2014-10-18 17:42:26.000 null 15 2014-10-20 01:22:01.000 null 15 2014-10-21 01:24:30.000 null 15 2014-10-22 00:10:34.000 null 15 2014-10-23 20:01:02.000 null 15 2014-10-24 01:08:51.000 null

and need

userid checkin checkout 15 2014-09-21 10:17:24.000 2014-09-21 18:50:24.000 15 2014-09-22 09:18:29.000 2014-09-22 18:06:15.000 15 2014-09-23 09:10:15.000 2014-09-23 18:01:30.000 15 2014-09-24 09:43:27.000 2014-09-24 16:52:36.000 15 2014-09-25 09:12:57.000 2014-09-25 12:58:51.000 15 2014-09-26 18:03:33.000 2014-09-26 22:03:11.000 15 2014-09-27 12:57:55.000 2014-09-27 17:01:32.000

thanks mr @slavoo

what have when seek ....

select u.badgenumber userid, c.checktime checkout, checkingout.time checkin, m.machinealias checkinout c inner bring together userinfo u on c.userid = u.userid inner bring together machines m on c.sensorid = m.machinenumber outer apply ( select top 1 c2.checktime time checkinout c2 c2.checkinorout = 'checkout' , c.userid = c2.userid , c.sensorid = c2.sensorid , c.checktime < c2.checktime order c2.checktime asc ) checkingout (c.checktime between '2014-10-19 00:00:00.000' , '2014-10-27 00:00:00.000' ) , u.badgenumber=660 , c.checkinorout = 'checkin' , checkingout.time not null order checkingout.time

the output:

userid checkout checkin machinealias 660 2014-10-19 01:56:47.000 2014-10-19 17:27:41.000 branch4 660 2014-10-20 02:00:14.000 2014-10-20 17:39:35.000 branch4 660 2014-10-21 01:55:49.000 2014-10-21 16:57:22.000 branch4 660 2014-10-22 01:59:23.000 2014-10-25 16:48:29.000 branch4 660 2014-10-23 16:59:34.000 2014-10-25 16:48:29.000 branch4 660 2014-10-24 16:58:36.000 2014-10-25 16:48:29.000 branch4 660 2014-10-25 01:56:47.000 2014-10-25 16:48:29.000 branch4

and need be

userid checkout checkin machinealias 660 2014-10-20 02:00:14.000 2014-10-19 17:27:41.000 branch4 660 2014-10-21 01:55:49.000 2014-10-20 17:39:35.000 branch4 660 2014-10-22 01:59:23.000 2014-10-21 16:57:22.000 branch4 660 null 2014-10-23 16:59:34.000 branch4 660 2014-10-25 01:56:47.000 2014-10-24 16:58:36.000 branch4 660 2014-10-26 00:55:35.000 2014-10-25 16:48:29.000 branch4

and table read it

select userid ,checktime,checkinorout checkinout userid=80 , checktime between '2014-10-19 00:00:00.000' , '2014-10-27 00:00:00.000' order checktime

and total transaction user

80 2014-10-19 01:56:47.000 checkin 80 2014-10-19 17:27:41.000 checkout 80 2014-10-20 02:00:14.000 checkin 80 2014-10-20 17:39:35.000 checkout 80 2014-10-21 01:55:49.000 checkin 80 2014-10-21 16:57:22.000 checkout 80 2014-10-22 01:59:23.000 checkin 80 2014-10-23 16:59:34.000 checkin 80 2014-10-24 16:58:36.000 checkin 80 2014-10-25 01:56:47.000 checkin 80 2014-10-25 16:48:29.000 checkout 80 2014-10-26 00:55:35.000 checkin

for note have script update check in or out in checkinorout column

to know study finger print check in , check out study , if found no check in leave null , if found check out null leave null ( employ have shift check in 5:00:00 pm , check out 2:00:00 ) ; .

select u.badgenumber userid, c.checktime checkin, checkingout.time checkout, m.machinealias checkinout c inner bring together users u on c.userid = u.userid inner bring together machines m on c.sensorid = m.machinenumber outer apply ( select top 1 c2.checktime time checkinout c2 c2.checkinorout = 'checkout' , c.userid = c2.userid , c.sensorid = c2.sensorid , c.checktime < c2.checktime , cast(c.checktime date) = cast(c2.checktime date) order c2.checktime asc ) checkingout (c.checktime > '2014-09-25 00:00:00.000') , c.checkinorout = 'checkin' order checkin

sql fiddle

sql fiddle after update

or this:

select u.badgenumber userid, c.checktime checkin, checkingout.time checkout, machines.machinealias checkinout c inner bring together users u on c.userid = u.userid inner bring together machines on c.sensorid = machines.machinenumber outer apply ( select top 1 c2.checktime time checkinout c2 c2.checkinorout = 'checkout' , c.userid = c2.userid , c.sensorid = c2.sensorid , c.checktime < c2.checktime , not exists (select * checkinout c3 c3.checkinorout = 'checkin' , c3.userid = c2.userid , c3.sensorid = c2.sensorid , c3.checktime > c.checktime , c3.checktime < c2.checktime) order c2.checktime asc ) checkingout (c.checktime > '2014-09-25 00:00:00.000') , c.checkinorout = 'checkin' order checkin

sqlfiddle

sql sql-server sql-server-2008