How To Perform Date Calculations From Different Tables?

Please forgive me if this is a basic question, I'm a beginner in SQL and need some help performing date calculations from 2 tables in SQL. I have two tables (patient and chd) they

Solution 1:

Let me assume that this query gets the date of death from the patient table:

select p.id, min(p.date) as deathdate
from patient p
where p.Alive = 'N'groupby p.id;

Then, you can get what you want with a join:

select count(*)
from chd c join
     (select p.id, min(p.date) as deathdate
      from patient p
      where p.Alive = 'N'groupby p.id
     ) pd
     on c.id = pd.id;

You can then address your questions with a where clause in the outer query. For instance:

where deathdate >=current_date-interval'1 year'