Select last week data from current date based on condition in SQL Server

Last Reply 4 months ago By pandeyism

Posted 5 months ago

Hi everyone,

this job will work every Monday at first. He's going to check up last week conditions 

for example

x users - at week 23 - 8 hours of data entered - TotalWatch 40 - 8 - 8 = 24 hours of data if that week coincides with April 23(holiday table).

If x user - also in the 23rd week - no data entered - If that week is equalizing April 23 Total Time 40 - 8 = 32 hours need to enter data.

If x user - also in week 23 - no data entered - that week no holidays, ie Total Time 40 = 40 hours.

I created sample data. Also I shared my code.

I want to make all employees come from the table of employees and efort table that week, if the data entered the total hours come. If the data is not entered that week, total hours 0.

http://sqlfiddle.com/#!9/cf4da7/1

Posted 4 months ago

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