Here is a quick "set based loop" method using an inline Tally table to count the hours.

😎

USE tempdb;

GO

DECLARE @TEST_DATA TABLE

(

TEST_DATA_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,startdate DATETIME NOT NULL

,enddate DATETIME NOT NULL

);

INSERT INTO @TEST_DATA(startdate,enddate)

VALUES

('2014-08-20 09:00:00.000','2014-08-23 13:00:00.000')

;

/* Seed for the inline Tally table */

;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)

,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

/* Assign 1 to each working hour between 9 and 17 */

,COUNT_HOURS AS

(

SELECT

TD.TEST_DATA_ID

,CASE

WHEN DATEPART(HOUR,(DATEADD(HOUR,NM.N,TD.startdate))) BETWEEN 10 AND 17 THEN 1

ELSE 0

END AS COUNT_HOURS

,CONVERT(DATE,DATEADD(HOUR,NM.N,TD.startdate)) AS GR_DATE

FROM @TEST_DATA TD

OUTER APPLY

(

SELECT TOP(DATEDIFF(HOUR,TD.startdate,TD.enddate))

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N

FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7

) AS NM(N)

)

--resource date hours

SELECT

CH.TEST_DATA_ID AS [resource]

,CH.GR_DATE AS [date]

,SUM(CH.COUNT_HOURS) AS [hours]

FROM COUNT_HOURS CH

GROUP BY CH.TEST_DATA_ID,CH.GR_DATE;

Results

resource date hours

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

1 2014-08-20 8

1 2014-08-21 8

1 2014-08-22 8

1 2014-08-23 4