Records added since specific month this year in SQL

user_14342019 0
Viewed 49

I have a table which contains the following data

userId | name   |   from       
1      | aaa    |  2020-09-23  
2      | bbb    |  2020-09-01  
3      | ccc    |  2019-05-12  
4      | ddd    |  2019-06-01  
5      | eee    |  2018-06-23  
6      | fff    |  2018-07-23

It is for an educational purpose and therefore the year runs from September to August rather than January to December. How do I output all of the users who were added since the previous September (so if it's October 2020 then 1 month ago, if it's January 2021 then 4 months ago)? The query has to be relative so that it always outputs the previous September to the time that the query is run rather than a specific September.

The result of the query should be

bbb 2020-09-01 
aaa 2020-09-23
mysql sql
2 Answers

You can subtract 9 months and compare to the year:

where year(`from` - interval 9 month) = year(curdate() - interval 9 month)

Actually, you might want year(curdate()) +/- 1 depending on how you are identifying the year.

user_11440350

With NOT EXISTS:

select t.* from tablename t
where t.`from` >= concat(year(current_date), '-09-01') 
and not exists (
  select 1 from tablename
  where name = t.name
    and `from` between 
               concat(year(current_date), '-09-01') - interval 1 year 
               and 
               concat(year(current_date), '-09-01') - interval 1 day
)

Or maybe:

select t.* from tablename t
where t.`from` >= concat(year(current_date) - (month(current_date) < 9), '-09-01') 
and not exists (
  select 1 from tablename
  where name = t.name
    and `from` between 
               concat(year(current_date) - (month(current_date) < 9), '-09-01') - interval 1 year 
               and 
               concat(year(current_date) - (month(current_date) < 9), '-09-01') - interval 1 day
)

so if you execute the query in say March 2021, you will get the correct results that compare the current educational year with the previous one.

See the demo.
Results:

> userId | name | from      
> -----: | :--- | :---------
>      1 | aaa  | 2020-09-23
>      2 | bbb  | 2020-09-01
user_104988280
Related