Date difference to nearest half day counting only working hours. Yikes!

[slobad23]

Scenario:

Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 13:00

Working hours are 9-5 so this is closest to 1.5 days.

Start DateTime = 01/01/2012 09:00
End DateTime = 01/01/2012 17:00

This shows 1 day.

Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 16:00

This shows 2 days.

My current logic is this:

SELECT (CEILING(CAST(DATEDIFF(hh,@Startdate,@Enddate) AS DECIMAL) / 12)) / 2

The above works fine for values over 1 day. The problem is with start and ends on the same day show a half day. Ideally I would be able to only count the working parts of the day and not the full 24 hours.

Any help on this would be much appreciated.

Thanks,

Slobad23

 

[TomKane]

You're solution is very elegant. The only one I could get to work is nothing like as elegant or efficient as what you were proposing but I'm pretty happy with it - here you go:

/* Congfigure Environment Settings */

SET DATEFORMAT DMY

/* Declare Variables */

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @DayUnit DECIMAL(18,2)
DECLARE @MaxID INT

DECLARE @DateTable TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
StartDate DATETIME,
EndDate DATETIME,
Hours DECIMAL(18,2)
)

/* Set Variables */

SET @DayUnit = 8.0

SET @StartDate = '01/01/2012 09:00'
SET @EndDate = '01/01/2012 17:00'

/* Use CTE to select days in the range */
;WITH DateCTE AS
(
SELECT @StartDate StartDate, DATEADD(hh,@DayUnit,@StartDate) EndDate
UNION ALL
SELECT StartDate + 1, DATEADD(hh,@DayUnit,StartDate + 1)
FROM DateCTE
WHERE StartDate BETWEEN @StartDate AND @EndDate - 1
)

/* Put the CTE into a table variable */
INSERT INTO @DateTable
(
StartDate,
EndDate,
Hours
)
SELECT StartDate,
EndDate,
DATEDIFF(hh, StartDate, EndDate)
FROM DateCTE; /* You coud potenitally add logic here to exclude weekends and public holidays */

/* Get the record count */
SET @MaxID = @@ROWCOUNT;

/* Set the true end date on the end date of the final record */
UPDATE @DateTable
SET EndDate = @EndDate, Hours = DATEDIFF(hh,StartDate, @EndDate)
WHERE ID = @MaxID;

/* Summarise the hours returned */
SELECT SUM(Hours) / @DayUnit
FROM @DateTable;

/* Detail in the table */
SELECT StartDate,
EndDate,
Hours
FROM @DateTable;