Query the database and return results based on the most current date record is created

Transact-SQL
1

Greetings again experts.

The code below returns results based or current year, previous year or years after last year.

Thanks to Scott Pletcher for his contributions to making my original code work.

Here is that code:

 ALTER PROCEDURE [dbo].[uspGetRecs] 
     @empID nvarchar(50)
 AS
 begin
     SELECT e.employeeName, e.email, e.emptitle, e.EmpID,s.sourcename, s.sourceaddress,d.dateCreated,sp.spousename, sp.spouseaddress,
 	 r.reimbursementName,r.reimbursementAddress, h.HonorariaName,h.HonorariaAddress, 
 	 h.HonorariaName as honoraria,h.HonorariaAddress,h.HonorariaIncome,g.giftName,g.giftAddress,dv.dividentName,dv.dividentAddress,
 	 o.orgName,o.orgAddress,cr.creditorName, cr.creditorAddress,
      CASE WHEN d.dateCreated < prev_yr_jan01 THEN 1 ELSE 0 END AS pastYears,
      CASE WHEN d.dateCreated >= prev_yr_jan01 AND d.dateCreated < curr_yr_jan01
           THEN 1 ELSE 0 END as previousYear, 
      CASE WHEN d.dateCreated >= curr_yr_jan01 THEN 1 ELSE 0 END as thisYear 
      FROM Employees e 
      CROSS APPLY (
          SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS curr_yr_jan01,
              DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS prev_yr_jan01
     ) AS yr_start_dates
 	INNER JOIN  SourceDetails s ON e.EmployeeID = s.EmployeeID 
 	INNER JOIN  SpouseDetails sp ON e.EmployeeID = sp.employeeID
 	INNER JOIN  DividentDetails dv ON e.EmployeeID = dv.EmployeeID 
 	INNER JOIN  ReimbursementDetails r ON e.EmployeeID = r.employeeID
 	INNER JOIN  Honoraria h ON e.EmployeeID = h .EmployeeID 
 	INNER JOIN  GiftDetails g ON e.EmployeeID = g.employeeID
 	INNER JOIN  dateDetails d ON e.EmployeeID = d.employeeID
 	INNER JOIN  org o ON e.employeeID = o.employeeID
 	INNER JOIN  creditorDetails cr ON e.employeeID = cr.employeeID
     WHERE e.EmpID=@empID
 	ORDER By d.dateCreated DESC
 end

Our management determined that it needs to be simplified.

Right now, the new requirement is to query the database and display records (if it exists) based on the latest date the record is created.

This form is completed every year and date the form is created called dateCreated is saved on the database.

For instance, if an employee completed the form in 2020 and 2021, when the employee attempts to complete the form for the year 2022, the database is queried and in this example, we would like the employee records from the latest date of 2021 should be displayed.

Any ideas how to modify this code to just pull records from the database based on the latest date the form was last completed by an employee?

Many thanks in advance for your assistance.

2

UPDATE:

I have an updated code that works partially.

When I join Employee table to dateDetails table, I am able to get the latest value by date.

However, I need to join those two tables to the rest of the tables to get latest value for ALL tables.

Can someone please see where I am getting it wrong?

ALTER PROCEDURE [dbo].[uspGetRecs] 
     @empID nvarchar(50)
 AS
 begin
     SELECT e.employeeName, e.email, e.emptitle, e.EmpID,s.sourcename, s.sourceaddress,d.dateCreated,sp.spousename, sp.spouseaddress,
 	 r.reimbursementName,r.reimbursementAddress, h.HonorariaName,h.HonorariaAddress, 
 	 h.HonorariaName as honoraria,h.HonorariaAddress,h.HonorariaIncome,g.giftName,g.giftAddress,dv.dividentName,dv.dividentAddress,
 	 o.orgName,o.orgAddress,cr.creditorName, cr.creditorAddress
      FROM Employees e 
      INNER JOIN
       (
    Select e.EmpID, Max(d.dateCreated) dateCreated
    From Employees e INNER JOIN dateDetails d ON e.EmployeeID = d.employeeID
    Group BY e.EmpID
) MaxDates
On e.EmployeeID = MaxDates.EmployeeID
And e.dateCreated = MaxDates.dateCreated

 	INNER JOIN  SourceDetails s ON e.EmployeeID = s.EmployeeID 
 	INNER JOIN  SpouseDetails sp ON e.EmployeeID = sp.employeeID
 	INNER JOIN  DividentDetails dv ON e.EmployeeID = dv.EmployeeID 
 	INNER JOIN  ReimbursementDetails r ON e.EmployeeID = r.employeeID
 	INNER JOIN  Honoraria h ON e.EmployeeID = h .EmployeeID 
 	INNER JOIN  GiftDetails g ON e.EmployeeID = g.employeeID
 	INNER JOIN  dateDetails d ON e.EmployeeID = d.employeeID
 	INNER JOIN  org o ON e.employeeID = o.employeeID
 	INNER JOIN  creditorDetails cr ON e.employeeID = cr.employeeID
     WHERE e.EmpID=@empID
 	ORDER By d.dateCreated DESC
 end
3

this is very generic general

sometimes when you join to the other tables
-- the data is lost or the data becomes different

debugging
one join at a time .. are you getting the expected result or missing something .. rectify it
move on to the next join

4

MaxDates.EmployeeID doesn't exists, MaxDates.EmpID does.

5

Thanks for pointing that out.

Just before your response, I tried to edit the post to fix it but there was a message that it was too late to edit or delete post.

This appears to be working as it is getting only latest values based on latest date.

     SELECT e.employeeName, e.email, e.emptitle, e.EmpID,s.sourcename, s.sourceaddress,d.dateCreated,sp.spousename, sp.spouseaddress,
 	 r.reimbursementName,r.reimbursementAddress, h.HonorariaName,h.HonorariaAddress, 
 	 h.HonorariaName as honoraria,h.HonorariaAddress,h.HonorariaIncome,g.giftName,g.giftAddress,dv.dividentName,dv.dividentAddress,
 	 o.orgName,o.orgAddress,cr.creditorName, cr.creditorAddress
      FROM Employees e 
	  
 	INNER JOIN  SourceDetails s ON e.EmployeeID = s.EmployeeID 
 	INNER JOIN  SpouseDetails sp ON e.EmployeeID = sp.employeeID
 	INNER JOIN  DividentDetails dv ON e.EmployeeID = dv.EmployeeID 
 	INNER JOIN  ReimbursementDetails r ON e.EmployeeID = r.employeeID
 	INNER JOIN  Honoraria h ON e.EmployeeID = h .EmployeeID 
 	INNER JOIN  GiftDetails g ON e.EmployeeID = g.employeeID
 	INNER JOIN  dateDetails d ON e.EmployeeID = d.employeeID
 	INNER JOIN  org o ON e.employeeID = o.employeeID
 	INNER JOIN  creditorDetails cr ON e.employeeID = cr.employeeID
      INNER JOIN
       (
    Select e.EmpID, Max(d.dateCreated) AS dateCreated
    From Employees e INNER JOIN dateDetails d ON e.EmployeeID = d.employeeID
    Group BY e.EmpID
) MaxDates
On e.empid = MaxDates.EmpID
And d.dateCreated = MaxDates.dateCreated
WHERE e.EmpID=@empID
ORDER By d.dateCreated DESC