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

Number of Working Days Expand / Collapse
Author
Message
Posted Thursday, February 07, 2013 9:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 221, Visits: 310
Comments posted to this topic are about the item Number of Working Days
Post #1417450
Posted Friday, February 08, 2013 5:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 147, Visits: 498
Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.
CREATE FUNCTION [dbo].[CalculateBusinessDays]
(
@StartDate DATE,
@EndDate DATE
)
RETURNS INTEGER
AS
BEGIN
IF @EndDate IS NULL
SELECT @EndDate = GETDATE()
RETURN CONVERT(INTEGER,
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- ISNULL((SELECT COUNT(*) FROM tblHoliDays
WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))
END

Post #1417961
Posted Saturday, February 09, 2013 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 221, Visits: 310
Dave Vroman (2/8/2013)
Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.
CREATE FUNCTION [dbo].[CalculateBusinessDays]
(
@StartDate DATE,
@EndDate DATE
)
RETURNS INTEGER
AS
BEGIN
IF @EndDate IS NULL
SELECT @EndDate = GETDATE()
RETURN CONVERT(INTEGER,
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
- (DATEDIFF(wk, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- ISNULL((SELECT COUNT(*) FROM tblHoliDays
WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))
END



What sorts of errors are you getting when you try to run the function?
Post #1418025
Posted Monday, February 11, 2013 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 147, Visits: 498
Your answer told me that I was having problems with the browser (Chrome). Tried it with Firefox - works well.
Post #1418587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse