How to Calculate Working Days using Store procedure (SP)

By:Mohammad Tuaha 
 

Store procedure SQL server

Today I like to share working days calculation using store procedure T-SQL. Working days calculation need for salary sheet, Employee leave and attendance report.

Only work Days Calculation

      DECLARE @StartDate DATETIME = '2015/08/01'

      DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

            CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )

As [WorkDays]

Only Holidays Calculation

First create holiday’s table

CREATE TABLE [dbo].[tbl_lms_holidayList](

      [ID] [bigint] IDENTITY(1,1) NOT NULL,

      [HolidayName] [varchar](150) NULL,

      [Holiday] [date] NOT NULL,

      [HoliOfYear] [varchar](50) NULL,

      [Remark] [varchar](150) NULL,

      [Status] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[tbl_lms_holidayList] ADD  CONSTRAINT [DF_tbl_lms_holidayList_Status]  DEFAULT ((1)) FOR [Status]

GO

---------------------

DECLARE @StartDate DATETIME = '2015/01/01'

DECLARE @EndDate DATETIME = '2015/08/31'

(Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and     DATENAME(dw, @StartDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and       DATENAME(dw, @EndDate) != 'Sunday')

        )    

     

OR

DECLARE @StartDate DATETIME = '2015/01/01'

DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate and Status = 1)

Work Days Without Holidays [Total days – holidays]

DECLARE @StartDate DATETIME = '2015/08/01'

DECLARE @EndDate DATETIME = '2015/08/31'

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

         CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )    

      -  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and     DATENAME(dw, @StartDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and       DATENAME(dw, @EndDate) != 'Sunday'))   

       as [WorkDays]

 

Full Store Procedure

Create  PROCEDURE [dbo].[SP_Calculating_workdays]

            @StartDate DATETIME ,

            @EndDate DATETIME

       

AS

BEGIN            

      --    DECLARE @StartDate DATETIME = '2019/09/01'

      --    DECLARE @EndDate DATETIME = '2019/09/30'

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -

   (

         CASE

                  WHEN DATENAME(dw, @StartDate) = 'Sunday'

                  THEN 1

                  ELSE 0

            END

      )  -

      (

            CASE

                  WHEN DATENAME(dw, @EndDate) = 'Saturday'

                  THEN 1

                  ELSE 0

            END

      )    

      -  (Select Count(*) from  tbl_lms_holidayList

        where   Holiday between @StartDate and @EndDate

        and (DATENAME(dw, @StartDate) != 'Saturday' and DATENAME(dw, @EndDate) != 'Sunday')

        and (DATENAME(dw, @EndDate) != 'Saturday' and DATENAME(dw, @StartDate) != 'Sunday'))

       

       as [WorkDays]

                               

END