How do I make this SELECT query...?

Question

4.20/5 (2 votes)

See more:

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

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:

VB

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

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

XML

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

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!

Posted 26-Mar-15 16:10pm

Florencia P.

Updated 27-Mar-15 5:54am

v3

Add a Solution

Comments

Suvendu Shekhar Giri 26-Mar-15 23:09pm

What is the output you want? Can you just show an example?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**DiponRoy** · Answer 1 · 2015-03-30T07:36:00

try this one

SQL

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 · Answer 2 · 2015-03-26T18:31:00

Solution 1

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