Calculating Work Days
-
Cool Kurt... wanna share your SQL code on this?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Yes, your algorithm is indeed correct. I never really doubted that ... I always suspected it was a db data issue.
With help from Sergiy the date conversion allowed me to spot that the dates were 2 days out in the SQL db compared to how they were entered in the App. Hence, why it gave 21 days ... the App dates of 1st May - 31st May should have given 23 weekdays, but because the App was then saving them in the SQL server with a 2-day offset the SQL server actually had them as 3rd May to 2nd June which is indeed 21 weekdays.
Thanks
-
Ah... got it... had to go back and look... Serqiy is correct and I'll throw in that you may not ever want to pass date serial numbers between products... Best bet is probably ISO dates. I believe that Excel has even a different day zero than VB or SQL Server.
Sorry I didn't get what you were saying the first time.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There is scope for another function work hours in a daterange
regards
john
-
Excellent observation!
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sorry folks,
I am a newbie here and I wonder if some one is kind enough and explain to me how do I get the number of days out of this SP or how do I run this SP in Query Analyzer and see the result there. I tried to run this SP and nothing come back.
-
Post the code that you tried, Tony... it's the only way we can see what might be wrong...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Jeff I try to understand your code and learn how to work with SP and all. Here is what I tried to run the example that you wrote here (finding number of workdays)
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
--query analyzer----
Declare @startdate datetime, @enddate datetime
Select @startdate = '12/17/2007', @enddate = '12/25/2007'
Exec dbo.fn_WorkDays @startdate, @enddate
---coldfusion----
I am not sure if I have the CF call right either..Can you show me the way. Thanks
-
How do I post CF code here??
-
First, dunno how to post CF code on this forum mostly because I've never used CF.
Still need to see code... I see that you've created a function to use the "method", and I need to see that to help you figure out why you're not getting a return.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The only thing I changed was to make it as SP instead of a function. Thanks
---Declare @startdate datetime, @enddate datetime
---Select @startdate = '12/17/2007', @enddate = '12/25/2007'
Exec dbo.TEST_WorkDays '12/17/2007', '12/25/2007'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.TEST_WorkDays
/***************************************************************************************
Purpose:
1. Given any valid start date and end date, this function will calculate and return
the number of workdays (Mon - Fri).
2. Given only a valid start date (end date has DEFAULT in it), this function will
return a 1 if the start date is a weekday and a 0 if not a weekday.
Usage:
1. MASTER.dbo.TEST_WorkDays(@StartDate,@EndDate)
2. MASTER.dbo.TEST_WorkDays(@StartDate,DEFAULT) --Always returns 1 or 0
3. MASTER.dbo.TEST_WorkDays(@EndDate,@StartDate)
4. MASTER.dbo.TEST_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
5. MASTER.dbo.TEST_WorkDays(@EndDate,@EndDate) --Always returns 1 or 0
Notes:
1. Holidays are NOT considered.
2. Because of the way SQL Server calculates weeks and named days of the week, no
special consideration for the value of DATEFIRST is given. In other words, it
doesn't matter what DATEFIRST is set to for this function.
3. If the input dates are in the incorrect order, they will be reversed prior to any
calculations.
4. Only whole days are considered. Times are NOT used.
5. The number of workdays INCLUDES both dates
6. Inputs may be literal representations of dates, datetime datatypes, numbers that
represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
be implicitly converted to or already is a datetime datatype.
7. Undocumented: 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.
8. This UDF does NOT create a tally table or sequence table to operate. Not only is
it set based, it is truly "tableless".
Error Indications:
1. If either the @StartDate or the @EndDate parameter is an invalid date, the
following error is returned...
"Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
2. If either the @StartDate or the @EndDate parameter is a string not resembling a
date, the following error is returned...
"Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string."
3. If only one parameter is passed, the following error is returned...
"Server: Msg 313, Level 16, State 2, Line 3
An insufficient number of arguments were supplied for the procedure or
function MASTER.dbo.fn_WorkDays."
Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
***************************************************************************************/
--======================================================================================
-- Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDate DATETIME,
@EndDate DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)
--===== Define the output data type
RETURN INT
AS
--======================================================================================
-- Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME, @Days DATETIME
--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
RETURN NULL
--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Heh... If you're going to copy code 100%, the least you could do is put my name on it 😉
First, why are you using this as a proc... why not just use it as a function? That would be perfect...
Second, if you have a DBA that would rather have scalar procs instead of the benefit of scalar functions, then you need to add another variable to the proc and the call.
--======================================================================================
-- Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDate DATETIME,
@EndDate DATETIME = NULL, --@EndDate replaced by @StartDate when DEFAULTed
@WorkDays INT OUTPUT
)
----===== Define the output data type
--RETURN INT
AS
--======================================================================================
-- Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME, @Days DATETIME
--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
BEGIN
SELECT @WordDays = NULL
RETURN
END
--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
--RETURN (
SELECT @WorkDays =
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
--)
And, the call should be...
Declare @startdate datetime, @enddate datetime, @WorkDays INT
Select @startdate = '12/17/2007', @enddate = '12/25/2007'
Exec dbo.TEST_WorkDays @startdate , @enddate , @WorkDays OUTPUT
Note that some of the code in the red areas above have simply been commented out and don't actually need to be included in the code.
Function would still be better. Also, since you're new, you may want to study "CREATE PROCEDURE" in Books OnLine for how to return data through variables in a proc like this.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks Jeff,
I am still learning and this is just to see how it works. Sure if I use the code anywhere, your name will be there.
I am not so familiar with function ( I had a little more on sp) that's why. On top of that I am using CF, I have not yet get to the point of calling a function as I am learning the rope on proc & CF.I will give it another try and thanks for being patient with me.
Tony
-
ahhh.... error....."has too many arguments specified"
Declare @StartDate datetime, @EndDate datetime, @WorkDays INT
Select @StartDate = '12/17/2007', @EndDate = '12/25/2007'
Exec dbo.PIN_WorkDays @StartDate, @EndDate, @WorkDays OUTPUT
using exactly what previously corrected (except for 'worddays'...spelling??)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.PIN_WorkDays
Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
***************************************************************************************/
--======================================================================================
-- Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDate DATETIME,
@EndDate DATETIME = NULL, --@EndDate replaced by @StartDate when DEFAULTed
@WorkDays INT OUTPUT
)
--===== Define the output data type
RETURN INT
AS
--======================================================================================
-- Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME, @Days DATETIME
IF @StartDate IS NULL
BEGIN
SELECT @WorkDays = NULL
RETURN
END
--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
RETURN NULL
--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
--RETURN (
SELECT @WorkDays =
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
--)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
I haven't check all the code, but I see that you have a RETURN that I commented out in red...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 76 through 90 (of 156 total)
You must be logged in to reply to this topic. Login to reply