asked
insert column based another column ?
I want to insert to the column checkinorout value checkin or checkout in first checkin and then checkout and then checkin based on the same SENSORID and the same checktime as you see I want this select USERID, checktime, SENSORID, checkinorout from CHECKINOUT order by CHECKTIME Output: USERID checktime SENSORID checkinorout 3059 2014-05-31 09:02:08.000 213 NULL 3059 2014-05-31 17:27:23.000 213 NULL 3059 2014-05-31 19:27:23.000 213 NULL 3059 2014-06-01 08:03:07.000 213 NULL 3059 2014-06-01 17:23:59.000 213 NULL 3059 2014-06-02 08:05:11.000 213 NULL 3059 2014-06-02 17:21:22.000 213 NULL 3059 2014-06-03 07:47:21.000 213 NULL 3059 2014-06-05 07:57:22.000 216 NULL 3059 2014-06-05 09:57:22.000 216 NULL 3059 2014-06-05 11:57:22.000 216 NULL 3059 2014-06-05 09:57:22.000 213 NULL to be select USERID, checktime, SENSORID, checkinorout from CHECKINOUT order by CHECKTIME Output: USERID checktime SENSORID checkinorout 3059 2014-05-31 09:02:08.000 213 checkin 3059 2014-05-31 17:27:23.000 213 checkout 3059 2014-05-31 19:27:23.000 213 checkin 3059 2014-06-01 08:03:07.000 213 checkin 3059 2014-06-01 17:23:59.000 213 checkout 3059 2014-06-02 08:05:11.000 213 checkin 3059 2014-06-02 17:21:22.000 213 checkout 3059 2014-06-03 07:47:21.000 213 checkin 3059 2014-06-05 07:57:22.000 216 checkin 3059 2014-06-05 09:57:22.000 216 checkout 3059 2014-06-05 11:57:22.000 216 checkin 3059 2014-06-05 07:57:22.000 213 checkin 3059 2014-06-05 09:57:22.000 213 checkout
Can I confirm - you're after the first line of the day to be a "checkin", then the second of each day to be "checkout", the third to be "checkin", etc?
eng-mohamed, the row_number function should work for you here if you partition it by the userid and sensorid. For example: --I am assuming that both user ID and sensor ID can vary --Setup, with slightly doctored rows ;with mycte AS ( SELECT USERID, CAST(CheckTime AS DATETIME) CheckTime, SENSORID FROM (VALUES (3060,'2014-05-31 09:02:08.000',213) ,(3060,'2014-05-31 17:27:23.000',213) ,(3060,'2014-05-31 18:27:23.000',213) ,(3060,'2014-07-31 18:27:23.000',213) ,(3059,'2014-05-31 19:27:23.000',213) ,(3059,'2014-06-01 08:03:07.000',213) ,(3059,'2014-06-05 07:57:22.000',216) ,(3059,'2014-06-05 09:57:22.000',216) ) AS a(UserID, CheckTIme, SensorID) ) --Example of use of Row_Number SELECT UserID ,CheckTime ,SensorID ,CASE WHEN ROW_NUmber() OVER (PARTITION BY mycte.userid, mycte.sensorid ORDER BY CheckTime ASC) % 2 = 1 THEN 'Checkin' WHEN ROW_NUmber() OVER (PARTITION BY mycte.userid, mycte.sensorid ORDER BY CheckTime ASC) % 2 = 0 THEN 'Checkout' ELSE 'ERROR' END AS CheckInOrOut FROM mycte ORDER BY CheckTime
17 People are following this question.
