Include person_id if previous record start date is more than 3 years from specific start date/term

[borisbe]

I hope I can word this so it makes sense and I didn't forget anything.

person_id start_date term class
1 10/23/2012 1128 test
1 05/07/2013 1136 life
1 07/20/2013 1136 life
1 10/07/2014 1148 test
1 01/26/2015 1154 test

2 05/24/2011 1116 life
2 06/20/2015 1156 life
2 11/05/2015 1158 life

3 04/30/2014 1144 life
3 06/11/2015 1156 life

1. I need to look at person_ids where the terms are greater or equal to 1136
2. Take the minimum start date in each term
3. If the previous record's start date is one more than 3 years from that minimum start date then include.

For the above records, these are the person_ids that would be included:

person_id start_date term class
2 05/24/2011 1116 life
2 06/20/2015 1156 life - 1) term is greater or equal to 1136 2) previous record's start date 05/24/2011 is more than 3 years when you minus 3 years from this class's start date 06/20/2015 which is 06/20/2012
2 11/05/2015 1158 life


3 04/30/2014 1144 life - term is greater or equal to 1144 and there are no previous records so include.
3 06/11/2015 1156 life

For the above records, these are the person_ids that would be NOT be included:

1 10/23/2012 1128 test
1 05/07/2013 1136 life- 1) term is greater or equal to 1136 2) minimum start date in this term is 05/07/2013 3) previous record's start date is 10/23/2012 so this is NOT more than 3 years when you minus 3 years from this class's start date 05/07/2013 which is 05/07/2010
1 07/20/2013 1136 life
1 10/07/2014 1148 test
1 01/26/2015 1154 test

If I can't do greater or equal to 1136 in one run then I can do one term at a time. So just look at person_ids in 1136, evaluate if they should be included or not and get results. Run report again but look at person_ids in 1138, evaluate if the should be included or not then get results and so on.....

I'd really appreciate help with this report.

Thanks

 

[LaurieHamlin]

In the Select Expert click on Show Formula. There type in
term >= 1136 Or DateAdd ("yyyy", -3, start_date)

 

[borisbe]

Hi Laurie,

I might be doing something wrong or it might be my version of Crystal (version 9).

I have a person_id and start date group. I've selected "Select Expert" on the start date group but I don't see a "Show Formula" option.

Thanks for your help!

 

[LaurieHamlin]

In the Select Expert try clicking on the term field. Then choose "is greater than or equal to and type in 1136. Then it should allow you to click on Show Formula. It's been a long time since I used v 9, but getting into the Select Expert is half the battle.

 

[hilfy]

This is one that you may not be able to do just by using the Select Expert.

What type of database are you using? How good are your SQL skills? I think you probably need to use a Command (SQL Select statement) for this.

If you're using MS SQL Server, your query might look something like this:

With people as ( --get the list of people who have terms >= 1136
Select person_id, min(start_date) as first_date
from MyTable
where term >= 1136
group by person_id
},

dates as ( --get the record just prior to the one where term >= 1136
Select mt.person_id, max(mt.start_date) as last_date
from MyTable as mt
inner join people as p
on mt.person_id = p.person_id
and mt.start_date < p.first_date
group by mt.person_id
)

Select
mt.person_id,
mt.start_date,
mt.term,
mt.class
from MyTable as mt -- this gets us all of the records
inner join people p -- for the people who are in the list with term >= 1136
on mt.person_id = p.person_id
left outer join dates d -- include the most recent record prior to the one in the people query
on p.person_id = d.person_id
where (d.person_id is null or
DateDiff(year, d.last_date, p.first_date) >= 3) --either no prior records or prior record is more than 3 years old

If you're interested in learning to work with commands, see my blog post here:

http://scn.sap.com/community/crysta...ices-when-using-commands-with-crystal-reports

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com