Sample Query for a Question: “Select IN and OUT time based on flag”


  

create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:44:10.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:45:15.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:44:00.601','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O') 
 

;with mycte as (
select *, row_number() Over(Partition by EmpID Order by CheckedTime)
-row_number() Over(Partition by EmpID, CheckedType Order by CheckedTime) grp
 from #temp)
 ,mycte1 as (
 Select *
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime ASC) rnFirst
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime DESC) rnLast
   from mycte)

 Select EmpID,CheckedTime, CheckedType
 ,Case when rnFirst=1 and rnLast>1 then 'First_'
 when rnFirst>1 and rnLast=1 then 'Last_'
 End +CheckedType as checkCol 
 from mycte1
  Order by CheckedTime

drop table #temp
 

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9ca548b-af34-4726-9ffe-92351369c981/select-in-and-out-time-based-on-flag?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s