INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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

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

(OP)
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!

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

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.

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

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/crystal-reports/blog/...

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close