Hey all,
I've got some account level data I'm pulling in from Redshift, and I'm having trouble getting exactly what I need. There's some "interesting" record keeping in this database, so that makes it a bit more complicated.
Basically I've got fields for mortality count and lapse count, which denote if the policy died or lapsed respectively. A 1 means death or lapse, 0 means inforce, and -1 means a previous death or lapse record was reversed. I've got another field called type_of_record, and the only values we care about here are L, R, D, and null. L means lapse, R means reinstated, D means death, and null means inforce.
Lapses: It's possible for a policy to lapse and then be reinstated, so if there's an L on 5/20/2013 and an R on 5/25/2013, then I don't want either of those records. However, if there's an L and no R, then I want the valdate of the most recent lapse.
Deaths: Similar to lapses, if there's a death and no reversal of that death, then I want the valdate of the last recorded death. The part that is confusing me is how to grab only the death record if there was a previous lapse. Right now my workflow is grabbing the most recent lapse and the last recorded death, but if they died, I only want the death record.
Inforce: If there are no recorded lapses or deaths (or they were reversed out of), then I want the most recent valdate where the type_of_record field is null (or empty).
Any help on this would be greatly appreciated!
Edit: Added a record ID to the original dataset and the desired output.
