SQL Working days Function (excludes weekend and after hours)

SQL Working days Function (excludes weekend and after hours)

DECLARE @BeginDate DATETIME, @EndDate DATETIME, @RunDate DATETIME

DECLARE @Hours DECIMAL(38,8)

SET @BeginDate = '2015-06-18 8:15:33'

SET @EndDate = '2015-06-18 9:22:33'

SET @Hours = 0

       BEGIN

              -- Day 1

              IF (@begindate < @RunDate)

                           BEGIN

                     SET @RunDate = DATEADD(d, DATEDIFF(d, 0, @BeginDate), 0)

                     SET @RunDate = DATEADD(n, 1050, @RunDate)

                     SELECT @Hours = DATEDIFF(n, @begindate, @RunDate)

                           END

                       ELSE

                           BEGIN

                                  SET @RunDate = @EndDate

                           END

              SET @RunDate = DATEADD(d, 1, DATEDIFF(d, 0, @BeginDate))

              -- Day 2 < Max

              WHILE (DATEDIFF(d, @RunDate, @EndDate) > 0)

                     BEGIN

                           SELECT

                                  @Hours = ISNULL(@Hours, 0) +

                                         CASE

                                                WHEN datepart(dw,@RunDate) BETWEEN 2 AND 6

                                                       THEN 540 --(17.5-8.5) * 60

                                                ELSE

                                                       0

                                                END

                           SET @RunDate = DATEADD(d, 1, @RunDate)

                     END

             

              -- Last day

              SELECT

                     @Hours = ISNULL(@Hours, 0) +

                           CASE

                                  WHEN DATEDIFF(n, DATEADD(d, 0, DATEDIFF(d, 0,@EndDate)),@EndDate) > 1050 THEN 540

                                  ELSE

                                         DATEDIFF(n, DATEADD(d, 0, DATEDIFF(d, 0,@EndDate)),@EndDate) - 540

                                  END

              SELECT

                     @BeginDate

                     ,@EndDate

                     ,WorkHours                               = @Hours/60

       END

SQL Split Function

SQL Split Function

DROP FUNCTION [dbo].[ufn_Split]

GO

CREATE FUNCTION [dbo].[ufn_Split](@Val varchar(max), @Del varchar(max) = ',')

RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(max))

AS

BEGIN

       DECLARE @idx smallint,

       @value varchar(max),

       @bcontinue bit,

       @iStrike smallint,

       @iDelimlength tinyint

       IF @Del = 'Space'

       BEGIN

       SET @Del = ' '

       END

       SET @idx = 0

       SET @Val = LTrim(RTrim(@Val))

       SET @iDelimlength = DATALENGTH(@Del)

       SET @bcontinue = 1

       IF NOT ((@iDelimlength = 0) or (@Del = 'Empty'))

       BEGIN

       WHILE @bcontinue = 1

       BEGIN

       --If you can find the delimiter in the text, retrieve the first element and

       --insert it with its index into the return table.

       IF CHARINDEX(@Del, @Val)>0

       BEGIN

       SET @value = SUBSTRING(@Val,1, CHARINDEX(@Del,@Val)-1)

       BEGIN

       INSERT @retArray (idx, value)

       VALUES (@idx, @value)

       END

       --Trim the element and its delimiter from the front of the string.

       --Increment the index and loop.

       SET @iStrike = DATALENGTH(@value) + @iDelimlength

       SET @idx = @idx + 1

       SET @Val = LTrim(Right(@Val,DATALENGTH(@Val) - @iStrike))

       END

       ELSE

       BEGIN

       --If you can’t find the delimiter in the text, @Val is the last value in

       --@retArray.

       SET @value = @Val

       BEGIN

       INSERT @retArray (idx, value)

       VALUES (@idx, @value)

       END

       --Exit the WHILE loop.

       SET @bcontinue = 0

       END

       END

       END

       ELSE

       BEGIN

       WHILE @bcontinue=1

       BEGIN

       --If the delimiter is an empty string, check for remaining text

       --instead of a delimiter. Insert the first character into the

       --retArray table. Trim the character from the front of the string.

       --Increment the index and loop.

       IF DATALENGTH(@Val)>1

       BEGIN

       SET @value = SUBSTRING(@Val,1,1)

       BEGIN

       INSERT @retArray (idx, value)

       VALUES (@idx, @value)

       END

       SET @idx = @idx+1

       SET @Val = SUBSTRING(@Val,2,DATALENGTH(@Val)-1)

       END

       ELSE

       BEGIN

       --One character remains.

       --Insert the character, and exit the WHILE loop.

       INSERT @retArray (idx, value)

       VALUES (@idx, @Val)

       SET @bcontinue = 0

       END

       END

       END

       RETURN

END