SQL游标查询是以下问题所必需的 [英] Sql cursor Query is Required for Below Problem

问题描述

我在sql表中有一个访问记录表数据，我需要计算在办公室的工作时间，也需要计算他规定的工作时间.
任何人都可以使用光标进行查询
e，g下表提供了一位员工的详细信息.
DoorNo 1是入口门代码5是出口门代码
记录的时间是访问的时间
accesscardNo是员工的卡号，
EntryExit表示A001是条目A0002是从办公室退出

Hi,
I have a access recording table data in the sql table i need to calculate number of hours is in office and also i need to calculate the number of hours he statyed out.
Any one can use cursor for this and give query
e,g The below table gives detail of the table of one employee.
DoorNo 1 is entry door code 5 is exit door code
Recordedtime is time accessed
accesscardNo is the card No of Employee,
EntryExit means A001 is Entry A0002 is Exit from office

Slno   DoorNo   	RecordedTime        AccessCardNo EntryExit
1	1	2011-09-08 09:23:37.000 	0050	   A0001 
1	5	2011-09-08 12:11:34.000	        0050	   A0002 
1	1	2011-09-08 13:49:59.000	        0050	   A0001 
1	5	2011-09-08 14:44:32.000	        0050	   A0002 
1	1	2011-09-08 14:46:43.000	        0050	   A0001 
1	5	2011-09-08 17:46:31.000	        0050	   A0002 
1	1	2011-09-08 17:47:32.000	        0050	   A0001 
1	5	2011-09-08 17:52:48.000	        0050	   A0002 
1	1	2011-09-08 17:58:18.000	        0050	   A0001 
1	5	2011-09-08 18:49:10.000	        0050	   A0002 


Thanks in advance

推荐答案

您好，
请执行以下步骤:
1.从AccessCardNo和EntryExit字段中删除所有空格(如果有空格)
注意:我只是复制您提供的表值并留有空白，因此将其删除.

2.执行以下查询，它将为您留在特定员工办公室的总秒数

Hi,
Follow the below steps:
1. Remove all blank spaces from AccessCardNo and EntryExit field (if any blank space)
Note: I just copy your provided table value and got blank space so remove them.

2. Execute the below query, it will give you Total Seconds stayed in office of a particular employee

select datediff(s,(SELECT TOP 1 RecordedTime AS Entry FROM tblTest Where AccessCardNo='0050' AND EntryExit='A0001'
ORDER BY RecordedTime ASC),(SELECT TOP 1 RecordedTime AS Out FROM tblTest Where AccessCardNo='0050' AND EntryExit='A0002'
ORDER BY RecordedTime DESC)) AS TotalSecondsInOffice

3.将值保存在变量中

4.如果将结果除以3600，则会得到小时
5.如果需要分钟，然后将剩余"值除以60，则将得到分钟

注意:如果只需要一个小时，请运行以下查询

3. Keep the value in a variable

4. If you divide the result by 3600 then you will get hour
5. If you need Minute then divide the Remainder value by 60 then you will get Minute

Note: If you need only hour then run the below query

SELECT DATEDIFF(s,(SELECT TOP 1 RecordedTime AS Entry FROM tblTest Where AccessCardNo='0050' AND EntryExit='A0001'
ORDER BY RecordedTime ASC),(SELECT TOP 1 RecordedTime AS Out FROM tblTest Where AccessCardNo='0050' AND EntryExit='A0002'
ORDER BY RecordedTime DESC))/3600 AS TotalHoursInOffice

谢谢，
马蒙

Thanks,
Mamun

这篇关于SQL游标查询是以下问题所必需的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！