Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

slobad23

IS-IT--Management
Jun 30, 2006
90
GB
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
 
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;

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top