Pivoting Timelogs in Sql Query

0

Please login or register to vote for this query.

(click on this box to dismiss)

Italian Language Meta

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

create table #test
   (
   employeeid int,
   recorddate date,
   recordtime time,
   typee int
   )

insert into #test  values(    4 ,         '2016-07-01',     '07:18:37',         1)
insert into #test  values(    4 ,         '2016-07-01',     '12:03:14',         2)
insert into #test  values(    4 ,         '2016-07-01',     '12:08:02',         1)
insert into #test  values(    4 ,         '2016-07-01',     '18:02:03',         2)



;with cte
as
(
 select
 employeeid,
 max(recorddate) as recorddate,
min(recordtime) as timein,
max(recordtime) as timeout ,
lead(min(recordtime)) over (partition by employeeid order by min(recordtime)) as 'timein1',
lead(max(recordtime)) over (partition by employeeid order by max(recordtime)) as 'timeout1'
from #test
group by 
employeeid,typee

)
select *
from cte where timein1 is not null and timeout1 is not null

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results