Last row in an history table

[Rosanero4Ever]

Hi all,

I'm a newbie in the Cognos world.
I have an history table where for each person exist some rows, for example:

Name	LastName	Job	Start_date	Finish_date
Robert	De Niro	Actor	01/01/2010	01/01/2011
Robert	De Niro	Regist	01/01/2011	01/01/2012
Roberto	Baggio	Football player	01/01/1989	01/01/2001
Roberto	Baggio	Coach	01/01/2004	01/01/2012

Now i would create a query subject to find the last job of Robert De Niro and Roberto Baggio.

Does anybody can help me to solve this problem? Thanks in advance

[CognosPaul]

This is less of a Cognos question and more of a general design question.

The type of table you've described is a SCD Type 2. That type is very good at finding the state of a certain element on a specific date (today for example) or against a range of dates, but it's entirely unsuited for finding the last state of the element. In terms of the query, you would need complex logic (probably in the terms of a self join) to get it.

The correct way of dealing with this is to modify the table to add a last and current state flags. This would allow you to do a simple select * from history where last = 1. The change should be done in the ETL level.

If that's not possible, then you'll need to do something like this.
Create a new data source query called EmpLastDate with:

Code Select Expand

select id, max(finish_date) last_date from history group by id It might be better to make that a view in the database to give the db optimizer a better chance to work with it.
Join that table on the history table on history.id = emplastdate.id and history.finish_date=emplastdate.last_date
In the business layer create a filter that has [EmpLastDate].[id] = [EmpLastDate].[id].
When you drag that filter into the report, it will force the join to the EmpLastDate table, thus filtering the data that you need.

[Rosanero4Ever]

Hi,
above all thanks for your reply.
I can't use an ETL practice because I need to execute a query in real time on main data source.
Have you any other idea?
Thanks again

[CognosPaul]

How is the main data source populated? It should be a trivial matter to modify the process to add flag fields.

If the record is being closed the update could be:
update history set finish_date = ? and last_flag = 1 and current_flag = 0 where record_id = ?

If a new record is being inserted, you just need to do a
update history set last_flag = 0 where emp_id=?;
and add last_flag =1 and current_flag = 1 to the insert statement.

Again, it is possible without modifying the source system, with the SQL I posted above. But that will be significantly slower than the correct way of doing it.