CODEWITHSUNDEEP
sqlsql-servert-sql
Guitambau
Guitambau

Reputation: 11

SQL return the last date

i need to return the last date after 11 months this date enter in my sql. But if i have the same name i should return this name with the most recently date

Ex

Name Date
John 03/17/21
Max 02/19/21
John 02/19/21

My code:

select * 
from TAB_ATESTADO_DE_SAUDE_OCUPACIONAL 
where datediff (month, DATE, '02/17/2022') >= 11

And return this for me

Name Date
John 03/17/21
Max 02/19/21
John 02/19/21

I need to return this:

Name Date
John 03/17/21
Max 02/19/21

Upvotes: 0

Views: 59

Answers (2)

Hubert
Hubert

Reputation: 131

This should do the job:

select name, max(date) as 'Last Date' 
from TAB_ATESTADO_DE_SAUDE_OCUPACIONAL
group by name
having DATEDIFF(month,max(LoadedDate),GETDATE()) >= 11

For each name you are displaying the latest date, at than at end you are filtering only the records where the difference between the max(date) and now is higher than 11.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Are you trying to combine filtering with aggregation?

select name, max(date)
from TAB_ATESTADO_DE_SAUDE_OCUPACIONAL
where datediff(month, DATE, '2022-02-17') >= 11
group by name;

Note: I would recommend writing the where clause as:

where date <= dateadd(month, -11, '2022-02-17')

Upvotes: 2

Related Questions