Special Pivot Operation - 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 2008 Forums

Transact-SQL (2008)

Special Pivot Operation

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2012-01-23 : 06:58:55

I am using SQL Server 2005. I have an employee time recording system as described below in table @EmployeeTimeSwipeDetails. I need to get the result as shown below.

How do I get the expected result
1) Without using PIVOT
2) With using PIVOT

NOTE: There should be no loops and no cursors.
--Expected Result
(ID,EntryTime,ExitTime)
(1, '2012-01-23 17:18:00.000', '2012-01-23 17:20:00.000')
(2, '2012-01-23 17:22:00.000', '2012-01-23 17:24:00.000')
(1, '2012-01-23 17:26:00.000', '2012-01-23 17:28:00.000')

--Available Data
DECLARE @EmployeeTimeSwipeDetails TABLE(empID INT, [Time] DATETIME,[Action] VARCHAR(100))

INSERT INTO @EmployeeTimeSwipeDetails VALUES (1, '2012-01-23 17:18:00.000','ENTER')
INSERT INTO @EmployeeTimeSwipeDetails VALUES (1, '2012-01-23 17:20:00.000','EXIT')
INSERT INTO @EmployeeTimeSwipeDetails VALUES (2, '2012-01-23 17:22:00.000','ENTER')
INSERT INTO @EmployeeTimeSwipeDetails VALUES (2, '2012-01-23 17:24:00.000','EXIT')
INSERT INTO @EmployeeTimeSwipeDetails VALUES (1, '2012-01-23 17:26:00.000','ENTER')
INSERT INTO @EmployeeTimeSwipeDetails VALUES (1, '2012-01-23 17:28:00.000','EXIT')

SELECT * FROM @EmployeeTimeSwipeDetails

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-23 : 07:34:21

homework? or something else?

Here's one way



SELECT
	ent.[empID] AS [EmpID]
	, ent.[Time] AS [EntryTime]
	, ext.[Time] AS [ExitTime]
FROM
	@EmployeeTimeSwipeDetails AS ent
	OUTER APPLY (
		SELECT TOP 1
			[Time]
		FROM
			@EmployeeTimeSwipeDetails AS ext
		WHERE
			ext.[Action] = 'EXIT'
			AND ext.[empID] = ent.[empID]
			AND ext.[Time] > ent.[Time]
		ORDER BY
			ext.[Time] ASC
		)
		AS ext
WHERE
	[Action] = 'ENTER'

However it makes an assumption that every ENTER has an EXIT, And that the [Times] will be unique to an employee / action.

If the table actually has a unique identifier for each row there are more checks that can be done.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources