I need to return results for either this year, last year, or the years before last year

Transact-SQL
1

Greetings experts,

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.

2

Somewhat vague and contradictory, but generally like this:

SELECT employeeName, email, emptitle, EmpID,
    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 lastYear, 
    CASE WHEN d.dateCreated >= curr_yr_jan01 THEN 1 ELSE 0 END as currentYear 
    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
    ...
3

what do you want to see in that column?

create table #flexseed(EmpID int not null identity(1,1), 
employeename varchar(50), email varchar(50), emptitle varchar(50), 
dateCreated datetime);

insert into #flexseed
select 'Darth Vader', 'dvader@darkside.emp', 'Darth', 
dateadd(year,0, getdate()) union
select 'Sheev Palpatine', 'spalpatine@darkside.emp', 'Emperor',  
dateadd(year,-1, getdate()) union
select 'Obi-Wan Kenobi ', 'o1k@lightside.emp', 'Emperor',  
dateadd(year,-2, getdate())
  

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 #flexseed d 

drop table #flexseed
4

Thank you guys very much for your quick responses.

Scott, not sure what you mean by contradictory.

Also, where did this:
prev_yr_jan01 come from?

yosiasz, doesn't this -> ```
CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) -2
show last two years?

For instance, if last year is 2019, then your code will show the year as 2018?

And to answer your question as to what data I want to see, all records that are created in 2020 (current year) should display under currentYear; all records for last year should be displayed under last year column and all records that are from 2018 or earlier should show under pastYears column.

At least that's what I had in mind with the code I showed.

Thanks again.

5

The prev_yr_jan01 came from the CROSS APPLY I added to the query.

Generally you don't want to apply functions on a column in every row if it can be avoided because of the overhead and the potential performance impacts on the query plan.

1 Like
6

Ok, sorry; it is right there. I am stupid.

7

Thank you very much Scott!

It worked.

8

I thought that was somewhat contradictory since the "current year" is 2023, so going back 2 years would not get back to 2017. But you likely meant "current year" as "year the row was added"(?).