Author |
Message |
phoeneous
Newbie
Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
phoeneous
Newbie
Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
|
Posted: 18 Dec 2009 at 8:54am |
Originally posted by DBlankYou 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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
phoeneous
Newbie
Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
phoeneous
Newbie
Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
flanman
Senior Member
Joined: 04 Nov 2009
Online Status: Offline
Posts: 123
|
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 Logged |
|
phoeneous
Newbie
Joined: 06 Oct 2009
Online Status: Offline
Posts: 13
|
Posted: 28 Dec 2009 at 10:37am |
Originally posted by flanmanin 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 Logged |
|
|