出勤相关查询 [英] Attendance Related Query
问题描述
我对出席率有一个疑问，在我的表中我有以下详细信息，如
ECode LogDate
- ---------------
C0001 2012-12-12 07：56：05.000
C0001 2012-12-12 17： 47：02.000
E5890 2012-12-12 07：54：59.000
E5890 2012-12-12 08：23：23.000
E5890 2012-12-12 08：23：42.000
E5890 2012-12-12 08：30：10.000
E5890 2012-12-12 08：30：41.000
E5890 2012-12-12 08：31：28.000
E5890 2012-12-12 08：32：13.000
E5890 2012-12-12 08：35：52.000
E5890 2012-12-12 08：42：59.000
E5890 2012-12-12 14：08：26.000
E5890 2012-12-12 19：14：35.000
E5890 2012-12-12 19：19：53.000
E5890 2012-12-12 19：22：32.000
E6000 2012-12-12 17：32：32.000
在此我需要输出如
代码日期IN Out状态
----------- --------------------------------
C0001 2012-12-12 07:56 17 ：47 P
E5890 2012-12-12 07:54 19:22 P
E6000 2012-12-12 17:32我
亲切的建议如何做到这一点，感谢您的支持。
谢谢和问候，
Balaji.D
Hi,
I am having one doubt regarding the attendance , In my Table I have following Details like
ECode LogDate
-----------------
C0001 2012-12-12 07:56:05.000
C0001 2012-12-12 17:47:02.000
E5890 2012-12-12 07:54:59.000
E5890 2012-12-12 08:23:23.000
E5890 2012-12-12 08:23:42.000
E5890 2012-12-12 08:30:10.000
E5890 2012-12-12 08:30:41.000
E5890 2012-12-12 08:31:28.000
E5890 2012-12-12 08:32:13.000
E5890 2012-12-12 08:35:52.000
E5890 2012-12-12 08:42:59.000
E5890 2012-12-12 14:08:26.000
E5890 2012-12-12 19:14:35.000
E5890 2012-12-12 19:19:53.000
E5890 2012-12-12 19:22:32.000
E6000 2012-12-12 17:32:32.000
In this I need the output like
Code Date IN Out Status
-------------------------------------------
C0001 2012-12-12 07:56 17:47 P
E5890 2012-12-12 07:54 19:22 P
E6000 2012-12-12 17:32 I
Kindly Suggest How to do this,Thanks for your support.
Thanks & Regards,
Balaji.D
推荐答案
使用此...........：）
Use this ........... :)
SELECT ECode , MIN(logdate) logdate, CASE WHEN substring(convert(varchar(20), MIN(logdate), 100), 18, 5) = 'AM' THEN 'P' ELSE 'I' END AS STATUS FROM Stu_Log
GROUP BY ECode
输出
OUTPUT
ECode logdate STATUS
---------- ----------------------- ------
C0001 2012-12-12 07:56:05.000 P
E5890 2012-12-12 07:54:59.000 P
E6000 2012-12-12 17:32:32.000 I
问候，
Vijay
Regards,
Vijay
拿这个:)
Take this :)
SELECT ECode , MIN(logdate) [LOGIN], MAX(logdate) [LOGOUT], CASE WHEN MIN(logdate)<> MAX(logdate) THEN 'P' ELSE 'I' END AS STATUS FROM Stu_Log
GROUP BY ECode
ECode LOGIN LOGOUT STATUS
---------- ----------------------- ----------------------- ------
C0001 2012-12-12 07:56:05.000 2012-12-12 17:47:02.000 P
E5890 2012-12-12 07:54:59.000 2012-12-12 08:30:10.000 P
E6000 2012-12-12 17:32:32.000 2012-12-12 17:32:32.000 I
问候，
Vijay
Regards,
Vijay
按照我的说法你必须改变你的桌面设计.. ..它应该是这样的...
创建表EmpLogin
（Id int，
LoginDate日期，
LoinTime时间，
退出时间）
登录时，新行将插入和注销将为NULL，并且对于注销，同一行将随着时间更新.... :)
问候，
Vijay
As per me you have to change your table design .... it should be like this ...
Create Table EmpLogin
(Id int ,
LoginDate date,
LoinTime time ,
Logout time )
At the time of log in one new row will be insert and Logout will be NULL, and for log out same row will be update with the out time .... :)
Regards ,
Vijay
这篇关于出勤相关查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
