Print Page | Close Window

Find all records 30 days from current date

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8672
Printed Date: 20 Sep 2024 at 3:00pm


Topic: Find all records 30 days from current date
Posted By: phoeneous
Subject: Find all records 30 days from current date
Date Posted: 17 Dec 2009 at 2:37pm
Hello.  I have the following formula below from the select expert but it isn't finished.  What I'm trying to do is find only records that have a comments.create_date that is 30 days greater than the current date.  How do I write that?  Thank you.
 
{cases.closed_ind} = "O" and
{case_pick.group_code} = "CSTATU" and
{case_pick.current_ind} = "Y" and
{comments.create_date} > DateAdd('d',-30 ,Today)
 


Replies:
Posted By: DBlank
Date Posted: 17 Dec 2009 at 6:53pm
You had it looking for days greater than 30 days ago. Just change the (-30) to 30:
{cases.closed_ind} = "O" and
{case_pick.group_code} = "CSTATU" and
{case_pick.current_ind} = "Y" and
{comments.create_date} > DateAdd('d',30 ,currentdate)

Posted By: phoeneous
Date Posted: 18 Dec 2009 at 8:54am
Originally posted by DBlank

You had it looking for days greater than 30 days ago. Just change the (-30) to 30:
{cases.closed_ind} = "O" and
{case_pick.group_code} = "CSTATU" and
{case_pick.current_ind} = "Y" and
{comments.create_date} > DateAdd('d',30 ,currentdate)
 
I've been asked to change the parameters of the report.  Now I need to find anything that has not had a comment in the last 30 days.
 
My guess is something like this but do I need to use maximum so that it only looks at the most recent create_date per case?
 
not({comments.create_date} > DateAdd('d',30 ,Today))

Posted By: DBlank
Date Posted: 18 Dec 2009 at 9:30am

You would have to do that with Grouped information.

Group your data on a field that identifies all records as 1 case (likely a casenumber field) and then in the select expert use click on show formula.
There is a toggle optino between Record Selection and Group Selection.
With Record selection use part of your formula:
{cases.closed_ind} = "O" and
{case_pick.group_code} = "CSTATU" and
{case_pick.current_ind} = "Y"
In the Group Selection add in you rnew criteria:
Maximum({comments.create_date},{table.groupedonfield} < dateadd('d',-30,currentdate)

Posted By: phoeneous
Date Posted: 18 Dec 2009 at 3:08pm
Originally posted by DBlank

You would have to do that with Grouped information.

Group your data on a field that identifies all records as 1 case (likely a casenumber field) and then in the select expert use click on show formula.
There is a toggle optino between Record Selection and Group Selection.
With Record selection use part of your formula:
{cases.closed_ind} = "O" and
{case_pick.group_code} = "CSTATU" and
{case_pick.current_ind} = "Y"
In the Group Selection add in you rnew criteria:
Maximum({comments.create_date},{table.groupedonfield} < dateadd('d',-30,currentdate)
 
This works perfectly!  The only problem that I have now is that it is retrieving all of the comments of each case.  What I would like it to do is only pull the most recent comment date.
 
I found the group expert that lets you choose topN for a specific group.  I chose the top 1 for the comments.create_date group but it still shows all of them.  What could I be doing wrong?

Posted By: DBlank
Date Posted: 18 Dec 2009 at 3:13pm
Don't use TOP N. THat is the top value based on Groups like if you grouped on employees showed their sales and you wanted to see the top 5 performing emplyees not the top sale per employee.
For display purposes just sort by create date descending, create a GH1-b and drop the values there. It will show the first record (most recent because you are doing a descending sort). SUppress the detail section since you do not need it.

Posted By: phoeneous
Date Posted: 18 Dec 2009 at 3:29pm

You lost me there :)

I'm not much of a crystal reports guy as you can tell.  I definitely appreciate all of your help though.
 
Do these screenshots help?
 
 
 

Posted By: DBlank
Date Posted: 18 Dec 2009 at 6:24pm
Move your group 4 name back onto group header 4.
I the create date and the Firstlast fields are what you want to display move them onto GRoup header4 too.
Unsupress GH4
Suppress Details.
 

Posted By: flanman
Date Posted: 18 Dec 2009 at 7:23pm
in your select criteria why not change
{comments.create_date} > DateAdd('d',-30 ,Today)
to
comments.create_date >  CurrentDate()-30
I use this all the time for date ranges. You will always get 30 days back from any current date.

Posted By: phoeneous
Date Posted: 28 Dec 2009 at 10:37am
Originally posted by flanman

in your select criteria why not change
{comments.create_date} > DateAdd('d',-30 ,Today)
to
comments.create_date >  CurrentDate()-30
I use this all the time for date ranges. You will always get 30 days back from any current date.
 
Since Im only looking for the most recent comment, should I use:
 
Maximum({comments.create_date},{cases.style}) > CurrentDate()-30
 
?

Posted By: phoeneous
Date Posted: 28 Dec 2009 at 10:38am
Originally posted by DBlank

Move your group 4 name back onto group header 4.
I the create date and the Firstlast fields are what you want to display move them onto GRoup header4 too.
Unsupress GH4
Suppress Details.
 
 
Thank you very much.


Print Page | Close Window