Using a Package: a way to get just one current most record

[hhavel]

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

[AnalyticsWithJay]

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.

[hespora]

or, build a filter with

Code Select Expand


[bene_reg] = maximum([bene_reg] for [personsID])

and set that filter to "After Auto Aggregation".

[hhavel]

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

[hhavel]

I got both to work.

Thank you for your help!

Havel

[hespora]

Glad I/we could help. ;)