Test study year and policy year mortality data long format

test.sql

drop table if exists mortality.sample_exposure_and_events;
create table mortality.sample_exposure_and_events ( policy_id int, dob_date date, start_date date, end_date date, event_date date, covariate_1 varchar(1), covariate_2 varchar(1) );
insert into mortality.sample_exposure_and_events ( policy_id, dob_date, start_date, end_date, event_date, covariate_1, covariate_2 ) ( select 1, '1989-07-25', '2010-01-01', '2012-12-31', NULL, 'M', '5');
insert into mortality.sample_exposure_and_events ( policy_id, dob_date, start_date, end_date, event_date, covariate_1, covariate_2 ) ( select 2, '1990-08-26', '2012-01-01', '2013-12-31', NULL, 'M', '3');
insert into mortality.sample_exposure_and_events ( policy_id, dob_date, start_date, end_date, event_date, covariate_1, covariate_2 ) ( select 3, '1991-08-19', '2014-01-01', '2016-06-30', '2016-06-30', 'F', '3');

select * from mortality.sample_exposure_and_events order by policy_id;

create table mortality.sample_study_years ( study_year int );
insert into mortality.sample_study_years ( study_year ) ( select 2009 );
insert into mortality.sample_study_years ( study_year ) ( select 2010 );
insert into mortality.sample_study_years ( study_year ) ( select 2011 );
insert into mortality.sample_study_years ( study_year ) ( select 2012 );
insert into mortality.sample_study_years ( study_year ) ( select 2013 );
insert into mortality.sample_study_years ( study_year ) ( select 2014 );
insert into mortality.sample_study_years ( study_year ) ( select 2015 );

select * from mortality.sample_study_years order by 1;

select ee.*, sy.study_year  from mortality.sample_exposure_and_events ee inner join mortality.sample_study_years sy on true;

select ee.*, sy.study_year  from mortality.sample_exposure_and_events ee inner join mortality.sample_study_years sy on year(ee.start_date) <= study_year and year(ee.end_date) >= study_year order by sy.study_year;

create table mortality.sample_policy_years ( policy_year int );
insert into mortality.sample_policy_years ( policy_year ) ( select 1 );
insert into mortality.sample_policy_years ( policy_year ) ( select 2 );
insert into mortality.sample_policy_years ( policy_year ) ( select 3 );
insert into mortality.sample_policy_years ( policy_year ) ( select 4 );
insert into mortality.sample_policy_years ( policy_year ) ( select 5 );
insert into mortality.sample_policy_years ( policy_year ) ( select 6 );
insert into mortality.sample_policy_years ( policy_year ) ( select 7 );

select * from mortality.sample_policy_years order by 1;

select ee.*, sy.policy_year  from mortality.sample_exposure_and_events ee inner join mortality.sample_policy_years sy on datediff('year', ee.start_date, ee.end_date) >= sy.policy_year-1 order by policy_id, policy_year;