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.
Author |
Topic |
asif372
Posting Yak Master
100 Posts |
Posted - 2013-02-06 : 05:18:21
|
basically I am Working on Attendance System I receive Data from Attendance Machine Like This ID DATETIME1116 2012-12-03 14:46:46.0001116 2012-12-03 18:12:17.0001116 2012-12-03 19:00:11.0001116 2012-12-03 21:00:15.0001116 2012-12-04 09:06:54.0001116 2012-12-04 17:46:08.0001116 2012-12-05 09:09:36.0001116 2012-12-05 17:04:33.0001114 2012-12-06 09:22:01.0001114 2012-12-06 17:46:13.0001117 2012-12-07 09:04:37.0001117 2012-12-07 17:46:39.000my Resultant Data i is like thisID------TIMEIN---------------------------TIMEOUT1116----2012-12-03 14:46:46------------2012-12-03 18:12:171116----2012-12-03 19:00:11------------2012-12-03 21:00:151116----2012-12-04 09:06:54------------2012-12-04 17:46:081116----2012-12-05 09:09:36------------2012-12-05 17:04:331114----2012-12-06 09:22:01------------2012-12-06 17:46:131117----2012-12-07 09:04:37------------2012-12-07 17:46:39how can it is PossibleThanks in Advance |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 07:44:20
|
[code];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 TIMEOUTFROM cte a LEFT JOIN cte b ON a.NN+1 = b.NN AND a.Id = b.IdWHERE a.NN%2 = 1ORDER BY a.ID, a.[DATETIME]; [/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 11:04:52
|
[code]declare @test table(ID int,[DATETIME] DATETIME)insert @testvalues(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 TIMEOUTFROM(SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATETIME ) AS Seq,*FROM @test)tGROUP BY ID, (Seq-1) /2ORDER BY ID,MIN(Seq)output------------------------------------------------------ID TIMEIN TIMEOUT------------------------------------------------------1114 2012-12-06 09:22:01.000 2012-12-06 17:46:13.0001116 2012-12-03 14:46:46.000 2012-12-03 18:12:17.0001116 2012-12-03 19:00:11.000 2012-12-03 21:00:15.0001116 2012-12-04 09:06:54.000 2012-12-04 17:46:08.0001116 2012-12-05 09:09:36.000 2012-12-05 17:04:33.0001117 2012-12-07 09:04:37.000 2012-12-07 17:46:39.000[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|