SubtractWorkingDays

7 Sep 2006 12:21 PM

Sn0tters

I've just written this UDF as a compliment to GetWorkingDays, which I
see around the net a lot.

I'm just looking for general comments and to leave it here for anyone
who wants to use it.

/*==============================================================================/

--
-- SCRIPT      :   CREATE_UDF_SubtractWorkingDays.sql
--
-- DESCRIPTION :   This script will create the UDF SubtractWorkingDays
which
--                    will subtract a number of working days from a
date
--
-- $Date: $
--
-- $Rev: $
--
--==============================================================================*/

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SubtractWorkingDays]') and OBJECTPROPERTY(id,
N'IsScalarFunction') = 1)
drop function [dbo].[SubtractWorkingDays]

GO

CREATE FUNCTION dbo.SubtractWorkingDays
( @StartDate DATETIME,
    @Days INT )

RETURNS DATETIME

AS

BEGIN
DECLARE @EndDate DATETIME
DECLARE @FirstPart INT
DECLARE @RealNumerOfDays INT

SELECT   @FirstPart = CASE DATENAME(weekday, @StartDate)
                 WHEN 'Sunday' THEN 0
                 WHEN 'Monday' THEN 0
                 WHEN 'Tuesday' THEN 1
                 WHEN 'Wednesday' THEN 2
                 WHEN 'Thursday' THEN 3
                 WHEN 'Friday' THEN 4
                 WHEN 'Saturday' THEN 5
               END

IF ((@FirstPart - @Days) >= 0)
     BEGIN
     --
     -- If the number of days to subtract does not go over a weekend
then
     -- just do the subtraction
     --
       SELECT @EndDate = DATEADD( DAY, @Days *-1, @StartDate )
     END
ELSE
     BEGIN
         --
        -- If the number of days to subtract does go over a weekend
then
        -- work out the number of weekend days to remove
         --
       SELECT @RealNumerOfDays = (((@Days) / 5) + 1) * 2

       SELECT @EndDate = DATEADD( DAY, (@Days + @RealNumerOfDays) *-1,
@StartDate )
     END

RETURN ( @EndDate )
END

7 Sep 2006 1:31 PM

SQL Menace

You probably want to name this proc WeekDays or
SubtractWorkingDaysNotTakingIntoConsiderationHolidays

What happens on July 4th, December 25th etc
Calendar table (or a combination of a number table + a calendar table)
would be better suited for this IMHO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Sn0tt***@yahoo.co.uk wrote:
Show quote

> I've just written this UDF as a compliment to GetWorkingDays, which I
> see around the net a lot.
>
> I'm just looking for general comments and to leave it here for anyone
> who wants to use it.
>
> /*==============================================================================/
> --
> -- SCRIPT      :   CREATE_UDF_SubtractWorkingDays.sql
> --
> -- DESCRIPTION :   This script will create the UDF SubtractWorkingDays
> which
> --                    will subtract a number of working days from a
> date
> --
> -- $Date: $
> --
> -- $Rev: $
> --
> --==============================================================================*/
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SubtractWorkingDays]') and OBJECTPROPERTY(id,
> N'IsScalarFunction') = 1)
> drop function [dbo].[SubtractWorkingDays]
>
> GO
>
>
> CREATE FUNCTION dbo.SubtractWorkingDays
>   ( @StartDate DATETIME,
>     @Days INT )
>
> RETURNS DATETIME
>
> AS
>
> BEGIN
>   DECLARE @EndDate DATETIME
>   DECLARE @FirstPart INT
>   DECLARE @RealNumerOfDays INT
>
>   SELECT   @FirstPart = CASE DATENAME(weekday, @StartDate)
>                  WHEN 'Sunday' THEN 0
>                  WHEN 'Monday' THEN 0
>                  WHEN 'Tuesday' THEN 1
>                  WHEN 'Wednesday' THEN 2
>                  WHEN 'Thursday' THEN 3
>                  WHEN 'Friday' THEN 4
>                  WHEN 'Saturday' THEN 5
>                END
>
>
>   IF ((@FirstPart - @Days) >= 0)
>      BEGIN
>      --
>      -- If the number of days to subtract does not go over a weekend
> then
>      -- just do the subtraction
>      --
>        SELECT @EndDate = DATEADD( DAY, @Days *-1, @StartDate )
>      END
>   ELSE
>      BEGIN
>          --
>         -- If the number of days to subtract does go over a weekend
> then
>         -- work out the number of weekend days to remove
>          --
>        SELECT @RealNumerOfDays = (((@Days) / 5) + 1) * 2
>
>        SELECT @EndDate = DATEADD( DAY, (@Days + @RealNumerOfDays) *-1,
> @StartDate )
>      END
>
>   RETURN ( @EndDate )
> END

