time registration problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

time registration problem

Author

Topic

mr_magician
Starting Member

1 Post

Posted - 2010-02-04 : 15:20:44

Hi all,

Maybe you'll be able to help me. I want to create a database from which I will be able to generate reports of employee's work time.
Data will be imported from external application. Here is example of input data:

employee_number;time;in_or_out;
15;04-01-2010 08:00;0
15;04-01-2010 12:00;1
15:04-01-2010 13:00;0
15;04-01-2010 16:00;1
20;04-01-2010 07:30;0
20;04-01-2010 16:00;1
15;05-01-2010 08:00;0
15;05-01-2010 16:00;1
etc.

In the last column: 0 - employee entered the office, 1 - employee left the office.

Should import data to SQL server as it is or I need to add some id column.
I also do not have any clue how to write a query to sum up time employee spent in the office during the day. There is a need to compare dates from different rows.

Thanks in advance
mr_magician

Biscuithead
Starting Member

30 Posts

Posted - 2010-02-04 : 16:21:00

You def need an ID column. Otherwise you'll never be able to generate any type of report due to rows potentially having the same data.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-04 : 16:35:56

It would help if you set up your data with DDL and Insert statements. Additionally, providing the expected output will help us to produce a solution that works for you. Here is some code that might help get you going:

DECLARE @Foo TABLE (employee_number INT, [time] DATETIME, in_or_out TINYINT)

INSERT @Foo
SELECT 15, '04-01-2010 08:00', 0
UNION ALL SELECT 15, '04-01-2010 12:00', 1
UNION ALL SELECT 15, '04-01-2010 13:00', 0
UNION ALL SELECT 15, '04-01-2010 16:00', 1
UNION ALL SELECT 20, '04-01-2010 07:30', 0
UNION ALL SELECT 20, '04-01-2010 16:00', 1
UNION ALL SELECT 15, '05-01-2010 08:00', 0
UNION ALL SELECT 15, '05-01-2010 16:00', 1
;

WITH Emp 
AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY employee_number ORDER BY employee_number, [time], in_or_out) AS RowNum
	FROM
		@Foo
)

SELECT 
	A.employee_number, 
	DATEADD(DAY, DATEDIFF(DAY, 0, A.Time), 0) AS [Day],
	SUM(DATEDIFF(MINUTE, A.[time], B.[time])) AS WorkingMinutesPerDay
FROM 
	Emp AS A
INNER JOIN
	Emp AS B
	ON A.RowNum + 1 = B.RowNum
	AND A.in_or_out <> B.in_or_out
	AND A.in_or_out = 0
GROUP BY
	A.employee_number, 
	DATEADD(DAY, DATEDIFF(DAY, 0, A.Time), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources