>How do you get 32 hours?
>The example gave me 36 hours if you calculate the time between StartTime and EndTime.
>If you calculate the time between 23/01/2014 09:15:00 and 29/01/2014 13:45:00 the example data gave me 31.5 hours.
>
>

> DECLARE @StartDate datetime 
> DECLARE @EndDate datetime 
> 
> SET @StartDate = '2014-01-23T09:15:00'
> SET @EndDate = '2014-01-29T13:45:00'
> 
>
>
>DECLARE @Test TABLE (StartDate datetime, EndDate datetime NULL)
>
>INSERT INTO @Test VALUES ('2014-01-23T08:00:00', '2014-01-23T12:00:00')
>INSERT INTO @Test VALUES ('2014-01-23T13:00:00', '2014-01-23T17:00:00')
>INSERT INTO @Test VALUES ('2014-01-24T08:00:00', '2014-01-24T12:00:00')
>INSERT INTO @Test VALUES ('2014-01-24T13:00:00', '2014-01-24T17:00:00')
>INSERT INTO @Test VALUES ('2014-01-27T08:00:00', '2014-01-27T12:00:00')
>INSERT INTO @Test VALUES ('2014-01-27T13:00:00', '2014-01-27T17:00:00')
>INSERT INTO @Test VALUES ('2014-01-28T08:00:00', '2014-01-28T12:00:00')
>INSERT INTO @Test VALUES ('2014-01-28T13:00:00', '2014-01-28T17:00:00')
>INSERT INTO @Test VALUES ('2014-01-29T13:00:00', '2014-01-29T17:00:00')
>
>SELECT SUM(Test1) AS Test1
>FROM (
>SELECT *, DATEDIFF(SECOND, CASE WHEN StartDate > @StartDate 
>                                     THEN StartDate
>                           ELSE @StartDate END,
>                           CASE WHEN EndDate < @EndDate 
>                                     THEN EndDate 
>                           ELSE @EndDate END)/3600.0 AS Test1
>FROM @Test) Tst
>
>SELECT SUM(Test1) AS Test1
>FROM (
>SELECT *, DATEDIFF(SECOND, StartDate, EndDate)/3600.0 AS Test1
>FROM @Test) Tst
>

Thanks, this seems to be heading into what I am trying to achieve.

So far, I have this:

DECLARE @Start DATETIME
DECLARE @End DATETIME
 
SET @Start='2014-01-23 09:15:00'
SET @End='2014-01-29 13:45:00'
 
SELECT SUM(Temp.SecondValue)/3600 AS HourValue
FROM (SELECT DATEDIFF(SECOND,
 CASE WHEN BusinessTime.Start>@Start THEN BusinessTime.Start ELSE @Start END,
 CASE WHEN BusinessTime.[End]<@End THEN BusinessTime.[End] ELSE @End END) AS SecondValue
 FROM BusinessTime
 WHERE BusinessTime.Start>=@Start AND BusinessTime.Start<=@End AND
  BusinessTime.[End]>=@Start AND BusinessTime.[End]<=@End) Temp

This gives me 32 hours.

If I change the End value to 12h45, it still gives me 32 hours.

I am trying to find out what need to be adjusted.