question

eng-mohamed avatar image
eng-mohamed 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
sql-server-2008-r2columnsql2008
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
"3059 2014-06-05 07:57:22.000 213 checkin " this should be a checkout or checkin ? You have to explain the rules behind
0 Likes 0 ·

1 Answer

·
sdoubleday avatar image
sdoubleday answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.