7 Sep 2006 1:41 PM

--CELKO--

>> I've just written this UDF as a compliment to GetWorkingDays, I'm just looking for general comments .. <<

1) it does not work. You forgot that holidays are irregular. This is
why we use a Calendar table. Just add a julianized work day column to
the calendar table and do the math that way.

SQL programmers think in terms of data and declarations, not
procedures. Look up an article I did at www.dbazine.com on converting
from a procedural mindset to a declarative one.

2) Aside from #1, which is a killer, your T_SQL shows that you are not
used to the language.

a) The use of SELECT for assignment is bad coding practice use the SET
which is part of the SQL/PSM standard.

b) Code like " (@days + @RealNumerOfDays) * -1" show you do not know
that SQL has a unary minus. You are still writing your native language
which does not have that feature.

c) T-SQL is a really simple one-pass compiler. When you needlessly
create local variables, it obeys. For example, your @FirstPart is used
in one place and the CASE expression that defines it can be substituted
for it in.

When you write a function in T-SQL, the goal is to do it in one
expression inside the RETURN().   With a little algebra and effort you
can fold all of this into one statement that the SQL optimizer can use.
Here is a very quick and untested attempt.

CREATE FUNCTION dbo.SubtractWorkingDays
(@start_date DATETIME, @days INTEGER)
RETURNS DATETIME
AS
RETURN(
CASE WHEN
     (CASE DATENAME(weekday, @start_date)
                 WHEN 'Sunday' THEN 0
                 WHEN 'Monday' THEN 0
                 WHEN 'Tuesday' THEN 1
                 WHEN 'Wednesday' THEN 2
                 WHEN 'Thursday' THEN 3
                 WHEN 'Friday' THEN 4
                 WHEN 'Saturday' THEN 5
               END - @days) >= 0)
      THEN DATEADD(DAY, -@days, @start_date)
ELSE
   DATEADD (DAY, (@days -((((@days) / 5) + 1) * 2)),
@start_date)
END;

7 Sep 2006 2:36 PM

Sn0tters

--CELKO-- wrote:

Show quote

> CREATE FUNCTION dbo.SubtractWorkingDays
> (@start_date DATETIME, @days INTEGER)
> RETURNS DATETIME
> AS
> RETURN(
> CASE WHEN
>      (CASE DATENAME(weekday, @start_date)
>                  WHEN 'Sunday' THEN 0
>                  WHEN 'Monday' THEN 0
>                  WHEN 'Tuesday' THEN 1
>                  WHEN 'Wednesday' THEN 2
>                  WHEN 'Thursday' THEN 3
>                  WHEN 'Friday' THEN 4
>                  WHEN 'Saturday' THEN 5
>                END - @days) >= 0)
>       THEN DATEADD(DAY, -@days, @start_date)
>   ELSE
>    DATEADD (DAY, (@days -((((@days) / 5) + 1) * 2)),
> @start_date)
>   END;

The point about the naming is taken on.

As it stand that doesn't run as I can't create it as an inline function
due to it returning a DATETIME and not a table ( I presume ).

If I put a BEGIN select and END around the CASE then it still does all
it's work in the return. Can the optimizer do anything with that?

7 Sep 2006 4:15 PM

--CELKO--

>> If I put a BEGIN select and END around the CASE then it still does all it's work in the return. Can the optimizer do anything with that? <<

A single statement in a BEGIN-END block is still just a single
statement in Algol-like languages. The T-SQL parser treats them alike
in its single pass. Then the SQL engine gets that statement, so there
is no real difference.