I would like to query our database to display records from current year going back to year 2017.
In other words, I would like something like this:
SELECT employeeName, email, emptitle, EmpID, CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) -2 THEN 1 ELSE 0 END as pastYears, CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) -1 THEN 1 ELSE 0 END as lastYear, CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as currentYear 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 WHERE e.EmpID='95934' END
In other words, I would like to display three columns, currentYear, lastYear, pastYears
Data for the this year will be displayed in the currentYear column.
Data for previous year will be displayed in the lastYear column
Data for years before last year will be displayed in the pastYears column regardless of the year as long as it is not current year or last year.
I am having a little problem with formatting date for pastYears.
Any thoughts on what I need to do?
Please note, I am not really concerned about time. We need data for year only.
Many thanks for your assistance.