question

sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 asked LiHongMSFT-3908 commented

Calculate number of working days between 2 dates

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sujithkumarmatharasi-0343
Have you validate all the answers so far and provide any update?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered

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,
LossDate,
ClaimDate) values(1,'20210101','20210103')
,(1,'20210201','20210204')
,(1,'20210301','20210303')


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @sujithkumarmatharasi-0343
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.

 SELECT (DATEDIFF(dd,CAST(CAST(LossDate AS VARCHAR)AS DATE), CAST(CAST(ClaimDate AS VARCHAR)AS DATE)) + 1)
       -(DATEDIFF(wk,CAST(CAST(LossDate AS VARCHAR)AS DATE), CAST(CAST(ClaimDate AS VARCHAR)AS DATE)) * 2)
       -(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,
LiHong


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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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

See "udf_AddBusinessDays":

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.