SQLTeam.com | Weblogs | Forums

Any ideas why this query would not produce any results?

I am joining three tables in attempt to display records from previous year if the records exist on the database.

The tables are:

Employees
employeeID PK int
empID nvarchar(50)

SourceDetails
employeeID (FK to Employees table) int
sourcename nvarchar(50)
sourceaddress(nvarchar(75)

dateDetails table
employeeID (FK to Employees table) int
dateCreated dateTime

We have this app that runs every year and the users asked that previous year's data be populated to the form when the page loads and user enters his/her empID to confirm identity.

The following query should return results if previous record exists verified by entering empID.

emPID is each employee's badge ID.

Can you please tell me where I am going wrong with this query?

ALTER PROCEDURE [dbo].[uspGetEmpRecs] 
    @empID nvarchar(50)
AS
begin
    SET NOCOUNT ON;
    select ROW_Number() OVER (ORDER BY(SELECT 0)) ID, s.sourcename As sourcename, d.dateCreated, s.sourceaddress As sourceaddress
    FROM SourceDetails s INNER JOIN  DateDetails d ON s.EmployeeID = d.EmployeeID 
	INNER JOIN Employees e ON s.EmployeeID = e.employeeID
    WHERE d.dateCreated > DATEADD(year,-1,GETDATE()) AND e.empID = @empID
end

For context, I am running only query by replacing empID with actual ID from DB.

The idea is once the query is confirmed to be working correctly, then I will execute the stored procedure.

Thanks a lot in advance

Most likely the issue is related to this statement: DATEADD(year, -1, GETDATE())

That statement will give you one year ago from today at the time it is executed. For example - if executed on 2020-01-23 13:15:00.000 it will return 2019-01-23 13:15:00.000. The query is looking for any data from that point in time forward.

Try removing the date requirement and returning all relevant data for the employee - and see what is returned. Then you can determine if the query is returning the correct information or if there is some other issue.

Now - if you want to look at the full range of the previous year - then you would want this:

WHERE d.dateCreated >= DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0)
AND d.dateCreated < DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)

That will included all dates for the prior year...

Thank you very much.