Get TOP 1 row from multiple tables based on date

I have five tables in a SQL Server database. These tables are listed below and I want to select Data from these tables according to date. I tried searching but could not find solution for multiple tables. Please help

TABLE1

id          PId          CId
----------- ---------- ------------
1           P001          1
2           P002          2
3           P003          4
4           P004          5

TABLE2

id          CId            CNo          ConId
----------- ---------- ------------  ----------
1            1             1            C123 
2            1             2            PA444
3            1             3            PA456
4            2             1            AUX2398
5            2             2            AUX2345
6            4             1            PA123
7            5             1            C234

TABLE3

id          CId            CNo          Label       Date
----------- ---------- ------------  ----------  ----------
1            1             1            A         1/1/2000
2            1             2            A         15/10/2020
3            1             3            A         20/10/2020
4            2             1            A         15/10/2020
5            2             2            A         20/10/2020
6            4             1            A         20/10/2020
7            5             1            A         27/10/2020

TABLE4

id          CId            CNo          Label       Date
----------- ---------- ------------  ----------  ----------
1            1             1            B         20/10/2020
2            1             2            B         27/10/2020
3            1             3            B         22/10/2020
4            2             1            B         22/10/2020
5            2             2            B         26/10/2020
6            4             1            B         22/10/2020
7            5             1            B         30/10/2020

TABLE5

id          CId            CNo          Label       Date
----------- ---------- ------------  ----------  ----------
1            1             1            C         26/10/2020
2            1             2            C         1/1/2000
3            1             3            C         23/10/2020
4            2             1            C         25/10/2020
5            2             2            C         30/10/2020
6            4             1            C         25/10/2020
7            5             1            C         1/1/2000

I want to select Label and Date from Table 3, 4 and 5 where Date is >1/1/2000 and < than and close to 24/10/2020 and grouped according to PId, CId, ConId and CNo.

Desired result:

PId          CId       ConId      CNo       Label       Date
-------- ---------- ----------  --------  ---------  ----------
P001          1        C123        1          B         20/10/2020
P001          1        PA444       2          A         15/10/2020
P001          1        PA456       3          C         23/10/2020
P002          2        AUX2398     1          B         22/10/2020
P002          2        AUX2345     2          A         20/10/2020
P003          4        PA123       1          B         22/10/2020
P004          5        C234        1          -         -

Any help will be appreciated. Thank you.

Answer

You can achieve this with a couple of CTE’s; the first forms a UNION of TABLE3, TABLE4 and TABLE5; the second generates a row number based on the Date descending for each partition of PId, CId, ConId and CNo. We then select all rows from the second CTE where the row number is 1:

WITH CTE AS (
  SELECT * FROM Table3 WHERE date > '2000-01-01'
  UNION ALL
  SELECT * FROM Table4 WHERE date > '2000-01-01'
  UNION ALL 
  SELECT * FROM Table5 WHERE date > '2000-01-01'
),
CTE2 AS (
  SELECT t1.PId, t1.CId, t2.ConId, t2.CNo, CTE.Label, CTE.Date,
         ROW_NUMBER() OVER (PARTITION BY t1.PId, t1.CId, t2.ConId, t2.CNo ORDER BY CTE.Date DESC) AS rn
  FROM TABLE1 t1
  JOIN TABLE2 t2 ON t2.CId = t1.CId
  LEFT JOIN CTE ON CTE.Cid = t2.CId AND CTE.CNo = t2.CNo AND CTE.Date < '2020-10-24'
)
SELECT PId, CId, ConId, CNo, Label, Date
FROM CTE2
WHERE rn = 1
ORDER BY PId, CId, CNo

Output:

PId     CId     ConId       CNo     Label   Date
P001    1       C123        1       B       2020-10-20
P001    1       PA444       2       A       2020-10-15
P001    1       PA456       3       C       2020-10-23
P002    2       AUX2398     1       B       2020-10-22
P002    2       AUX2345     2       A       2020-10-20
P003    4       PA123       1       B       2020-10-22
P004    5       C234        1       -       -

Demo on dbfiddle