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.
Advertisement
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 - -