Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#2564 closed defect (fixed)

large jump in Frontiers patient numbers between Beaver and Lovewell

Reported by: tmcmahon Owned by: ngraham
Priority: major Milestone: heron-arkansas-update
Component: data-repository Keywords: public-web
Cc: Blocked By: #2569
Blocking: #2593 Sensitive: no


# of Frontiers patients jumped around 4.5 thousand between the Beaver and Lovewell releases. Previously it increases around 1,000-2,000 patients between releases on average.

Could this be related to #2451? Or was the Frontiers team that fast at entering forms last month? Inquiry comes from Jo Denton.

Change History (6)

comment:1 Changed 7 years ago by ngraham

It looks like the "normal" increase is around 600 to 700 per month. This month, we saw an increase over 4000. The start dates for these facts (derived from the service date in idx) are spread out quite a bit so it's not obvious to me that we just somehow skipped a monthly update here and there.

I've e-mailed Brandon Haight to get his opinion.


Looking back at previous releases, it seems that the number jumps between 600-700 or so:

Milford20077 (up 659)
Solomon19418 (up 623)
Chikaskia18795 (up 718)
Crooked Creek18077

This time, we did indeed jump by 4570

select count(*) from (
  select b.patient_num
  from blueherondata.observation_fact b
  where b.concept_cd = 'HICTR_PARTICIPANT:yes'
  )--26011 on A (Januaray data) and 21441 on B (December data) [delta 4570]

Since we update IDX each month with an incremental update, I wondered if we accidentally skipped a few months? If so, the start dates for the facts might be clustered around a few months perhaps?

-- For speed
create table frontiers_observations as (
  select patient_ide, start_date
  from observation_fact_hictr);

this_month_ides as (
  select pm.patient_ide
  from blueherondata.observation_fact@deid b
  join nightherondata.patient_mapping pm on pm.patient_num = b.patient_num
  where b.concept_cd = 'HICTR_PARTICIPANT:yes'
last_month_ides as (
  select pm.patient_ide
  from blueherondata.observation_fact@deid_b2 b
  join nightherondata.patient_mapping@id_b2 pm on pm.patient_num = b.patient_num
  where b.concept_cd = 'HICTR_PARTICIPANT:yes'
new_ides as (
  select t.patient_ide from this_month_ides t
  left join last_month_ides l on t.patient_ide = l.patient_ide
  where l.patient_ide is null
select count(*) cnt, y, m from (
  select extract(year from o.start_date) y, extract(month from o.start_date) m 
  from new_ides n
  join frontiers_observations o on o.patient_ide = ltrim(n.patient_ide, '0')
group by y, m
order by cnt desc

Top 10 by count


I wonder if the IDX system updates old records and therefore our monthly update process misses them? Or, perhaps we have a bug elsewhere? Maybe there are gaps between monthly updates?

I've e-mailed Brandon with these questions.

From: Nathan Graham <>
To: Brandon Haight <>
Subject: IDX Updates
Date: Thu, 27 Feb 2014 11:15:20 -0600

comment:2 Changed 7 years ago by ngraham

Blocked By: 2569 added

comment:3 Changed 7 years ago by dconnolly

Blocking: 2593 added

comment:4 Changed 7 years ago by ngraham

Resolution: fixed
Status: newclosed

I looked at the Frontiers flag and found 26,568 with February data. For the release last month (Beaver), we had 26,011 so the increase is 557 which I think is reasonable given increases of previous months (comment:1).

So, I think that we're reasonably confident that the Frontiers flag is being interpreted properly and that the jump in Frontiers participants was because we were missing them before due to incremental updates (#2569).

comment:5 Changed 7 years ago by mhoag

Keywords: public-web added

comment:6 Changed 7 years ago by ngraham

Sensitive: unset
Note: See TracTickets for help on using tickets.