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
 General SQL Server Forums
 New to SQL Server Programming
 Merge 2 row Data in One Row

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

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 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];
[/code]
Go to Top of Page

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

[/code]

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

Go to Top of Page
   

- Advertisement -