Heracles 109 Number of persons with continuous observation in each year

[gowthamrao]

WebAPI/src/main/resources/resources/cohortanalysis/sql/runHeraclesAnalyses.sql

Line 644 in c92863d

DATEFROMPARTS(YEAR(observation_period_start_date),1,1) as obs_year_start,

-- 109 Number of persons with continuous observation in each year

What we want is all the possible calendar years between min(observation_period_start_date) and max(observation_period_end_date) for the population in cohort.

All this SQL seems to be doing is

  SELECT DISTINCT 
  YEAR(observation_period_start_date) AS obs_year,
	DATEFROMPARTS(YEAR(observation_period_start_date),1,1) as obs_year_start,
	DATEFROMPARTS(YEAR(observation_period_start_date),12,31) as obs_year_end
  INTO
  #temp_dates_1
  from @CDM_schema.PERSON p1
  inner join (select subject_id, cohort_definition_id as cohort_definition_id from @results_schema.COHORT where cohort_definition_id in (@cohort_definition_id)) c1
  on p1.person_id = c1.subject_id
  inner join
  @CDM_schema.observation_period op1
  on p1.person_id = op1.person_id
  ;

creating a list of unique calendar years corresponding to observation_period_start_date for any person in the cohort. This approach will skip some calendar years, if the observation_periods pans across years.

One approach is to do something like this for all dates between min(observation_period_start_date) and max(observation_period_start_date)

https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers
where the low number is min(year(observation_period_start_date)) and high number is max(year(observation_period_start_date))

Same applies to Heracles 110, 116, 117,