Thursday, June 7, 2012

Eliminate holidays (weekends, specific day) using T-SQL


Eliminate holidays (weekends, specific day) using T-SQL
One of the tasks that may we have to deal with is how to eliminate weekends and holidays from a period using SQL server, e.g. if you want to calculate number of business days for specific company you may need to eliminate Saturday and Sunday or Friday.
To do this you can use DATENAME function to get name of the day and then eliminate this day from your selection.
In the bellow query I calculate the number of business days excluding Friday.
To do this I used Function and then call this function in may select statement.
ALTER FUNCTION [dbo].[Caldates]
(
      @SDate Date,
      @EDate Date
)
RETURNS int
AS
BEGIN
      DECLARE @WeekEnd AS NVARCHAR(15)
      DECLARE @Days AS INT
      SET @WeekEnd = 'Friday' – you can create more than one variable for different days
      SET @Days = 0
     
      WHILE @SDate <= @EDate
      BEGIN
            IF DATENAME(dw, @SDate) <> @WeekEnd
            BEGIN
                  SET @Days = @Days + 1 –-  you can use this to calculate number of hours spend in business days by multiplying it by the number of hours per day.
            END
            SET @SDate =  DATEADD(day,1,@SDate)
      END
      return @Days
END
Your select statement
SELECT [dbo].[ Caldates] (‘2012-01-01’, ‘2012-06-07’)
GO
You can change the above query as you want, and please share another ways to play with date.

Written by: Elmozamil Elamir Hamid
7-6-2012.

1 comment:

Card