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