Solved Suggestions on a more automated year criteria? (1 Viewer)

[gojets1721]

I have a DB of complaints dating back 10 years, and I have a query specifically meant to filter to complaints from only the last three calendar years (i.e. 2022, 2021, and 2020).

Right now, I do that by creating a year column using the complaint date and then placing the following into the criteria:

In("2022","2021","2020")

I'd like to see if there's a better criteria to be using, since I'll need to manually update these years at the beginning of each year. I've used Year([SalesDate]) = Year(Date()) - 1 before, for instance, to zero in on only last years entries, but I haven't found something for the past 3 years.

Any suggestions?

 

[plog]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

 

[moke123]

how about something like

> dateserial(year(date)-2,1,1)

 

[The_Doc_Man]

Are the database records of these complaints based on date variables or dates stored as strings?

Normally, what I would do is create a date variable, then load it with Year( Now() ) - which would give me 2022. Then subtract 2. Then create a date like

strX = "#01-Jan-" & CStr( Year( Now() ) - 2 ) & "#"

Then you can concatenate that into an SQL string based on Year( Now() ) >= strX

or something similar to that.

 

[jdraw]

It might be helpful if you could show us any table structures or queries or forms involved.
Is there some issue eg. timing/speed at the root of your question?

 

[Pat Hartman]

If you are talking about calendar years, Plog's solution is fine. If you want elapsed years, then something that uses dateadd() would be more appropriate.

Where YourDate >= DateAdd("yyyy", -3, YourDate)


The other two solutions specifically bring you back to 1/1/2020 so you get all of 2020, all of 2021 and ytd for 2022 so the result is essentially the same as plog's. The Dateadd() solution brings you back to 7/27/ of 2019 because it calculates full years.

Take your pick.

 

[gojets1721]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

This worked! Thanks so much!

 

[Galaxiom]

I assume you have no future complaints--nothing from 2023. If that's the case this will work:

Year([YourField]) >= (Year(Date())-2)

Not a good query at all because it relies on applying a function to every record before performing the select.

moke123 shows the correct way to structure the WHERE clause. This is a Sargable query that can use the index on the date field.

how about something like

> dateserial(year(date)-2,1,1)

 

[Pat Hartman]

In case anyone wants to understand Sargable.

How to use sargable expressions in T-SQL queries; performance advantages and examples

One of the main tasks of a SQL Server database admins is performance tuning. Developers don’t always prioritize database performance or query optimization.

www.sqlshack.com