Get last year data + use search criteria within the last year date range

Question

Get last year data + use search criteria within the last year date range

PJ_SQL

SSCertifiable

Points: 6703
More actions
October 25, 2022 at 3:11 am

#4103972

Hello!
I have a scenario where I need to get employee(s) who were hired last year and also need to able to filter through date range provided within the last year data.
Example:
Table : Employee
Emp_Id Emp_Name Employee_HireDate
1 ABC 09/09/2021
2 AAA 10/01/2021
3 ABD 10/02/2021
4 ABB 10/02/2020
5 CCC 01/09/2019

If I use date range @startdate>='01/01/2021 and @enddate <='09/30/2021' then output
Emp_Id Emp_Name Employee_HireDate
1 ABC 09/09/2021
If I use date range @startdate>='01/01/2021 and @enddate <='10/30/2021' then output
Emp_Id Emp_Name Employee_HireDate
1 ABC 09/09/2021
2 AAA 10/01/2021
3 ABD 10/02/2021
If I use date range @startdate>='01/01/2020 and @enddate <='01/31/2020' then output should say something like 'Out of Date Range' as it only should pull data for last year.

Thanks in advance.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

So test the values of @startdate and @enddate to make sure that they're both >= DATEADD(yy,-1,CONVERT(DATE,GETDATE())) and pop the message if they're not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)