如何进行此SELECT查询...? [英] How do I make this SELECT query...?

问题描述

首先，英语不是我的母语，因此深表歉意.现在的问题是:我被要求制作一份报告，以显示公司员工在可变时期内的总工作时间 .我选择在.NET上的Visual Studio 2013上做一个解决方案，因为我对Web开发不满意.他们将使用的数据库交给我一个.mdb访问文件.
我的问题是，签入和签出时间在不同的寄存器上，所以我找不到简单的方法来仅执行一个选择查询来返回所需的信息.
下表如下(我将仅显示所需的列)

USERINFO:USERID，名称等.
CHECKINOUT:USERID，CHECKTIME，CHECKTYPE等

示例:ID为1的用户在2015年3月26日10:03:01签入，并在2015年3月26日17:59:10签出，因此其注册方式如下:

First of all, english is not my mother tongue, so advanced apologies. Now the problem: I was asked to make a report presenting the total worked hours of a company employees on a variable period of time. I chose to make a solution on Visual Studio 2013 on .NET because I''m rusty with web development. They hand me over an access .mdb file with the db they use.
My problem is that the check in and check out time are on different registers, so I can''t find an easy way to make only one select query that returns the needed info.
The tables are as follows (I''ll only show the columns needed)

USERINFO: USERID, name, etc.
CHECKINOUT: USERID, CHECKTIME, CHECKTYPE, etc.

Example: User with ID 1 checks in at 26/03/2015 10:03:01 and checks out at 26/03/2015 17:59:10, so it registers as follows:

USERID | CHECKTIME           | CHECKTYPE |...
-------+---------------------+-----------+---
    1  | 26/03/2015 10:03:01 |   "I"     |...
    1  | 26/03/2015 17:59:10 |   "O"     |...

如您所见，每次签入/签出都在其自己的寄存器中，这非常成问题...我曾想过要对每次签入和签出的汇总进行区别，但是当员工签入时于23小时开始，并于第二天的01日退房，该服务不再可用.
我还发现重复的寄存器在几秒钟内有所不同，例如:

As you can see every check in/out is in it''s own register, and it''s very problematic... I thought on making the difference of the sumatory of every check In and check Out but when a employee checks in at 23 hs and checks out at 01 of the next day, that doesn''t work anymore.
I also found repeated registers that differs on a couple of seconds, something like:

USERID | CHECKTIME           | CHECKTYPE |...
-------+---------------------+-----------+---
    1  | 26/03/2015 17:59:10 |   "O"     |...
    1  | 26/03/2015 17:59:16 |   "O"     |...
    1  | 26/03/2015 17:59:23 |   "O"     |...

我不知道他们是怎么到达那里的，但是他们也有问题...

查询的预期结果应如下所示:

I don''t know how they got there but they are also problematic...

The expected result of the query should look something like this:

    NAME   | WORKED HOURS |
-----------+--------------+
 John Doe  |     50       |
 Jane Doe  |     63       |
 ...       |     ...      |

每个注册人员都应指出每个员工(用户)在一段时间(通过Windows窗体应用程序选择)内的总工作时间.

我希望我能清楚地解释自己.感谢您的阅读！

Every register should indicate the total worked hours within a period of time (selected via a windows form app) for every employee (user).

I hope I explained myself clearly. Thanks for reading!

推荐答案

尝试这个

try this one

CREATE TABLE #UserCheckInOut(UserId INT,
							CheckTime DATETIME,
							CheckType CHAR)

--data
--all detetime in formate yyyy-mm-dd hh:mm:ss(24h)	
INSERT	
	INTO #UserCheckInOut
	VALUES
	--in and out
	(1, CONVERT(DATETIME, '2015-02-20 09:00:00', 120), 'I'),
	(1, CONVERT(DATETIME, '2015-02-20 18:00:00', 120), 'O'),
	
	--in and multiple out
	(1, CONVERT(DATETIME, '2015-02-21 09:00:00', 120), 'I'),
	(1, CONVERT(DATETIME, '2015-02-21 18:00:00', 120), 'O'),
	(1, CONVERT(DATETIME, '2015-02-21 18:00:10', 120), 'O'),	
	(1, CONVERT(DATETIME, '2015-02-21 18:00:20', 120), 'O'),
	
	--multiple in and multiple out
	(1, CONVERT(DATETIME, '2015-02-22 09:00:00', 120), 'I'),
	(1, CONVERT(DATETIME, '2015-02-22 12:00:00', 120), 'O'),
	(1, CONVERT(DATETIME, '2015-02-22 14:00:10', 120), 'I'),	
	(1, CONVERT(DATETIME, '2015-02-22 18:00:20', 120), 'O'),

	--in and out at another date
	(1, CONVERT(DATETIME, '2015-02-25 20:00:00', 120), 'I'),
	(1, CONVERT(DATETIME, '2015-02-26 06:00:00', 120), 'O');
	
	
--main query	
WITH UserChecks(UserId, CheckTime, CheckType, Id) AS(
	SELECT *, ROW_NUMBER() over (ORDER BY CheckTime) AS Id
		FROM #UserCheckInOut
		WHERE UserId = 1
),
UserCheckPeer AS(
	SELECT UserId, CheckTime AS CheckInTime,
		(
			SELECT TOP(1) CheckTime
				FROM UserChecks AS ckOut
				WHERE ckOut.CheckType = 'O'
				AND ckOut.Id > ckIn.Id
				AND ckOut.Id < (SELECT TOP(1) Id FROM UserChecks WHERE CheckType = 'I' AND Id > ckIn.Id)
				ORDER BY ckOut.CheckTime DESC
				
		) AS CheckOutTime
		FROM UserChecks AS ckIn
		WHERE CheckType = 'I'
)
SELECT *
	FROM UserCheckPeer

你好..
我的自我Kalpesh Jain
请告诉我您想要的输出是什么..?
这样我就可以正确识别您的问题.

Hello ..
My Self Kalpesh Jain
Please tell me what is the output you want here .. ?
So I can identify properly your question..

这篇关于如何进行此SELECT查询...?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！