Your comment : Can you explain how you get the value 781?

Very easy, I calculated wrong. I saw it for a while ago ..

By the way, I have modified Your code as follows, (I ship the create of table and data).

DECLARE @dtStart DATETIME ;

DECLARE @dtStop DATETIME ;

SET @dtStart = '2013-06-11 12:40' ;-- '2013-06-11 12:40:21' ;

set @dtStop = '2013-06-12 08:55' ;-- '2013-06-12 08:59' ;

SELECT

t.Shift

,t.dtBegin

,t.dtEnd

,DATEDIFF(minute, /*t.dtBegin*/(case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end),

/*t.dtEnd*/ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end)) -

COALESCE(SUM(DATEDIFF(minute,t2.dtBegin,t2.dtEnd)),0)

AS WorkingTimeMinutes

---------------------------------------------------------------

--Just for verification under the development ...

---------------------------------------------------------------

, (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end) as dtStartValue

, (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end) as dtStopValue

FROM @tblSchema t

LEFT OUTER JOIN @tblSchema t2 ON t2.Shift = t.Shift

AND t2.Code <> 1

AND t2.dtBegin

BETWEEN /*t.dtBegin*/ (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end)

AND /* t.dtEnd */ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end)

AND t2.dtEnd

BETWEEN /*t.dtBegin*/ (case when (t.dtBegin < @dtStart) then @dtStart else t.dtBegin end)

AND /* t.dtEnd */ (case when (t.dtEnd > @dtStop) then @dtStop else t.dtEnd end)

WHERE t.Code = 1 and

t.dtEnd >= @dtStart and t.dtBegin <= @dtStop

GROUP BY t.Shift,t.dtBegin,t.dtEnd, t.Code

ORDER BY

t.dtBegin,

t.Shift;

Note! I have added some fields, just for debuging ...

It's (more or less) working as my thoughts, but there is still a problem that I don't see.

It's coming when @dtStart is in a pause period, if I for example enter time value = '2013-06-11 12:40' then the result is added with 5 minutes.

Do I enter a value outside pausetimes then its OK

Examples:

'2013-06-11 12:40' gives WorkingTimeMinutes = 101 WRONG (should be 96)

'2013-06-11 12:30' gives WorkingTimeMinutes = 96 OK

'2013-06-11 12:45' gives WorkingTimeMinutes = 96 OK

'2013-06-11 12:00' gives WorkingTimeMinutes = 126 OK

'2013-06-11 12:50' gives WorkingTimeMinutes = 91 OK

I see the same behavour with the @dtStop .. Outside Pausetime it working ok

Problerly is the reason easy, but I don't see it right now.

Any ideas where I'm is doing wrong ??