Hi AliYilmaz,
Refer below sample query.
SQL
CREATE TABLE #Employess (Id INT NOT NULL ,FName VARCHAR(MAX) NOT NULL,LName VARCHAR(MAX) NOT NULL)
CREATE TABLE #EffortTable (Id INT NOT NULL ,FName VARCHAR(MAX) NOT NULL, EfortDate DATETIME,[Hours] INT)
CREATE TABLE #HolidayTable (Id INT NOT NULL ,StartDate datetime NOT NULL,FinishDate datetime NOT NULL,HolidayName varchar(MAX) NOT NULL)
INSERT INTO #Employess VALUES(1, 'John','Cameron')
INSERT INTO #Employess VALUES(2, 'Cosh','Tango')
INSERT INTO #Employess VALUES(3, 'Sam','Smith')
INSERT INTO #EffortTable VALUES(1, 'John', '2019-07-10',8)
INSERT INTO #EffortTable VALUES(2, 'Cosh', '2019-07-10',8)
INSERT INTO #HolidayTable VALUES(1, '2019-04-23','2019-07-20', '23 Nisan Bayramı')
INSERT INTO #HolidayTable VALUES(2, '2019-08-10','2019-08-10', '10 Agustos Bayramı')
INSERT INTO #HolidayTable VALUES (3, '2019-10-29', '2019-10-29', '29 Ekim Bayramı')
DECLARE @Week_StarDate AS DATETIME
SET @Week_StarDate = (SELECT DATEADD(DAY, 2 - DATEPART(WEEKDAY, '2019/07/17'), CAST('2019/07/17' AS DATETIME)) [Week_Start_Date])
DECLARE @Week_EndDate AS DATETIME
SET @Week_EndDate = (Select DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATETIME)) [Week_End_Date] )
IF EXISTS(SELECT * FROM #HolidayTable WHERE StartDate BETWEEN @Week_StarDate AND @Week_EndDate OR FinishDate BETWEEN @Week_StarDate AND @Week_EndDate)
BEGIN
SELECT [Hours] FROM #EffortTable Hours
END
ELSE
BEGIN
SELECT 0 Hours
END
DROP TABLE #Employess
DROP TABLE #EffortTable
DROP TABLE #HolidayTable
