在(1行)中一起使用timein和timeout [英] use the timein and timeout together in (1 row)

问题描述

我在使用sqlserver时有Attendence的表名称
表的列是

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

表中的数据会像这样

Code AttendenceDate AttendenceTime状态
1001 3/27/2009 3/27/2009 9:05 1
1001 2009年3月27日2009年3月27日7:05 0

1002 3/28/2009 3/27/2009 10:05 1//用户未结帐

1003 2009年3月29日2009年3月27日7:05 1
1003 2009年3月29日2009年3月27日7:05 0

1001 3/30/2009 3/27/2009 7:05 1
1001 3/30/2009 3/27/2009 7:05 0
1003 2009年3月29日2009年3月27日7:05 0//用户未签到

当我们在状态中发现"1"时，表示签入，而"0"时表示签出
我必须进行查询才能显示这样的结果

代码日期超时时间
1001 3/27/2009 9:05 7:05


如何对其进行完美和满意的查询
在此先感谢
等待中:-O

基本上我得到了机器生成的txt文件的数据通知.用户在其中检入存储的数据，他可能会去户外工作到很晚才不回来检出...

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 ...

推荐答案

qasimidl写道:

qasimidl wrote:

在此先感谢
等待笑脸！

Thanks in advance
waiting smiley!

我看到到目前为止，您已经问了大约24个问题……对它们的回答很多，而没有一个问题被您回答为已接受答案"！然而，您坐着，等待，然后微笑着…….那是正确的吗?

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?

您应该购买一本非常基础的SQL书籍并阅读.除了琐碎的事，我什么都看不到.您的规则似乎很奇怪-为什么您在未签出时存储错误的日期而不是null?

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 ?

我必须同意CG ...看起来很奇怪设置表格的方式以及您提供的示例数据.我可以使用SQL查询获得的最接近的是:

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:

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))

但是，它并不能按照您想要的方式工作. (它将第二个1003条目与第一个条目连接在一起，将第三个1003条目与第一个条目连接在一起.

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.

这篇关于在(1行)中一起使用timein和timeout的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！