ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anil <anilk...@gmail.com>
Subject Re: Affinity
Date Mon, 02 Jan 2017 12:45:14 GMT
Hi Nikolay,

Thanks for the response. The use case is very simple.

Each Person is associated with number of person details which holds
address, start and end dates.

*Scenario* - Get person information with latest person detail for given
person ids. Person information must include duplicate person with same
equivalent Id

1. To get the duplicate persons with same equivalentid -

join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON group
by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId

2. To get the latest person details - based on recent end date

 join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
  join (select equivalentId, max(enddate) as enddate from
DETAILS_CACHE.PersonDetail  group by equivalentId) maxPd on p.equivalentId
= maxPd.equivalentId and maxPd.endDate = pd.endDate

To support group sorted index, i have created index on equivalentId in
DETAILS_CACHE.

Please let me know if you have any questions.
Thanks

Mime
View raw message