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