CODEWITHSUNDEEP
sqlsql-servert-sqldate
kazzi
kazzi

Reputation: 534

Join subquery and pull most recent date

I have two tables:

CLIENTS

ID   NAME
001  John
002  Sara

CLIENT_STATUS

CLIENT_ID   STATUS   DATE
001         3        2018-01-02
001         2        2018-01-04
002         2        2018-01-02
002         1        2018-01-03

I want to filter by status = 1 and I just want the most recent date within the specified time frame.

I have this so far:

DECLARE 
    @StartDate  DATE,
    @EndDate    DATE
SET @StartDate  = '2016-07-01'    
SET @EndDate    = '2018-06-30'    

SELECT 
   c.NAME
 , c.ID
 , cs.STATUS
FROM CLIENT c
LEFT JOIN (
    SELECT cs.CLIENT_ID, cs.DATE 
    FROM CLIENT_STATUS 
    WHERE STATUS = 1 AND h.DATE BETWEEN @StartDate AND @EndDate
) AS hst ON hst.CLIENT_ID = c.ID

Only that doesn't Order by the most recent date.

Please note this is part of a larger query.

Upvotes: 1

Views: 517

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

Left (or INNER) join with a sub-query that has row numbers:

SELECT *
FROM CLIENTS
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CLIENT_ID ORDER BY DATE DESC) AS rn
    FROM CLIENT_STATUS
    WHERE STATUS = 1
) RECENT_STATUS ON CLIENTS.ID = RECENT_STATUS.CLIENT_ID AND RECENT_STATUS.rn = 1

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() function :

SELECT TOP (1) WITH TIES c.ID, c.NAME, cs.DATE
FROM CLIENT c INNER JOIN
     CLIENT_STATUS cs
     ON cs.CLIENT_ID = c.ID 
WHERE cs.STATUS = 1 AND cs.DATE >= @StartDate AND cs.DATE <= @EndDate
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.ID ORDER BY cs.DATE DESC);

Upvotes: 1

Related Questions