combine row data

[monchichi1]

hi,please help.how to combine 2 row data to become 1?
currently from the database table have time,employeeno,date fields.

for example this is the current database :
2008-05-10 00:00:00 M122 1899-12-30 06:24:40.000
2008-05-10 00:00:00 M122 1899-12-30 15:12:03.000

the result i wish to get is:
date empno time1 time2
2008-05-10 00:00:00 M122 1899-12-30 06:24:40.000 1899-12-30 5:12:03.000

this is my current code:
Code:
SELECT OvertimeDate, EmployeeNo,time
FROM Attendance
WHERE (EmployeeNo = 'm122')
order by overtimedate desc

thanks for help.

[砸死牛顿的苹果]

Only 2 rows or more when EmployeeNo is 'm122'?

[vbman2003]

If only 2 rows of data

try:

SELECT OvertimeDate, EmployeeNo,min(time) as time1,max(time) as time2
FROM Attendance
WHERE (EmployeeNo = 'm122')
Group by OvertimeDate, EmployeeNo

[monchichi1]

vbman2003:
hi,thanks is done.now is able to combine the data to become 1 row data.one more question
how to sum of time1 + time2?is possible to do it from sql query?use datediff or...?

2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000 1899-12-30 18:47:25.000
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000 1899-12-30 18:39:47.000

please help.thanks.

[monchichi1]

[Quote=引用 3 楼 monchichi1 的回复:]
vbman2003:
hi,thanks is done.now is able to combine the data to become 1 row data.one more question
how to count the time different between time2 and time1?is possible to do it from sql query?use datediff or...?

2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000 1899-12-30 18:47:25.000
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000 1899-12-30 18:39:47.000

please help.thanks.

[/Quote]

[monchichi1]

hi.please help.now i'm able to do the datediff minute.but why the min result is -695...?
and how to convert the -695 to 11.58 hh:mm so i use -695/60min=11:58.

this is the current query:
SELECT OvertimeDate, EmployeeNo,min(time) as time1,max(time) as time2,datediff(minute,max(time),min(time))as diffmin
FROM Attendance
Group by OvertimeDate, EmployeeNo

this is the current result:
2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000 1899-12-30 18:47:25.000 -696
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000 1899-12-30 18:39:47.000 -685

please help.thanks.

[vbman2003]

monchichi1:
Please sample the data and results

data:

2008-05-10 00:00:00 M122 1899-12-30 06:24:40.000
2008-05-10 00:00:00 M122 1899-12-30 15:12:03.000
2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000
2008-05-07 00:00:00 IM21 1899-12-30 18:47:25.000
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000
2008-05-10 00:00:00 N18 1899-12-30 18:39:47.000

results?

[monchichi1]

vbman2003:
hi,below is the sample result.

this is the data:
2008-05-10 00:00:00 M122 1899-12-30 06:24:40.000
2008-05-10 00:00:00 M122 1899-12-30 15:12:03.000
2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000
2008-05-07 00:00:00 IM21 1899-12-30 18:47:25.000
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000
2008-05-10 00:00:00 N18 1899-12-30 18:39:47.000

this is the result:
2008-05-07 00:00:00 IM21 1899-12-30 07:11:52.000 1899-12-30 18:47:25.000 -696
2008-05-10 00:00:00 N18 1899-12-30 07:14:24.000 1899-12-30 18:39:47.000 -685
2008-05-10 00:00:00 N35 1899-12-30 06:41:41.000 1899-12-30 19:10:21.000 -749
2008-05-10 00:00:00 I595 1899-12-30 07:22:40.000 1899-12-30 17:00:24.000 -578
2008-05-10 00:00:00 I315 1899-12-30 06:48:58.000 1899-12-30 15:11:51.000 -503
2008-05-06 00:00:00 N73 1899-12-30 18:49:21.000 1899-12-30 18:49:21.000 0
2008-05-07 00:00:00 N98 1899-12-30 06:34:42.000 1899-12-30 23:10:20.000 -996

current code:
SELECT OvertimeDate, EmployeeNo,min(time) as timein,max(time) as timeout,datediff(minute,max(time),min(time))as timediff
FROM Attendance
Group by OvertimeDate, EmployeeNo

[砸死牛顿的苹果]

datediff(minute,max(time),min(time))
==>
datediff(minute,min(time),max(time))