[SOLUTION] Compare current row to next row in sql

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?

;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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 24

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented: 2015-03-30

Which version of SQL Server?

axnst2Author Commented: 2015-03-30

Microsoft SQL Server 2008 R2 (SP3)

LVL 42

Kyle AbrahamsSenior .Net DeveloperCommented: 2015-03-30

You'll have to change the column names . . . c1 will be the current row. c2 will be the next row.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Compare current row to next row in sql

Who is Participating?

Already a member? Login.