>>Could you post some example data and desired result from it?
>
>For example, if my start and end time is 23/01/2014 09:15:00 and 29/01/2014 13:45:00, the SQL select might return a set of records like this for specific working shifts:
>
>23/01/2014 08:00:00 - 23/01/2014 12:00:00
>23/01/2014 13:00:00 - 23/01/2014 17:00:00
>24/01/2014 08:00:00 - 24/01/2014 12:00:00
>24/01/2014 13:00:00 - 24/01/2014 17:00:00
>27/01/2014 08:00:00 - 27/01/2014 12:00:00
>27/01/2014 13:00:00 - 27/01/2014 17:00:00
>28/01/2014 08:00:00 - 28/01/2014 12:00:00
>28/01/2014 13:00:00 - 28/01/2014 17:00:00
>29/01/2014 13:00:00 - 29/01/2014 17:00:00
>
>That could represent, for example, Thursday, Friday, Monday, Tuesday and Wednesday afternoon, assuming Wednesday morning was a holiday.
>
>Those would have been selected because they fit within the range of the start and end dates.
>
>So, here, I need to obtain 32.00 hours because this is the total amount of working hours that were eligible between the two dates.
>
>If the start and end time would be 23/01/2014 09:20:00 and 29/01/2014 13:45:00, that would give 31.95 hours.


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