Problem with corelated subquery

[crystalnewbie]

I have a table that looks like this:

EmpID---EventDate---InpunchDTM---OutpunchDTM---StartReason

1000---1/1/04---9:00am---12:00pm---NewShift
1000---1/1/04---12:30pm---5:30pm---MealBreak

1002---1/1/04---9:00am---12:30pm---NewShift
1002---1/1/04---12:50pm---5:30pm---MealBreak

1002---1/1/04---9:00am---5:00pm---NewShift

What I'm trying to calculate is the amount of break they took, so for the example above, EmpID = 1000 took a 30 min break.

I tried writing a corelated subquery to rewrite the table to look like below so that I can do a datediff on the OutpunchDTM and BreakEnd to get their break time. However, i'm getting an error stating that the subquery is returning more than 1 value.

EmpID---EventDate---InpunchDTM---OutpunchDTM---StartReason---BreakEnd
1000---1/1/04---9:00am---12:00pm---NewShift---12:30pm

My sql is as follows:

select empid, eventdate, inpunchdtm, outpunchdtm, startreason,
(select Inpunchdtm from mytable as B where B.empid = mytable.empid and startreason = 'MealBreak') -- also tried using max() on inpuchdtm but it's giving the wrong date
from
mytable
where
startreason = 'NewShift'

Would like to solve this without using a stored procedure.

Any help or a different approach to solving my problem is greatly appreciated. Thanks.

 

[jiqjaq]

You're returning multiple values because all the employees Meal Breaks from every day at work are being included.

Your subquery also need to include:
AND B.EventDate = mytable.EventDate

 

[nigelrivett]

Looks like you need something more complex to get the start and end of the shift and the break time.
Looks like the out time for the MealBreak entry is the end of the shift
so something like
select t1.EmpID, t2.EventDate, t1.inpunchdtm, t2.outpunchdtm
from
(select EmpID, EventDate, inpunchdtm = min(inpunchdtm) from mytable) t1
join
(select EmpID, EventDate, outpunchdtm = max(outpunchdtm) from mytable) t2
on t1.EmpID = t2.EmpID
and t1.EventDate = t2.EventDate

Will give the start and end of the shift.
Now to get the breaks you will have to sum the diffs of end and starts in that

For the output you suggest above which doesn't look too useful and will also fail if two mealbreaks are taken

select empid, eventdate, inpunchdtm, outpunchdtm, startreason,
BreakEnd = (select Inpunchdtm from mytable as B where B.empid = mytable.empid and startreason = 'MealBreak' and B.eventdate = a.eventdate)
from
mytable a
where
startreason = 'NewShift'




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[crystalnewbie]

Jiqjaq - i'm getting the expected results after i added the join condition.

Nigel - you are right, my query would fail if a person has multiple breaks within a shift. I did try your sql though but all i got was duplicated lines for the people with meal breaks.

Like this:

1000---1/1/04---9:00am---12:00pm---NewShift
1000---1/1/04---9:00am---5:30pm---NewShift
1000---1/1/04---12:30pm---12:00pm---MealBreak
1000---1/1/04---12:30pm---5:30pm---MealBreak

It's not obvious to me how this would take care of multiple breaks.

Thanks again guys.

 

[nigelrivett]

oops - should be a group by EmpID, EventDate in both the subqueries.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[crystalnewbie]

Nigel - I added the group by on EmpID and EventDate prior to my post and still getting the same results.

1000---1/1/04---9:00am---12:00pm---NewShift
1000---1/1/04---9:00am---5:30pm---NewShift
1000---1/1/04---12:30pm---12:00pm---MealBreak
1000---1/1/04---12:30pm---5:30pm---MealBreak