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.