Change output
Sorry I'm new to sql world, this statement can be check time with different check time and remove checkinorout from statement as you can see well:
SELECT
USERINFO.Badgenumber AS USERID,
CHECKINOUT.CHECKTIME AS Checkin,
CHECKINOUT.CHECKTIME AS Checkout,
Machines.MachineAlias,
CHECKINOUT.checkinorout
FROM CHECKINOUT
INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
INNER JOIN Machines ON CHECKINOUT.SENSORID = Machines.MachineNumber
WHERE (CHECKINOUT.CHECKTIME > '2014-09-25 00:00:00.000')
order by 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 an output similar to this
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, but when I edit your statement to have the result
with cte as (
select *,
row_number() over (partition by userid, checkinorout order by CHECKTIME) rn
from CHECKINOUT
)
select userid,
max(case when checkinorout = 'checkin' then CHECKTIME end) checkin,
max(case when checkinorout = 'checkout' then CHECKTIME end) checkout
from cte where USERID=15 and CHECKTIME between '2014-9-21 00:00:00.000' and '2014-10-25 00:00:00.000'
group by 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 I need him to be
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 do I have when I try ....
SELECT
u.Badgenumber AS USERID,
c.CHECKTIME AS Checkout,
checkingOut.time AS Checkin,
m.MachineAlias
FROM CHECKINOUT c
INNER JOIN USERINFO u ON c.USERID = u.USERID
INNER JOIN Machines m ON c.SENSORID = m.MachineNumber
OUTER APPLY
(
SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2
WHERE c2.checkinorout = 'Checkout'
AND c.USERID = c2.USERID
AND c.SENSORID = c2.SENSORID
AND c.CHECKTIME < c2.CHECKTIME
order by c2.CHECKTIME asc
) as checkingOut
WHERE (c.CHECKTIME between '2014-10-19 00:00:00.000' and '2014-10-27 00:00:00.000' )
and u.Badgenumber=660
AND c.checkinorout = 'Checkin'
AND checkingOut.time is not null
order by checkingOut.time
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 I need him to 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 this is the table I read from it
select USERID ,CHECKTIME,checkinorout from CHECKINOUT where USERID=80 and CHECKTIME between '2014-10-19 00:00:00.000' and '2014-10-27 00:00:00.000'
order by CHECKTIME
and this is a complete transaction for this 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 your note, I have a script to update it, check or exit the checkinorout column
to find out that this report will be checked for printing with a finger and check the report and if he did not find the receipt on vacation, then Null and if he found that the receipt was rejected, leave it zero (this usage has a shift at 5:00:00 and check-out 2:00:00);.
+3
source to share
2 answers
SELECT
u.Badgenumber AS USERID,
c.CHECKTIME AS Checkin,
checkingOut.time AS Checkout,
m.MachineAlias
FROM CHECKINOUT c
INNER JOIN users u ON c.USERID = u.USERID
INNER JOIN Machines m ON c.SENSORID = m.MachineNumber
OUTER APPLY
(
SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2
WHERE c2.checkinorout = 'Checkout'
AND c.USERID = c2.USERID
AND c.SENSORID = c2.SENSORID
AND c.CHECKTIME < c2.CHECKTIME
AND CAST(c.CHECKTIME as DATE) = CAST(c2.CHECKTIME as DATE)
order by c2.CHECKTIME asc
) as checkingOut
WHERE (c.CHECKTIME > '2014-09-25 00:00:00.000')
AND c.checkinorout = 'Checkin'
order by checkin
Or that:
SELECT
u.Badgenumber AS USERID,
c.CHECKTIME AS Checkin,
checkingOut.time AS Checkout,
Machines.MachineAlias
FROM CHECKINOUT c
INNER JOIN users u ON c.USERID = u.USERID
INNER JOIN Machines ON c.SENSORID = Machines.MachineNumber
OUTER APPLY
(
SELECT top 1 c2.CHECKTIME as time FROM CHECKINOUT c2
WHERE c2.checkinorout = 'Checkout'
AND c.USERID = c2.USERID
AND c.SENSORID = c2.SENSORID
AND c.CHECKTIME < c2.CHECKTIME
AND NOT EXISTS (SELECT * FROM CHECKINOUT c3
WHERE c3.checkinorout = 'Checkin'
AND c3.USERID = c2.USERID
AND c3.SENSORID = c2.SENSORID
AND c3.CHECKTIME > c.CHECKTIME
AND c3.CHECKTIME < c2.CHECKTIME)
order by c2.CHECKTIME asc
) as checkingOut
WHERE (c.CHECKTIME > '2014-09-25 00:00:00.000')
AND c.checkinorout = 'Checkin'
order by checkin
+1
source to share
You can use ROW_NUMBER()
to set grouping between your checks and checks. Then you need to rotate your results - you can use MAX
with CASE
to do this:
Here's a simplified version using a generic table expression:
with cte as (
select *,
row_number() over (partition by userid, checkinorout order by checkin) rn
from results
)
select userid,
max(case when checkinorout = 'checkin' then checkin end) checkin,
max(case when checkinorout = 'checkout' then checkout end) checkout,
machines
from cte
group by userid, machines, rn
Edit, given your comments, can you just use the suggestion HAVING
to delete entries NULL
?
having max(case when checkinorout = 'checkin' then checkin end) is not null
and max(case when checkinorout = 'checkout' then checkout end) is not null
+1
source to share