Get most recent data based on Date but I am stuck on the CASE statement-CodePudding

I have looked into the suggested similar questions presented by StackOverflow but my question has a twist that I did not see on any of the suggested solutions.

We have a survey on our web app that selected group of individuals are required to complete and submit every year.

Each year, one of the members of this select group opens the survey, enters his/her employee ID for validation.

If this employee has completed this form previously, then his/her most recent record based on date created (dateCreated) partially populates the form for him / her.

If the user has never completed the form, then he/she is presented with a blank form to complete the entire form.

This part seems to work fine.

Prior the the COVID 19 pandemic, the following CASE expression makes it easier to identify whether a user has completed the form the previous year or has never completed the form.

CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) -1 THEN 1 ELSE 0 END as previousYear, 
CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear

The pandemic has created a two year gap as a result of this process being halted for two years.

How do I modify the above CASE expression to say that if YEAR(dateCreated) is equal to, or earlier than, last year then 1 else if YEAR(dateCreated) is equal to this year (someone has already submitted this form this year then 1 else 0?

We use the value of 1 or 0 in our asp.net app to determine what information to display to user.

If value is 1 then user has completed this form before, populate the form with the approved records from the database. If this is the first time the user is completing this form, present a completely blank form to him/her.

Below is the complete stored procedure are trying to modify its CASE statement.

ALTER PROCEDURE [dbo].[getRecs]
@empID varchar(50)
 AS
  BEGIN
   SET NOCOUNT OFF;

     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 YEAR(d.dateCreated) = YEAR(getdate()) -1 THEN 1 ELSE 0 END as previousYear, 
       CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
    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
   End

CodePudding user response：

Maybe I'm missing something, but it seems like you could just ask for the year of d.dateCreated being less than year of getdate().

CASE WHEN YEAR(d.dateCreated) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear

Is there a reason this won't work?