We help IT Professionals succeed at work.

Compare current row to next row in sql

I have time card data that I am trying to compare the time between the clockout time of one day to the clock in time of the next day.  I have tried a few variations and I think I am close, but seemed to be hung up on how to get the next row.  The problem I am trying to solve is if an employee has less than 8 hours off between the last clock out time to the next clock in time.

Here is the raw data for one employee.

employeeid          BUSINESS_DATE                clock_in_date_time               clock_out_date_time
206                      2015-03-09 00:00:00.000      2015-03-09 07:25:40.140      2015-03-09 12:00:26.687
206                      2015-03-09 00:00:00.000      2015-03-09 13:00:00.000      2015-03-09 16:34:11.493
206                      2015-03-10 00:00:00.000      2015-03-10 07:58:52.803      2015-03-10 12:00:47.793
206                      2015-03-10 00:00:00.000      2015-03-10 12:59:36.843      2015-03-10 17:05:09.593
206                      2015-03-11 00:00:00.000      2015-03-11 08:00:36.007      2015-03-11 12:09:56.753
206                      2015-03-11 00:00:00.000      2015-03-11 13:00:00.113      2015-03-11 17:13:47.700
206                      2015-03-12 00:00:00.000      2015-03-12 07:56:15.817      2015-03-12 13:00:03.440
206                      2015-03-12 00:00:00.000      2015-03-12 13:53:05.850      2015-03-12 17:10:06.713
206                      2015-03-14 00:00:00.000      2015-03-14 07:57:33.777      2015-03-14 11:59:56.490
206                      2015-03-14 00:00:00.000      2015-03-14 13:00:34.667      2015-03-14 17:00:48.560

Here is my first attempt at getting the data summerized.  This is just a subquery.
select employeeid
			,business_date
			,min(clock_in_date_time) clock_in_date_time
			,max(clock_out_date_time) clock_out_date_time
	from Payroll.PUERTORICOTIMECARDS 
	where EMPLOYEEID = 206
	group by id, BUSINESS_DATE, EMPLOYEEID

Open in new window


This is the farthest I got with my query.  My issue here is I don't have the row Id (which ID is the primary key on the table) because of the group by will throw the sub queries off.
select t1.employeeid, t1.clock_out_date_time, t2.clock_in_date_time
from
(
	select employeeid
			,business_date
			,min(clock_in_date_time) clock_in_date_time
			,max(clock_out_date_time) clock_out_date_time
	from Payroll.PUERTORICOTIMECARDS 
	where EMPLOYEEID = 206
	group by id, BUSINESS_DATE, EMPLOYEEID
) t1
join
(
	select employeeid
			,business_date
			,min(clock_in_date_time) clock_in_date_time
			,max(clock_out_date_time) clock_out_date_time
	from Payroll.PUERTORICOTIMECARDS 
	where EMPLOYEEID = 206
	group by BUSINESS_DATE, EMPLOYEEID
) t2

Open in new window


How do I go about getting the next row of the summarized data for each employee?
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Which version of SQL Server?
axnst2IT Manager

Author

Commented:
Microsoft SQL Server 2008 R2 (SP3)
Senior .Net Developer
Commented:
You'll have to change the column names . . . c1 will be the current row.  c2 will be the next row.  

;with cte as 
(select id, BusinessDate, clockin, clockout, row_number() over (partition by id order by clockin)rn
from #temp
)

select c1.*, c2.* from cte c1
left join cte c2 on c1.id = c2.id and c1.rn+1 = c2.rn

Open in new window