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

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

use the timein and timeout together in (1 row)

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

use the timein and timeout together in (1 row)

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us