Calculate number of working days between 2 dates

sujith kumar matharasi 351 Reputation points

Hi All,

I have 2 SQL tables with the below DDL & sample data :

Create Table dbo.Test
ID int,
Loss Date int,
ClaimDate Int

Insert Into dbo.Test values(1,20210101,20210103)
Insert Into dbo.Test values(1,20210201,20210204)
Insert Into dbo.Test values(1,20210301,20210303)

Create Table dbo.Calendar
CalendarDate Int,
DayOfWeek String,
DayNumber int

Insert Into dbo.Calendar values(20210101,'Sunday',1)
Insert Into dbo.Calendar values(20210102,'Monday',2)
Insert Into dbo.Calendar values(20210103,'Tuesday',3)
Insert Into dbo.Calendar values(20210201,'Saturday',7)
Insert Into dbo.Calendar values(20210202,'Sunday',1)
Insert Into dbo.Calendar values(20210203,'Monday',2)
Insert Into dbo.Calendar values(20210204,'Tuesday',2)
Insert Into dbo.Calendar values(20210301,'Wednesday',4)
Insert Into dbo.Calendar values(20210302,'Thursday',5)
Insert Into dbo.Calendar values(20210303,'Friday',6)

I need to join these 2 tables to get the below data:

ID LossDate ClaimDate DaysDiff between lossdate and claimdate (Excl.Weekends)
1 20210101 20210103 1
1 20210201 20210204 1
1 20210301 20210303 2

As January & February have weekends the datediff is 1 day but for March there is no weekend the datediff is 2

Can someone please help me with a query to get this , I cannot create any UDF's as per the requirement so i need to join these 2 tables to get the result.

Any help would be appreciated.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP

    I will have to assume that the Calendar table has rows for all dates, or else the question does not make sense.

    Further, I'm assuming that the ClaimDate is not included in the day count.

    SELECT * FROM dbo.Test
    SELECT * FROM dbo.Calendar
    SELECT T.ID, T.LossDate, T.ClaimDate, SUM(CASE WHEN C.DayNumber BETWEEN 2 AND 6 THEN 1 ELSE 0 END)
    FROM   dbo.Test T
    JOIN   dbo.Calendar C ON C.CalendarDate >= T.LossDate 
                         AND C.CalendarDate < T.ClaimDate
    GROUP  BY T.ID, T.LossDate, T.ClaimDate
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points

    Drop TABLE If exists dbo.Test
    --Modify your tabl, add a primary key
    Create Table dbo.Test
    keyId int identity(1,1),
    ID int,LossDate date,ClaimDate date)

    Insert Into dbo.Test (ID,
    ClaimDate) values(1,'20210101','20210103')

    select ID,LossDate,ClaimDate, count(*) -1 cnt
    from dbo.Test t
    cross apply (values(1),(2),(3),(4),(5)) d(n)
    Where ClaimDate<=dateadd(day,n-1,LossDate)
    and datepart(weekday,dateadd(day,n-1,LossDate)) not in (1,7)
    --You can use a full calendar table
    Group by ID,LossDate,ClaimDate

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points

    Hi @sujith kumar matharasi
    If you're going to use the calendar table,then refer to Erland's answer above.
    Also ,you can try this method to calculate working days between 2 dates.

    SELECT (DATEDIFF(dd, LossDate, ClaimDate) + 1)  
          -(DATEDIFF(wk, LossDate, ClaimDate) * 2)  
          -(CASE WHEN DATENAME(dw, LossDate) = 'Sunday' THEN 1 ELSE 0 END)  
          -(CASE WHEN DATENAME(dw, ClaimDate) = 'Saturday' THEN 1 ELSE 0 END) AS WorkDay_Numbers  
    FROM #Test  

    Considering that the datatype of date in the table is INT, so it needs to be converted to date datatype first.

          -(CASE WHEN CAST(CAST(LossDate AS VARCHAR)AS DATE) = 'Sunday' THEN 1 ELSE 0 END)  
          -(CASE WHEN CAST(CAST(ClaimDate AS VARCHAR)AS DATE) = 'Saturday' THEN 1 ELSE 0 END) AS WorkDay_Numbers  
    FROM #Test  

    For more details , please refer to this link : Count work days between two dates

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. Tom Phillips 17,716 Reputation points

    Use flags in your calendar table. The next thing you will want is to exclude business holidays.

    See "udf_AddBusinessDays":

    0 comments No comments