How to find the missing information with ID

Question

0.00/5 (No votes)

See more:

I have table Log contain Three column they are:

EMPID 
Checktime
SENSORID

When employee show the card in RFID mechanic.
The data will come and sit in table like

EMPID ***     Checktime         ****  SENSORID
1234  ***     2010-06-30 07:31  ****    1
 253  ***     2010-06-30 08:31  ****    2

1234  ***     2010-06-30 18:31  ****    3

SENSORID is RFID machine eg: 1,2,3

SENSORID 1 and 2 is for login employee can use any one to login and SENSORID 3 is for logout, so I want to find all EMPID whose login information is in table but logout information is not there because SENSORID 3 is not there.

Posted 2-Aug-13 22:47pm

Nawab Ahmad

Updated 2-Aug-13 23:20pm

André Kraak

v3

Add a Solution

2 solutions

Solution 2

Try this...

SQL

Select * From TableName t 
where not exists (Select t2.EmpId From TableName t2 where t2.SensorId =3 and t2.EmpId=t.EmpId)

If you want the Records who has not log out on that particular day...

SQL

Select * From #Temp t
where not exists (Select t2.EmpId From #Temp t2 where t2.SensorId =3 and t2.EmpId=t.EmpId and DATEDIFF(D,t.CheckTime,t2.CheckTime)=0) and t.SensorId <>3

Tested...

SQL

Create table #Temp(EmpId Int, CheckTime DateTime, SensorId Int)
Insert into #Temp
Select '1234','2010-06-30 07:31',1 Union all
Select '253','2010-06-30 08:31',2 Union all
Select '1234','2010-06-30 18:31',3

Select * From #Temp t
where not exists (Select t2.EmpId From #Temp t2 where t2.SensorId =3 and t2.EmpId=t.EmpId)

Drop Table #Temp

Output:

EmpId	CheckTime	             SensorId
-----   -----------                  ---------
253	2010-06-30 08:31:00.000	        2

Posted 4-Aug-13 19:00pm

Raja Sekhar S

Updated 4-Aug-13 20:59pm

v3

Comments

Nawab Ahmad 5-Aug-13 1:46am

Hi,
Thanks for your reply......

Create table nawab(EmpId Int, CheckTime DateTime, SensorId Int)
Insert into nawab
Select '1234','2010-06-30 07:31',1 Union all
Select '253','2010-06-30 08:31',2 Union all
Select '1234','2010-06-30 18:31',3 union all
Select '253','2010-07-28 6:31',1 union all
Select '1234','2010-07-28 8:31',2 union all
Select '1234','2010-07-29 9:31',1

Select * From nawab t
where not exists (Select t2.EmpId From nawab t2 where t2.SensorId =3 and t2.EmpId=t.EmpId)

plz check this.....

Raja Sekhar S 5-Aug-13 2:56am

Th output will be only records of 253 as 1234 has sensor id 3... if u want to use for only one day... Check my Updated Solution...

Maciej Los 5-Aug-13 3:03am

It should works perfect.
+5!

Raja Sekhar S 5-Aug-13 3:05am

Thank you maciej los...

Nawab Ahmad 8-Aug-13 1:48am

sSqlQuery = "Select * from canteen where EPMID='" + Text1.Text + "' and canteen.CheckTime>='" & StartDate & "' and Canteen.CheckTime<='" & EndDate & "'"
If rstQuery.State = 1 Then rstQuery.Close
rstQuery.Open sSqlQuery, dBConnTT, adOpenStatic, adLockReadOnly

I wrote this code in vb 6.0 it is showing invalid Column name EMPID..
plz help

Raja Sekhar S 8-Aug-13 5:40am

The Error itself Clearly Explains What the Problem is... So Check the Column Name...

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)

André Kraak · Accepted Answer · 2013-08-02T23:35:00

Solution 1

The following statement should give all EMPID's who never logged out:

SQL

SELECT DISTINCT EMPID, Checktime
  FROM Log
 WHERE (SENSORID = 1 OR SENSORID = 2)
   AND EMPID NOT IN (SELECT DISTINCT EMPID
                       FROM EMPID
                      WHERE SENSORID = 3)

If you want the EMPID's who did not logout on the same day as they logged in use:

SQL

SELECT DISTINCT LogIn.EMPID, Login.Checktime
  FROM Log AS LogIn
 WHERE (LogIn.SENSORID = 1 OR LogIn.SENSORID = 2)
   AND LogIn.EMPID NOT IN (SELECT DISTINCT LogOut.EMPID
                             FROM EMPID AS LogOut
                            WHERE LogOut.SENSORID = 3
                              AND DATEDIFF(day, LogIn.Checktime, LogOut.Checktime) = 0)

Above statement assumes that Checktime is stored as DateTime[^] in the table.

Reference: DATEDIFF[^]

Posted 2-Aug-13 23:35pm

André Kraak

Updated 3-Aug-13 0:54am

v3

Comments

Nawab Ahmad 3-Aug-13 5:57am

Hi,thanks for your reply...
here i am only using one table log but you are using EMPID also like.. FROM EMPID AS LogOut

André Kraak 3-Aug-13 6:51am

The second SQL query uses the Log table two times and to keep them apart each of them is given an alias (http://www.w3schools.com/sql/sql_alias.asp). This way we can use a field from each in the DATEDIFF(day, LogIn.Checktime, LogOut.Checktime) statement. If we did not use aliases than the query would not known which field to use.

Nawab Ahmad 3-Aug-13 6:48am

it is showing only empid ,if i want see the checktime then waht i have to do...

André Kraak 3-Aug-13 6:56am

You add the Checktime field to the SELECT statement; I have updated the answer.
You should note although the DISTINCT (http://www.w3schools.com/sql/sql_distinct.asp) keyword is used you now may get the same EMPID multiple times is he/she logged in multiple times.

Nawab Ahmad 5-Aug-13 0:07am

Hi,
In your query in 5th line you use From EMPID, when i m executing this it shows error.
But when i changed it to Fro Log (my table name) then it is executing......

Maciej Los 5-Aug-13 3:02am

+5!