CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Calculate Total Working Days of month

I have this table where I am specifying Holidays in week. I want to calculate total working days between 2 specific dates using these fields. 

CREATE TABLE [tbl_Shift](
[OffDay1] [nvarchar](25) NOT NULL CONSTRAINT [DF_tbl_Shift_OffDay1]  DEFAULT (N'Sunday'),
[IsAlternateOffDay2] [bit] NULL,
[OffDay2] [nvarchar](25) NULL
)

INSERT INTO [tbl_Shift] VALUES ('Sunday', 'True', 'Saturday')

I have this query written but I am not able to get correct days. It should give 23 days as there are 2 holidays in each week and 31 days in total but I'm getting 26 days.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/05/01'
SET @EndDate = '2018/05/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
   -(CASE WHEN IsAlternateOffday2 = 1 THEN 1 END) FROM HRM.tbl_Shift

Upvotes: 4

Views: 14959

Answers (5)

pranshu gupta
pranshu gupta

Reputation: 1

MS SQL Server query to find count of all the working days (non Saturdays and Sundays) in current month. Works in SQL Server, Azure Synapse Analytics

Note: Change the getdate() as per your need.

SELECT Day(Eomonth(Getdate())) - ( Datediff(d, Dateadd(d, Datediff(d, -1,
                                               Dateadd(month, Month(
                                               Getdate()) - 1 + (
                                   Year(Getdate()
                                   ) - 1900 )
                                   *
                                   12, 6)) / 7 * 7, -1),
                                   Dateadd(m, 1, Dateadd(
                                   month,
                                                 Month(Getdate())
                                                 - 1 +
                                                 (
                                   Year(Getdate()) - 1900 )
                                                 *
                                                 12, 6
                                                 )
                                   )) / 7 +
                                          Datediff(d, Dateadd(d, Datediff(d, -1,
                                                                 Dateadd(
                                                                 month,
                                                      Month(Getdate()) - 1 + (
                                          Year(Getdate()
                                          ) - 1900 )
                                          *
                                          12, 7)) / 7 * 7, -1), Dateadd(m, 1,
                                          Dateadd(month, Month(Getdate()) - 1 +
                                                         (
                                          Year(Getdate()) - 1900 ) *
                                                              12, 7)
                                          )) / 7 ) AS Num_Working_Days

Upvotes: 0

ZeroCool
ZeroCool

Reputation: 1

This was what I created after reviewing the answers. I needed the number of days per month for several months. The below will provide that, and insert it into the #MonthDayCount table. Note: I named a field Month, even though that is a SQL Server defined term; you can change it if needed.

You must enter the first day of the month and the last for it to count the first and last month correctly. If you entered 1/2/2022, it would result in January being short one day.

DECLARE @StartDate DATE = '1/01/2021'--The start of the first month the number of days are needed for.
DECLARE @EndDate DATE = '1/31/2021'--The end of the first month the number of days are needed for.
DECLARE @FinalDate DATE = '12/31/2022'--This is the last month that will be inserted. Includes this month.

IF OBJECT_ID('tempdb..#MonthDayCount', 'U') IS NOT NULL
DROP TABLE #MonthDayCount; 
CREATE TABLE #MonthDayCount
                            (
                                [Month]             VARCHAR(MAX),
                                [# of WeekDays]     INT,
                                [EOM]               DATE
                            )


WHILE           @StartDate <= @FinalDate
BEGIN 
INSERT INTO     
    #MonthDayCount
                    VALUES  (
                                DATENAME(m,@StartDate),
                                (
                                    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
                                ),
                                @EndDate
                            )


  SET @StartDate = DATEADD(m,1,@StartDate)
  SET @EndDate = EOMONTH(@EndDate,1)
 


END

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

Try This

DECLARE @StartDate DATE ='2018-05-01',
        @EndDate DATE ='2018-05-31'
;WITH CTE
AS
(
SELECT DATEADD(DD,Number-1,@StartDate) MOnthDates,
       DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) As DayNAmes,
       CASE WHEN DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) IN ('Saturday','Sunday') THEN 0 ELSE 1 END WeekDays
FROM master.dbo.spt_values
WHERE [Type]='P'
AND Number Between 1 AND 10000
)
SELECT COUNT(WeekDays)  AS WeekDaysCount
FROM CTE
WHERE WeekDays<>0
AND MOnthDates Between @StartDate AND DATEADD(DAY,1,@EndDate)

Result

WeekDaysCount
-------------
23

Demo:http://rextester.com/TOLYT35075

Upvotes: 1

Sahi
Sahi

Reputation: 1484

this will give 23:

SELECT
    (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
    -ISNULL((CASE WHEN IsAlternateOffday2 = 1 THEN (DATEDIFF(wk, @StartDate, @EndDate)) END), 0) 
FROM HRM.tbl_Shift

Upvotes: 1

pacreely
pacreely

Reputation: 1931

This subtracts 2 days for every weekend. Check your server config if Sunday is first or last day of the week. This can throw things off by a weekend.

SELECT DATEPART(WEEKDAY,'20180506') --Checks if Sunday is Day 1 or Day 7

DECLARE @start DATETIME = '20180501'
DECLARE @end DATETIME = '20180531'

SELECT DATEDIFF(DAY,@start,@end+1) - (DATEDIFF(WEEK,@start,@end+1)*2)

UPDATE:

Use COALESCE to replace NULLS with an alternative value.

DECLARE @offdate DATETIME = NULL

SELECT COALESCE(@offdate,GETDATE())

Upvotes: 1

Related Questions