Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Find all records 30 days from current date Post Reply Post New Topic
Page  of 2 Next >>
Author Message
phoeneous
Newbie
Newbie


Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
Quote phoeneous Replybullet Topic: Find all records 30 days from current 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)
 


Edited by phoeneous - 17 Dec 2009 at 2:53pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
phoeneous
Newbie
Newbie


Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
Quote phoeneous Replybullet 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))
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
phoeneous
Newbie
Newbie


Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
Quote phoeneous Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
phoeneous
Newbie
Newbie


Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
Quote phoeneous Replybullet 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?
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
 
IP IP Logged
flanman
Senior Member
Senior Member
Avatar

Joined: 04 Nov 2009
Online Status: Offline
Posts: 123
Quote flanman Replybullet 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.
IP IP Logged
phoeneous
Newbie
Newbie


Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
Quote phoeneous Replybullet 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
 
?
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.