Annual report (scheduled) that would look back at everyone that has a seniority date between Jan 1 and Dec 31 of the prior year. I can look back to the previous month with the below code, but how do I look back at the entire previous year?
[Seniority Date] between
_first_of_month( _add_months(current_date,-1))
and
_last_of_month(_add_months(current_date,-1))
Thank you for your help !
This is one of those questions that could have a near infinite number of answers.
Are you on Oracle or SQL Server?
If SQL Server try something like year([Seniority Date]) = year(getDate()) - 1
My Oracle SQL is very very rusty, but you could try something like trunc([Senoirity Date],'year') = tonumber(tochar(current_date,'yyyy'))-1
I am running SQL and your suggestion worked PERFECTLY. Thank you for your help, I was stuck thinking I had to use "between" and your suggestion of YEAR got me on the right track. Thanks again!!
Or with a Cognos function, EXTRACT() which not particular to any specific RDBMS:
extract(YEAR,[Seniority Date]) = extract(YEAR,current_date) - 1