I have a PostgreSQL query which generates a view that buckets only the first 30 days of data into the view.
CREATE view vet_visits_over_first_30_days AS
(SELECT veterinarian_session.animal_id AS "animal", veterinarian_account.enrollment_date AS "Enrolled", veterinarian_sessionactivity.created_date AS "Admit Date", veterinarian_sessionactivity.detail AS "Reason"
FROM veterinarian_account, veterinarian_sessionactivity, veterinarian_session
WHERE veterinarian_sessionactivity.created_date BETWEEN veterinarian_account.enrollment_date AND veterinarian_account.enrollment_date + '30 days'::INTERVAL AND veterinarian_sessionactivity.activity_sub_id=52 AND veterinarian_sessionactivity.session_id = veterinarian_session.id AND veterinarian_session.animal_id=veterinarian_account.id
ORDER BY veterinarian_session.animal_id);
which will then let me do:
select * from vet_visits_over_first_30_days;
and get back only the visits in the first 30 days.
However, I need to do this generically over a large number of tables which have the same date fields over different time buckets such as 30, 60, 90, 180 days.
I think I could achieve this using SQL metaprogramming and views, but is there a better way using PostgreSQL?