Sunday, January 31, 2016

Adding Work Days Using T-SQL

USE [MasjidBooks]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_ADD_WORKING_DAYS]    Script Date: 1/31/2016 12:52:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_ADD_WORKING_DAYS] (
  @DATE      DATE,
  @NDAYS     INT  
) RETURNS DATE    
BEGIN        

       IF @DATE IS NULL
         BEGIN      
           SET @DATE = GETDATE();
         END

       DECLARE @STARTDATE  INT  = 0
       DECLARE @COUNT      INT  = 0
       DECLARE @NEWDATE    DATE = DATEADD(DAY, 1, @DATE)                                        

       WHILE @COUNT < @NDAYS
        BEGIN
          IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (6, 7)
      AND @NEWDATE NOT IN ( SELECT Officialholidays FROM Officialholiday )
  AND ((select Count(*) from [dbo].[Order] where CollectionDate=@NEWDATE and OrderTypeID=2) < (select Top 1 DailyQue From [dbo].[SystemPartameter]))
            SET @COUNT += 1;
            SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;
        END

        RETURN DATEADD(DAY, @STARTDATE, @DATE);
  END
---------------------------------------------------------------------------------------------------------------
Original Article
http://social.technet.microsoft.com/wiki/contents/articles/26413.adding-work-days-using-t-sql.aspx

Introduction

This article presents a Transact-SQL Scalar Function to determine a future date, only considering the next working days.
    Determine a future working day is a very common process in service delivery companies such as: Transporters, production of perishable food and other.

    This sample helps those who have a similar need, as described above. You can add more details to enhance the accuracy if needed, specifying hours, minutes and seconds.

    Please feel free to add more useful content.


    Defining work days


    Depending on the different cultures in each Country or even each Job sectors, we can have different start days of the week. 

    We can better understand this configuration and also as SQL Server work day of the week (by default), showing in calendar below  the "work day" used to demo (Wednesday, 08/27/2014) and "off day work" (Saturday, 08/30/2014and Sunday, 08/31/2014).

    See below the image.

    By default, the SQL Server define Sunday (weekday=7)  as the start of  the week based on "us_english" language. This information we can get through @@DATEFIRST   variable, but it's important to note that day indicated in SET DATEFIRST  configuration is only a reference for "weekday" parameter. This is used and better explained in the BOL - DATEPART   method. 

    In T-SQL script below, we change "start of the week" reference(weekday) just changing the language in the current context. This demo, vamos compares ​​"Brazilian Portuguese", "British English" and "American English"(default)languages, using date "08/31/2014" (Sunday).

    See below the image (click to enlarge).


    See below this T-SQL script
    DECLARE @SampleDate AS DATE = '2014-08-31';
    SET LANGUAGE Brazilian;             -- BrazilianSELECT 'Brazilian' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;
    SET     LANGUAGE British;                -- English - United KingdomSELECT 'English -UK' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;
    SET     LANGUAGE us_english;             -- English - United StatesSELECT 'English - USA' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName,DATEPART(weekday,@SampleDate) AS WeekDayNumber;    GO

    We can see to change start of the weekday, based on cultural pattern for each Country, changed SET LANGUAGE  configuration and therefore the "weekday" reference number for each day of a week.

    Now if we use the SET DATEFIRST   configuration, this command is higher than in the SQL Server behavior and their weekday relationship.

    In this way, the T-SQL script below  modifies the SQL Server default and all queries that will have same identifier for "weekday".

    See below the image (click to enlarge).


    See below this T-SQL script
    DECLARE @SampleDate AS DATE = '2014-08-31';
    SET DATEFIRST 2;
    SET LANGUAGE Brazilian;             -- BrazilianSELECT 'Brazilian' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) ASWeekDayNumber;
    SET     LANGUAGE British;               -- English - United KingdomSELECT 'English - UK' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) ASWeekDayNumber;
    SET     LANGUAGE us_english;             -- English - United StatesSELECT 'English - USA' AS CurrentLanguage, @@DATEFIRST AS SetDateFirstSQL,
    DATENAME(weekday,@SampleDate) AS WeekDayName, DATEPART(weekday,@SampleDate) ASWeekDayNumber;    GO

    We can see to change the start day a week by SET DATEFIRST setting to use Job sectors becomes new "weekday" in the SQL Server context, regardless of the "language - Country" used.

    In the following demo, we are using the SQL Server weekday default (us_english), as shown in the table below:

    Day of Week DATEFIRST SQL WeekDay (default)
    Monday12
    Tuesday23
    Wednesday34
    Thursday45
    Friday56
    Saturday67
    Sunday71

    Considering that the "Off Days" used for this demo are Saturday and Sunday, we will remove days respectively with "weekday" 7 and 1.


    Creating the Function


    The structure of this function intends to dispose non-working days for requested service to establish a delivery date.

    Thereby, we use the methods: DATEADD and DATEPART to add and compare date scheduled for next working day, discarding "Saturday" (weekday = 7) and "Sunday" (weekday = 1).

    You can also discard the national holidays, state(where applicable - for each country) holidays and/or city holidays, but although not the scope of this article we can add a query table to output several holidays and thus also removing these dates.

    I recommend way of error handling, include a check for @DATE parameter. If value is NULL, then get current date using the GETDATE function.

    Soon after, we make a loop using @COUNT variable as counter until number of days indicated on @NDAYS parameter.

    See below this T-SQL script
    CREATE FUNCTION dbo.ufn_ADD_WORKING_DAYS (  @DATE      DATE,  @NDAYS     INT   ) RETURNS DATE     BEGIN         
           IF @DATE IS NULL         BEGIN                  SET @DATE = GETDATE();         END
           DECLARE @STARTDATE  INT  = 0       DECLARE @COUNT      INT  = 0       DECLARE @NEWDATE    DATE = DATEADD(DAY, 1, @DATE)                                         
           WHILE @COUNT < @NDAYS         BEGIN           IF DATEPART(WEEKDAY, @NEWDATE) NOT IN (7, 1) --AND @NEWDATE NOT IN ( SELECT DT_HOLIDAY FROM TB_HOLIDAYS )             SET @COUNT += 1;            SELECT @NEWDATE = DATEADD(DAY, 1, @NEWDATE), @STARTDATE += 1;        END 
            RETURN DATEADD(DAY, @STARTDATE, @DATE);  END GO

    Note
    The starting day of the week (using SET DATEFIRST) as we used in this demo is the SQL Server default.
    You can change this Scalar Function on T-SQL script for dismiss other "days off" as used in the IN  condition.


    Using the Function


    See below a sample. We have a string date, converted using CAST function to DATE datatype and we added 4 working days.

    So, we use 08/27/2014 (Wednesday) and considering we were removing days: 08/30/2014 (Saturday) and 08/31/2014 (Sunday), then we will now have resulting 09/02/2014 as the next working day.

    See below the image.


    See below this T-SQL script.
    SELECT dbo.ufn_ADD_WORKING_DAYS( CAST('2014-08-27' AS DATE), 4 )GO

    This function can also be used in a SELECT statement to data query table, using as parameter in this Scalar Function a column with datatype DATE.


    Conclusion

    This Scalar Function is important to identify a date limit to work or delivery schedules, using only next working days to enhance conditions for data storage and display on reports. 

    Propose a deadline for completion of work with higher accuracy is one of the best criteria to differentiate your Company on the market.


    References


    See Also

    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)