Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

Calculating Work Days Expand / Collapse
Author
Message
Posted Wednesday, April 4, 2012 1:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 1:13 AM
Points: 1, Visits: 0
Great function to get the weekdays. I had a need to calculate hour differenct between two datetimes excluding weekends (exclude 48 hours for each week end) to calculate age of a document to be mailed disregaring holidays (as holidays are not so important compared with complexity it will involve and given the context of the document, according to my team lead)

I managed to change a little bit of your function for my need. Thanks for the idea you gave in your article. saved my day.

/****** Object: UserDefinedFunction [dbo].[fn_HourDiffExcudeWeekend] Script Date: 04/04/2012 03:08:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description: Calculate the hour gap between two given datetimes
-- excluding weekend 48 hours (from Saturday 00:00 hours to Sunday 24:00 hours)
-- =============================================
CREATE FUNCTION [dbo].[fn_HourDiffExcudeWeekend]
(
@StartDate datetime,
@EndDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @HourGap int

--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME

--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
BEGIN
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
END

--====== Calculate hour gap excluding weekend 48 hours
SELECT @HourGap =
-- hour difference betwenn the two days
(DATEDIFF(hh, @StartDate, @EndDate))

-- substract 48 hours for each weekend
--The DATEPART(dw,date) does not actually count weeks... It counts the
-- transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts
-- only whole weekends in any given date range.
-(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)

-- Substract the additional hours added by the @StartDate of Sunday to get the hour diff from starting Next Monday 00:00 Hours
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN (24 - DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ) ELSE 0 END)

-- Substract the additional hours added by the @EndDate of Saturday to get the hour diff to @EndDate 00:00 Hours (Prevoius Friday 24:00 hours)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@EndDate as date), @EndDate) ELSE 0 END)

-- Add hours from @StartDate 00:00 to the @StartDate time (xx:00 hours), to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)
+(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ELSE 0 END)

-- Add hours from @EndDate time xx:00 hours to @EndDate 24:00 hours to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)
+(CASE WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN (24 -DATEDIFF(hh,CAST(@EndDate as date), @EndDate)) ELSE 0 END)


RETURN @HourGap

END

GO
Post #1277822
Posted Sunday, April 22, 2012 10:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
Apologies for the delayed response. Thank you for the feedback and for posting the function you built. It might help others having the same problem.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1287794
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse