I am using a relational database package (for SunGard's OneSolution if you care.) and I'm attempting to list some employee information from another table and get an employee's benefit packages, but only the current most.
Here's some data as an example (I'd only want this person's info for the third record, starting on 2016-11-01):
personsID bene_beg bene_end bene_plan
XXXX 2016-09-19 2016-10-31 EEHCPX
XXXX 2016-01-01 2016-08-30 EEHCPX
XXXX 2016-11-01 2016-12-31 EEHCPX
The benefit table (data above) is a child table to employee table.
In straight sql I'd use top (1) and sort on bene_beg. Is there a way to do this is straight Cognos?
Thank you in advance.
Havel
Create a new data item called [Rank] and use the Rank() function (available both in Cognos and SQL), partitioning by personsID, ordering by the date DESC. Add a filter to your query on [Rank] = 1.
or, build a filter with
[bene_reg] = maximum([bene_reg] for [personsID])
and set that filter to "After Auto Aggregation".
Exciting! two possible ways to do this.
I'll give them both a try and let ya'll know how it goes.
Thank you!
Havel
I got both to work.
Thank you for your help!
Havel
Glad I/we could help. ;)