CODEWITHSUNDEEP
sql-serverdatabaset-sql
liampboyle
liampboyle

Reputation: 3

T-SQL find all records in a group with a max last modified date beyond a specific threshold

I have a Database table that has all the information I need arranged like so:

Inventory_ID | Dealer_ID | LastModifiedDate

Each Dealer_ID is attached to multiple Inventory_ID's. What I need is a query that calculates the Max Value LastModifiedDate for each dealer ID and then gives me a list of all the Dealer_ID's that have a last modified date beyond the last 30 days.

Getting The max last modified date for each Dealer_ID is simple, of course:

Select Dealer_ID, Max(LastModifiedDate)as MostRecentUpdate

from Inventory group by Dealer_ID order by MAX(LastModifiedDate)

The condition for records older than 30 day is also fairly simple:

LastModifiedDate < getdate() - 30

Somehow, I just can't figure out a way to combine the two that works properly.

Upvotes: 0

Views: 55

Answers (2)

Paresh J
Paresh J

Reputation: 2419

Check this query:

Select DT.DealerID, DT.MostRecentUpdate
(Select DealerID, Max(LastModifiedDate)as MostRecentUpdate
From YourTable
Group BY DealerID) DT
where DT.MostRecentUpdate < GETDATE() - 30

Upvotes: 0

owczarek
owczarek

Reputation: 347

Use HAVING:

Select Dealer_ID, Max(LastModifiedDate)as MostRecentUpdate
from Inventory 
group by Dealer_ID 
having LastModifiedDate < getdate() - 30
order by MAX(LastModifiedDate)

Upvotes: 2

Related Questions