WebCOBRA.com Community Message Boards

kippfeldt
Posts: 18
Since: 02/08/2007

Hello,

I am trying to create a report with the Relational DB data for QB Status. I can easily get to the QB status information but it only looks like it is for their current status.

Basically, I need to be able to enter a date range (04/01/2008 - 04/30/2008) and pull back the QB information (Basic info, qualifying event date, coverage begin date, QB status, etc...) at that point in time even though today they could be terminated.

Is this data available? Any help would be greatly appreciated.

Thanks,
Kipp

jwolgamott
Posts: 257
Since: 09/26/2005

Kipp --

There's a new table in the RelationalAPI database called QBGroupStatusHistory. This will track the information you're searching for for anyone who was enrolled or pending on/after 5/25/2008 (when the latest release was installed).

We just added this functionality to accomplish exactly what you were requesting.

select companies.companyname, qbgroups.description, qbgroups.nickname, qb.firstname, qb.lastname from QBGroupStatusHistory
inner join qbgroups on qbgroups.qbgroupid = QBGroupStatusHistory.qbgroupid
inner join qb on qb.qbgroupid = qbgroups.qbgroupid
inner join companies on companies.companyid = qbgroups.companyid
where
QBGroupStatusHistory.qbgroupstatusi d in (1,2,3)
and begindate <= '04/30/08'
and enddate >= '04/01/08'

order by companies.companyname, qbgroups.nickname, qbgroups.description

kippfeldt
Posts: 18
Since: 02/08/2007

Great, this should help.

So, just to make sure I understand correctly there will be no history within this table prior to 5/25/2008. Such as I won't be able to look for history for May of last year (2007)?

Thanks,
Kipp

jwolgamott
Posts: 257
Since: 09/26/2005

Kipp--

Well, they'll be history for May 07, only of the people enrolled as of 5/25/08.

I think it's important to have history of terminated participants, so we're currently looking into running the process on terminated records to get their history.

I'll post here when we have some findings within the next day or so.

jwolgamott
Posts: 257
Since: 09/26/2005

Kipp --

If you get your Daily RelationalAPI tomorrow (May 31), you'll be able to backtrace status changes for QBs throughout history.

This should be exactly what you're looking for, please let us know if it helps.

What we did was create a onetime process that created the timeline for all terminated QBs in the system; that process is running right now and will complete before the end of the night.