Is it possible to do it in one query?

Question

Post reply

Is it possible to do it in one query?

Vikka

Hall of Fame

Points: 3715
More actions
March 10, 2008 at 10:43 am

#184872

It's a little bit crazy.
If I have a table with employees punches and there can be two or more records (punch in, punch out, break, punch in, punch out)
But there can be two, three, four records per employee, means he could take one, two, three.. many breaks.
I need to calculate total time of all the breaks (it shouldn't be more than 60 min). It's easy to join tables in the case of just two records but if there are three records or more, I cannot imaging what to do..
2 records (one lunch break was taken):
select T.empl_id
from @split_shift T
join @split_shift T2 on T.empl_id = T2.empl_id
and T2.punch_in >= dateadd( n, 60, T.punch_out)
If I have 3 records, I would have to join same table three times to itself, but what to do if I have 33 records??? I mean it's extream but I didn't want to hardcode something but calculate how many records are in the table and based on this make a case like
select count(*) from @split_shift group by empl_id
Then I don't know what to do next. In the VB I would make a loop "for each i 1..count" I would join as many tables as I need. I don't know if same possible in SQL..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

I'd take a different approach

use test

go

Create Table Punches(empid int, time_in Datetime, time_out datetime)

insert INTO Punches(empid, time_in, time_out)

Select 1, '3/10/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 1, '3/10/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 1, '3/10/2008 12:30 PM', '3/10/2008 5:00 PM'

Select EmpID, datediff(n,min_time_in, max_time_out ) as TotalMinutesThisDay, TotalMinutesWorked,

TotalBreakTime = datediff(n,min_time_in, max_time_out ) - TotalMinutesWorked

From

(

Select EmpID, Min(time_in) min_time_in, max(time_out) max_time_out, sum(datediff(n,time_in,time_out)) TotalMinutesWorked

From Punches

Group By EmpID

) t1

This calculates when they were at work that day, adds up the total work time, and any extra time must have been break time.

Add some date filters, and/or some employee filters, and I think you've got it.

Vikka Hall of Fame Points: 3715 More actions · Answer 2

Vikka

Hall of Fame

Points: 3715

March 10, 2008 at 12:41 pm

#787542

This is very smart...

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

First, I thought you said the "records" looked like this...

PunchIn, PunchOut, Break, PunchIn, Punchout.

Second... are your really happy with the solution? Try it with multiple days...

USE tempdb

go

Create Table Punches(empid int, time_in Datetime, time_out datetime)

insert INTO Punches(empid, time_in, time_out)

Select 1, '3/10/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 1, '3/10/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 1, '3/10/2008 12:30 PM', '3/10/2008 5:00 PM' union all

Select 2, '3/10/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 2, '3/10/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 2, '3/10/2008 12:30 PM', '3/10/2008 5:00 PM' union all

Select 2, '3/11/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 2, '3/11/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 2, '3/11/2008 12:30 PM', '3/10/2008 5:00 PM'

Select EmpID, datediff(n,min_time_in, max_time_out ) as TotalMinutesThisDay, TotalMinutesWorked,

TotalBreakTime = datediff(n,min_time_in, max_time_out ) - TotalMinutesWorked

From

(

Select EmpID, Min(time_in) min_time_in, max(time_out) max_time_out, sum(datediff(n,time_in,time_out)) TotalMinutesWorked

From Punches

Group By EmpID

) t1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

Yeah, I know it won't work with multiple days, which is why I said "Add some date filters". I should mention adding group bys as well. This was just some skeleton code for the poster to flesh out with whatever they need.

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 5

What is your table structure? I didn't see this explicitly...may make a difference.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Guess it's time for porkchops here... Vika, if you want really good answers the first time around, see the URL in my signature line...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 7

I guess we need a group by on date?

Changed a bit of test data, fixed the checkout times of employee 2 on '3/11/2008'

Create Table Punches(empid int, time_in Datetime, time_out datetime)

insert INTO Punches(empid, time_in, time_out)

Select 1, '3/10/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 1, '3/10/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 1, '3/10/2008 12:30 PM', '3/10/2008 5:00 PM' union all

Select 2, '3/10/2008 08:00 AM', '3/10/2008 10:00 AM' union all

Select 2, '3/10/2008 10:15 AM', '3/10/2008 12:00 PM' union all

Select 2, '3/10/2008 12:30 PM', '3/10/2008 5:00 PM' union all

Select 2, '3/11/2008 08:00 AM', '3/11/2008 10:00 AM' union all

Select 2, '3/11/2008 10:15 AM', '3/11/2008 12:00 PM' union all

Select 2, '3/11/2008 12:30 PM', '3/11/2008 5:00 PM'

With PunchTimes AS

(Select EmpID,datediff(n,Min(time_in),max(time_out)) FullDay,datediff(n,Min(time_in),max(time_out))- sum(datediff(n,time_in,time_out)) BreakTime from Punches

group by EmpID, DATEADD(dd, 0, DATEDIFF(dd, 0, time_in)))

Select EmpID,Sum(Fullday) TotalWorkMins,Sum(BreakTime) BreakMins

from PunchTimes

group by empid

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]