use the timein and timeout together in (1 row)

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I m using sqlserver there is table name of Attendence
Columns of Tables are

1)EmpCode int
2)AttendenceDate datetime
3)AttendenceTime Datetime
4)status tinyint

Data in table would be like this

Code AttendenceDate AttendenceTime status
1001 3/27/2009 3/27/2009 9:05 1
1001 3/27/2009 3/27/2009 7:05 0

1002 3/28/2009 3/27/2009 10:05 1 //user didnot checkout

1003 3/29/2009 3/27/2009 7:05 1
1003 3/29/2009 3/27/2009 7:05 0

1001 3/30/2009 3/27/2009 7:05 1
1001 3/30/2009 3/27/2009 7:05 0
1003 3/29/2009 3/27/2009 7:05 0 //User didnot checkin

when we find "1" in staus it means its checkin and "0" means Check out
i have to make query to show the result like this

Code date Time in Time out
1001 3/27/2009 9:05 7:05

How to make a perfect and happy query for it
Thanks in advance
waiting :-O

basically i get this data inform of txt file which is generated by machine .where user check in it stores the data and he may go for outdoor work to get late not to come back to check out ...

Posted 26-Mar-10 10:19am

qasimidl

Updated 29-Mar-10 3:16am

v5

Add a Solution

3 solutions

Solution 1

You should buy a very basic SQL book and read it. I don't see how this is anything but trivial. Your rules seem to be bizarre - why do you store a wrong date when they did not check out, instead of null ?

Posted 26-Mar-10 10:42am

Christian Graus

Solution 2

I have to agree with CG...it seems very bizarre they way you have set up your tables, and the example data you've provided. The closest I could come with a SQL Query was:

SQL

SELECT CodeTimeIn.Code, CodeTimeIn.TimeIn, TimeOut, CodeTimeIn.AttendanceDate
FROM (SELECT Code, AttendanceDate, AttendanceTime as TimeIn
           FROM test
           WHERE Status=1) AS CodeTimeIn
INNER JOIN
(SELECT Code,AttendanceDate, AttendanceTime as TimeOut
  FROM test
  WHERE Status=0) As CodeTimeOut
ON ((CodeTimeIn.Code=CodeTimeout.Code) AND (CodeTimeIn.AttendanceDate=CodeTimeOut.AttendanceDate))

but, it doesn't work the way you want. (it joins the second 1003 entry with the first one and the third 1003 entry with the first one.

Posted 26-Mar-10 11:03am

William Winner

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)

Sandeep Mewara · Accepted Answer · 2010-03-26T11:09:00

Solution 3

qasimidl wrote:
Thanks in advance
waiting smiley!

I see you had asked around 24 questions till now... lots of response to them and not a single question closed by you as 'Answer accepted'! Yet you sit, wait and smile for another one.... is that correct?

Posted 26-Mar-10 11:09am

Sandeep Mewara