Need Query

Hi,

i have a table like below



CREATE table #TempTable (ID int,checkedtime time(7),ctype varchar(1))
insert into #TempTable VALUES (101,'09:02:00.0000000','I')
insert into #TempTable VALUES (101,'18:10:00.0000000','O')
insert into #TempTable VALUES (102,'09:03:00.0000000','I')
insert into #TempTable VALUES (102,'13:43:00.0000000','O')
insert into #TempTable VALUES (102,'14:54:00.0000000','I')
insert into #TempTable VALUES (102,'18:10:00.0000000','O')
SELECT * from #TempTable

i  need the result like below

regards

Kasim

May 28th, 2015 12:57pm

CREATE table #TempTable (ID int,checkedtime time(7),ctype varchar(1))
insert into #TempTable VALUES (101,'09:02:00.0000000','I')
insert into #TempTable VALUES (101,'18:10:00.0000000','O')
insert into #TempTable VALUES (102,'09:03:00.0000000','I')
insert into #TempTable VALUES (102,'13:43:00.0000000','O')
insert into #TempTable VALUES (102,'14:54:00.0000000','I')
insert into #TempTable VALUES (102,'18:10:00.0000000','O')

--insert into #TempTable VALUES (102,'19:54:00.0000000','I')
--insert into #TempTable VALUES (102,'19:59:00.0000000','O')


;with mycte as (
SELECT ID,checkedtime , ctype,  
row_number()Over(Partition by ID,ctype Order by checkedtime) rn 
from #TempTable)

Select ID, Max(Case when ctype='I' Then checkedtime END) InTime,
Max(Case when ctype='O' Then checkedtime END) OutTime 
 from mycte
 Group by ID, rn



drop table #TempTable

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 1:07pm

SELECT DISTINCT a.ID, a.checkedtime AS CheckIn,

oa.checkedtime AS CheckOut FROM #TempTable AS a OUTER APPLY (SELECT TOP 1 b.ID, b.ctype, b.checkedtime FROM #TempTable AS b WHERE a.ID = b.ID AND a.ctype <> b.ctype AND a.checkedtime < b.checkedtime ORDER BY b.checkedtime ) AS oa WHERE a.ctype = 'I'


May 28th, 2015 3:06pm

Thanks Jing
Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 12:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics