Return Vales when

I'm stuck here, guys.

I have two tables, each having a date column. One table is using timestamp (2016-08-05 09:16:11.000) the other has timestamp but isn't using it (2016-08-05 00:00:00.000)

I want to join these tables and return the MAX date from each table but ONLY when the MAX dates match. If one column has a more recent date, display the results when both columns had a matching max date.

I'm trying MAX and CAST(to remove timestamp)but I can only pull back the MAX date from each table. So it's not showing me the same dates when both tables don't have the same max date.

Here's my example

SELECT A.ID,
CAST(A.DATE as date),
CAST(B.DATE as date),
B.NUMBER,
A.VALUE,

FROM TABLE1 A
JOIN TABLE 2 B
ON A.ID = B.ID

Where b.DATE=(SELECT MAX(DATE)
FROM TABLE B
WHERE ID = b.ID) 

Where A.DATE=(SELECT MAX(DATE)
FROM TABLE A
WHERE ID = b.ID)

I want my results to be something like this:

ID | (no column name)| (no column name) | Number| Value |
1  | 2016-08-04      | 2016-08-04       | 42    | 77    | 
2  | 2016-08-04      | 2016-08-04       | 43    | 40    | 
3  | 2016-08-04      | 2016-08-04       | 44    | 42    |

But when they don't have the same max date, I get this

ID | (no column name)| (no column name) | Number| Value |
1  | 2016-08-04      | 2016-08-05       | 42    | 47    | 
2  | 2016-08-04      | 2016-08-05       | 43    | 43    | 
3  | 2016-08-04      | 2016-08-05       | 44    | 44    |

Hopefully I made myself clear. Any help will be appreciated.

Solution

Here's one option using row_number:

SELECT * 
FROM (
    SELECT A.ID,
           CAST(A.DATE as date),
           CAST(B.DATE as date),
           B.NUMBER,
           A.VALUE,
           ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY A.Date DESC) rn
    FROM TABLE1 A 
           JOIN TABLE 2 B ON A.ID = B.ID
                        AND CAST(A.DATE as date) = CAST(B.DATE as date)
) t
WHERE rn = 1