Search This Blog

Lets go..

Tuesday

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

     

1 comment:

  1. Very nice SQL. it's more easy way to calculate workdays

    ReplyDelete

Thanks for your opinion