How to fetch data from different tables with current date condition?

Question

I have a table called WB.Email, WB.DATA and WB.APRIOD. I have to fetch the data from WB.Email condition is: where current date = display date -15 days ;

To find the display date use table WB.DATA, select the date column where valid = 'Y' with the other condition (select PERIOD FROM WB.APRIOD WHERE VALID ='Y')

CVALID = 'V' and current date = session start date - 15 days

What would be the final query ?

WB.Email :

PERIOD	HSID	CVALID
2022-09-15	184	V
2022-09-15	184	V
2022-09-15	183	V
2022-09-15	183	V

WB.DATA table

PERIOD	HSID	SDATE	START
2022-09-15	184	2022-03-11	N
2022-09-15	184	2022-03-10	Y
2022-09-15	183	2022-01-06	N
2022-09-15	183	2022-01-05	Y

WB.APRIOD:

PERIOD	VALID
2022-09-15	Y
2021-09-15	N
2020-09-15	N
2029-09-15	N

Expected Result: for example current date is 24-02-22 and session start date i.e SDATE column in WB.DATA table is 2022-03-10 then result should come like below table.

PERIOD	HSID	CVALID
2022-09-15	184	V
2022-09-15	184	V

Answer 1

use this

SELECT * FROM WB.EMAIL E
LEFT JOIN WB.DATA D ON E.PERIOD = D.PERIOD
LEFT JOIN WB.APRIOD A ON D.PERIOD = A.PERIOD
WHERE E.CVALID = 'V'
AND D.START = 'Y'
AND GETDATE() = DATEADD(DAY,-15,D.SDATE)

here result show only Email table if you want diff column then right down your column name.