需要SQL查询[转发] [英] Need SQL query [repost]

问题描述

我正在使用带有表名称Attendence的SQL Server数据库.表格中的列为:

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

表中的数据将如下所示:

I m using a SQL Server database with a table names Attendence. The columns in the table are:

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

Data in the 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


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

状态的值"1"表示签入，值"0"表示签出.我需要查询以显示如下结果:

A value of "1" for Status means check-in and "0" means check-out. I need a query to show the results like this:

Code date      Timein Timeout
1001 3/27/2009 9:05   7:05

如何对此进行完美的查询?

在此先谢谢您.

How can I make the perfect query for this?

Thanks in advance.

推荐答案

我认为您的数据库设计错误.如果使用以下列，查询将没有问题，并且该表将占用更少的磁盘空间:

EmpCode int
AttendenceDate smalldatetime
TimeIn SmallDateTime
TimeOut SmallDateTime

I think your database design is wrong. If you use the folowing columns, you will have no problems with your query, and the table will take less diskspace:

EmpCode int
AttendenceDate smalldatetime
TimeIn SmallDateTime
TimeOut SmallDateTime

您希望通过重新发布相同的问题来获得答案或更佳的答案?

And you expect to get an answer, or better answer, by reposting the same question?

这篇关于需要SQL查询[转发]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！