How to insert the data with proper match

Question

0.00/5 (No votes)

See more:

Hi,

I have one table checkinout it include all the transaction of emp log

USERID
Chektime
sensorid

SQL

USERID..Checktime.......sensorid
 
1	8/19/2013 08:19	I
2	8/19/2013 14:53	I
3	8/19/2013 13:19	I
2	8/19/2013 13:22	0
3	8/19/2013 14:52	0
2	8/19/2013 18:22	I
1	8/19/2013 16:21	0
1	8/19/2013 16:46	I
2	8/19/2013 16:45	I
1	8/19/2013 16:45	I

now i have one more table diff..
userID
checkin
Checout

In this table i want insert all the record with sensorid='I'

query:

SQL

INSERT   INTO diff(CHECKIN)  SELECT checktime from checkinout where sensorid='I'

USERID..Checkin........checkout
1 8/19/2013 08:19
2 8/19/2013 14:53
3 8/19/2013 13:19
2 8/19/2013 18:22
1 8/19/2013 16:46
2 8/19/2013 16:45
1 8/19/2013 16:45

Now i want to insert checkout from checkinout table where sensorid ='0'

so i write query:

SQL

INSERT   INTO diff(CHECKOUT)  SELECT checktime from checkinout where sensorid='0'

2 8/19/2013 13:22 0
3 8/19/2013 14:52 0
1 8/19/2013 16:21 0

but the problem is that in checkout column the datetime should come respective to the USERID and checkin

suppose
userid.. checkin...... checkout
1 8/19/2013 08:19 8/19/2013 16:21
as userid first log as 'I' is checkin and first second log as '0'

plz help...

Posted 25-Aug-13 19:39pm

Nawab Ahmad

Updated 25-Aug-13 22:31pm

Herman<T>.Instance

v2

Add a Solution

Comments

CodeBlack 26-Aug-13 4:20am

so you want userid, checkin and checkout column to be filled order by datetime and userid correct ?

Nawab Ahmad 26-Aug-13 8:12am

yes..
i want like
userID Chekin Checkout
------- ----------- ---------------
1 8/19/2013 08:19 8/19/2013 10:19

checkin= sensorID with value I
and checkout = sensorID with value o
i want I and O value in checkin and checkout column..

Azziet 26-Aug-13 6:06am

exactly what data you want to enter in table??

Nawab Ahmad 26-Aug-13 8:17am

i want like
userID Chekin Checkout
------- ----------- ---------------
1 8/19/2013 08:19 8/19/2013 10:19
checkin= sensorID with value I and checkout = sensorID with value o i want I and O value in checkin and checkout column

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jorge J. Martins · Answer 1 · 2013-09-29T04:50:00

I think I have a solution for you.

First, I think your data is a bit incoherent. You have users checking out before checking in and users checking in twice.

If I’m right, you want a pair (USERID, Checkin Time) and the smallest Checkout time, if any, after that Checkin time.

With the correct data I tried this and it seems to work:

SQL

SELECT A.USERID, A.Checktime AS Checkin, MIN(B.Checktime) AS CheckOUT
FROM  (SELECT USERID, Checktime, sensorid
       FROM   checkinout
       WHERE  sensorid = 'I') AS A 
LEFT OUTER JOIN
      (SELECT USERID, Checktime, sensorid
       FROM   checkinout AS checkinout_1
       WHERE  sensorid = '0') AS B 
ON A.USERID = B.USERID AND B.Checktime > A.Checktime
GROUP BY A.USERID, A.Checktime

Don’t forget to mark as answered if it’s the case.