I am relatively new to cognos, but I am familiar with the maximum function in trying to identify the most recent date of a client's record. For instance;
maximum ([Date] for [Client Account])
Is there a similar function (or a way to get) the two most recent dates in a relational database?
The data look like;
Client Account Date
1234 1/12/2019
1234 1/15/2019
1234 2/21/2019
1235 1/11/2019
1235 1/14/2019
1235 1/21/2019
So, for Client Accounts 1234 and 1235, I only want to select the two most recent dates (1/12/2019 and 1/15/2019 for client 1234 and 1/11/2019 and 1/14/2019 for client 1235)
Any help would be appreciated.
I'm not sure that I've used it with dates, but you might be able to do something with the rank function. So something like:
rank([Date] for [Client Account]) <= 2
Thanks, BigChris. The rank function is new to me. It works great! If I include the <=2, it returns a '1' for the last two dates and a '0' for everything else. If I leave off the <=2, it provides a nice ranking of the dates, '1', '2', '3'...for the most recent date, the 2nd most recent date, and the 3rd most recent date, etc.
Thank you!