Check the first value according to first date

Question

I have two tables

 guid | id   | Status
 -----| -----| ----------
 1    | 123  | 0
 2    | 456  | 3
 3    | 789  | 0

The other table is

 id   | modified date | Status
------| --------------| ----------
1     | 26-08-2017    | 3
1     | 27-08-2017    | 0
1     | 01-09-2017    | 0
1     | 02-09-2017    | 0
2     | 26-08-2017    | 3
2     | 01-09-2017    | 0
2     | 02-09-2017    | 3
3     | 01-09-2017    | 0
3     | 02-09-2017    | 3
3     | 03-09-2017    | 0

Every time the status in the first table changes for each id it also modifies date and status in second table.Like for id 1 status was changed 4 times.

I want to select those ids by joining both tables whose value of status was 0 in its first modified date. In this example it should return only id 3 because only id 3 has a status value as 0 on it first modified date 01-09-2017.Ids 1& 2 have value 3 in their first modified date.

Any help

Answer 1

Try using below(Assuming first table as A and second table as B):

;with cte as (
Select a.id, b.Status, row_number() over(partition by a.id order by [modified date] asc) row_num 
from A 
inner join B
 on a.id = b.id
)
Select * from cte where 
status = 0 and row_num = 1

Answer 2

Think this will do what your looking for.

WITH    cte
      AS (SELECT    id
          ,         ROW_NUMBER() OVER (PARTITION BY (id) ORDER BY [modified date]) RN
          ,         Status
          FROM      SecondTable
         )
SELECT  *
FROM    FirstTable
JOIN    cte ON FirstTable.id = cte.id
               AND RN = 1
WHERE   cte.Status = 0

Just expand out the * and return what fields you need.