>>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.


With that WHERE clause you filter first and last record from the example data.
Maybe you need:

WHERE (BusinessTime.Start>=@Start AND BusinessTime.Start<=@End) OR
      (BusinessTime.[End]>=@Start AND BusinessTime.[End]<=@End)) Temp