SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Merge 2 row Data in One Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 02/06/2013 :  05:18:21  Show Profile  Reply with Quote
basically I am Working on Attendance System I receive Data from Attendance Machine Like This

ID DATETIME
1116 2012-12-03 14:46:46.000
1116 2012-12-03 18:12:17.000
1116 2012-12-03 19:00:11.000
1116 2012-12-03 21:00:15.000
1116 2012-12-04 09:06:54.000
1116 2012-12-04 17:46:08.000
1116 2012-12-05 09:09:36.000
1116 2012-12-05 17:04:33.000
1114 2012-12-06 09:22:01.000
1114 2012-12-06 17:46:13.000
1117 2012-12-07 09:04:37.000
1117 2012-12-07 17:46:39.000

my Resultant Data i is like this

ID------TIMEIN---------------------------TIMEOUT
1116----2012-12-03 14:46:46------------2012-12-03 18:12:17
1116----2012-12-03 19:00:11------------2012-12-03 21:00:15
1116----2012-12-04 09:06:54------------2012-12-04 17:46:08
1116----2012-12-05 09:09:36------------2012-12-05 17:04:33
1114----2012-12-06 09:22:01------------2012-12-06 17:46:13
1117----2012-12-07 09:04:37------------2012-12-07 17:46:39

how can it is Possible
Thanks in Advance

Edited by - asif372 on 02/06/2013 07:30:23

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 02/06/2013 :  07:44:20  Show Profile  Reply with Quote
;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATETIME]) AS NN
	FROM
		YourTableNameHere
)
SELECT
	a.ID,
	a.[DATETIME] AS TIMEIN,
	b.[DATETIME] AS TIMEOUT
FROM
	cte a
	LEFT JOIN cte b ON a.NN+1 = b.NN AND a.Id = b.Id
WHERE
	a.NN%2 = 1
ORDER BY
	a.ID,
	a.[DATETIME];
	

Edited by - James K on 02/06/2013 08:31:02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/06/2013 :  11:04:52  Show Profile  Reply with Quote

declare @test table
(
ID	 int,
[DATETIME] DATETIME
)
insert @test
values(1116,	 '2012-12-03 14:46:46.000'),
(1116	 ,	 '2012-12-03 18:12:17.000'),
(1116	 ,	 '2012-12-03 19:00:11.000'),
(1116	 ,	 '2012-12-03 21:00:15.000'),
(1116	 ,	 '2012-12-04 09:06:54.000'),
(1116	 ,	 '2012-12-04 17:46:08.000'),
(1116	 ,	 '2012-12-05 09:09:36.000'),
(1116	 ,	 '2012-12-05 17:04:33.000'),
(1114	 ,	 '2012-12-06 09:22:01.000'),
(1114	 ,	 '2012-12-06 17:46:13.000'),
(1117	 ,	 '2012-12-07 09:04:37.000'),
(1117	 ,	 '2012-12-07 17:46:39.000')


SELECT ID, MIN([DATETIME]) AS TIMEIN,MAX([DATETIME]) AS TIMEOUT
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATETIME ) AS Seq,*
FROM @test
)t
GROUP BY ID, (Seq-1) /2
ORDER BY ID,MIN(Seq)


output
------------------------------------------------------
ID	TIMEIN	                TIMEOUT
------------------------------------------------------
1114	2012-12-06 09:22:01.000	2012-12-06 17:46:13.000
1116	2012-12-03 14:46:46.000	2012-12-03 18:12:17.000
1116	2012-12-03 19:00:11.000	2012-12-03 21:00:15.000
1116	2012-12-04 09:06:54.000	2012-12-04 17:46:08.000
1116	2012-12-05 09:09:36.000	2012-12-05 17:04:33.000
1117	2012-12-07 09:04:37.000	2012-12-07 17:46:39.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000