CODEWITHSUNDEEP
sqlfiltergoogle-bigquerywhere-clause
Daryl Robins
Daryl Robins

Reputation: 21

SQL - Filter on the latest date row

I've got a table as below

ID Name Won? CloseDate
011 Forum Renewal No 01/08/21
011 Forum Renewal Yes 01/09/21
011 New Business No 01/02/22
012 CX & FM Renewal Yes 01/03/21
012 CX & FM Renewal No 01/08/22

I want to be able to pull out only the Renewal lines but only if it doesn't have another Renewal line with the same ID on a date after it. So the above example would pull nothing through for 011 but something for 022

I've got the below code but no idea where to bring in the filter

where 
SO1.CloseDate > '2021-01-01'
and (SRT.Name = 'Forum Renewal' OR SRT.Name = 'CX & FM Renewal')

Upvotes: 0

Views: 646

Answers (1)

NickW
NickW

Reputation: 9768

Something like this should work:

SELECT T1.*
FROM SRT T1
WHERE (T1.Name = 'Forum Renewal' OR T1.Name = 'CX & FM Renewal')
AND T1.CloseDate = (
    SELECT MAX(T2.CloseDate)
    FROM SRT T2
    WHERE T1.ID = T2.ID
    AND (T2.Name = 'Forum Renewal' OR T2.Name = 'CX & FM Renewal')
    )

Upvotes: 1

Related Questions