date difference/comparison within several details
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9258
Printed Date: 17 May 2024 at 11:46pm
Topic: date difference/comparison within several details
Posted By: flanman
Subject: date difference/comparison within several details
Date Posted: 02 Mar 2010 at 11:07am
|
I have a report where I am pulling a list of events by company within a 90 day time frame. Some companies have several events in the details section. One of the fields I am pulling is a date field. I need to create a formula that compares each of the dates in the detail field and if the difference is less that 56 days I need to see that date. Something like this. Company 1 Event1 Jan 05, 2010 Event2 Feb 18, 2010 Event3 March 24, 2010 Company 2 Event 1 Jan 01, 2010 Event 2 Mar 15, 2020 I already have a preset 90 day range or pulling the initial dates. The filter I would create would compare Company 1 Event1 date to Event2 date, and Event 2 Date to Event 3 Date and if any are within 56 days show those items. Based on the filter Co. Company 2 would not show as the dates are more than 56 days apart. Hopefully that all makes sense. Flanman |
Replies:
Posted By: DBlank
Date Posted: 02 Mar 2010 at 11:14am
| If you moved the Company to the group footer you can do it without subreports. You also need to decide how you are handling your last row in the group. |
Posted By: flanman
Date Posted: 02 Mar 2010 at 11:31am
| Moving Company to group footer is fine, but not sure what you mean by handling the last row in the group. I want to compare all dates so if there are 2 dates then just those two. If there are multiple dates then date1 to date2, date2 to date3, date3 to date4 etc. The dates are already in chronological order so I don't need to compare date1 to date3 etc. But I am lost on how to even create the formula to do the comparison. |
Posted By: DBlank
Date Posted: 02 Mar 2010 at 11:39am
|
You can use the NEXT() to do a comparison inside a Running Total.
What I mean by the last row is that it never has a next value to compare to so it will never be withing the 56 days. Unless you want all details suppressed or if you want both rows that have < 56 days shown. Group on the Company Name Create a RT as "DayCount" Field to Summarize=Comapny Name
Type of Summary= DistinctCount
Evaluate=Use a Formula
next(table.companyname)=table.companyname and
datediff('d',table.date,next(table.date))<56
Reset=On change of group (company name group) Place on GF1 you will see the value of 1 when there is at least one row that meets your 56 day event |
Posted By: DBlank
Date Posted: 02 Mar 2010 at 11:43am
| YOu can then use this RT for suppression condition on teh footer. Note it won't suppress your header because the RT does not work in a header. |
Posted By: flanman
Date Posted: 02 Mar 2010 at 11:52am
|
Ok. I am trying that, but I am getting a syntax error. Based on what you have above my formula for the RT is: next{Accounts.Name} = Accounts.Name and datediff('d',DriveMaster.FromDateTime,next{DriveMaster.FromDateTime})<56 but I am getting the error "The remaining text does not appear to be part of the formula?? btw. Thanks for the help. |
Posted By: DBlank
Date Posted: 02 Mar 2010 at 11:55am
|
You have to parenth around the NEXT function...Next(table.field) ... and around the Datediff...Datediff('d',startdate, enddate)...
datediff('d',DriveMaster.FromDateTime,next({DriveMaster.FromDateTime}))<56
|
Posted By: flanman
Date Posted: 02 Mar 2010 at 11:56am
|
I got it. next({Accounts.Name}) = {Accounts.Name} and datediff('d',{DriveMaster.FromDateTime},next({DriveMaster.FromDateTime}))<56; Working now. Thanks again. |
Posted By: benner
Date Posted: 18 Mar 2010 at 5:51am
|
I am trying to calculate a similar formula. Difference between two date fields. I used the same formula in this thread. I get an error of "A date is required here". Any ideas on how to fix this?
Thank you
|
Posted By: DBlank
Date Posted: 18 Mar 2010 at 6:07am
|
1. What are your field types for every value inthe formula? 2. what is the formula?
3. what are you trying to do with the the formula?
|
Posted By: benner
Date Posted: 18 Mar 2010 at 6:18am
|
Here is the formula:
Days between pmts:
datediff('d',date( mailto:%7b@SP - {@SP Pymnt date}),date( mailto:%7b@Ins - {@Ins Pymnt date}))
The error I get now is "dates must be between year 1 and year 9999".
The sp payment formula is :
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} The ins pymnt date formula is:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} We are trying to calculate the number of days between the self pay payment and the insurance payment.
Thank you for your help!
|
Posted By: benner
Date Posted: 18 Mar 2010 at 6:41am
|
We are trying to calculate the number of days between payments. I made some changes to the formula...
Here is the formula:
datediff('d',date( mailto:%7b@SP - {@SP Pymnt date}),date( mailto:%7b@Ins - {@Ins Pymnt date}))
SP Pymnt date formula:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} Ins Pmymnt date formula:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} All formulas check out with no errors. When I refresh the report I now get the error "dates must be between year 1 and year 9999".
Thanks for your help!
|
Posted By: DBlank
Date Posted: 18 Mar 2010 at 8:43am
|
1. what are some sample values from the {DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} field and what data type is is?
2. you are trying to compare one row to another row, not 2 dates frm ther same row correct?
|
Posted By: benner
Date Posted: 18 Mar 2010 at 8:47am
|
one sample is: 20090821
20090904
These values are in the details. The group for the report is Encounter Payment.Encounter Number
|
Posted By: DBlank
Date Posted: 18 Mar 2010 at 9:02am
|
1. It appears that this is just a number field that you will need to convert into a date field (it thinks you are using the year of 20090821, not the date of 2009-08-21). try this for that:
date(left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),4)+'/'+mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),5,2) + '/' + right(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),2))
2. You will need the NEXT function to evaluate from one row to the next.
Hard to tell exacly how to do that.
Post multiple rows (including the PlanCode) and how you want to calcualte it.
|
Posted By: benner
Date Posted: 18 Mar 2010 at 9:22am
|
See below for examples of output. We need to calculate the number of days between the SP payment and the Insurance payment. If there is an easier way to do this please let me know...I've tried multiple different formula's so I'm open to suggestions. I do not necessarily think we need the last four columns...they were added by someone else but I left them in just case we need them. Again...thank you for your help.
|
Posted By: benner
Date Posted: 18 Mar 2010 at 9:30am
|
With this formula I get an error..."bad date format string".
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} <> "SP" then
date(left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),4)+'/'+mid(totext ({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),5,2) + '/' + right(totext ({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE}),2)) |
Posted By: DBlank
Date Posted: 18 Mar 2010 at 9:35am
|
what about:
date(mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),5,2) + '/' + mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),7)+'/'+left(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),4))
Where do you need to display the date difference at?
|
Posted By: benner
Date Posted: 18 Mar 2010 at 9:43am
|
The formula worked! Thank you! Was thinking we would put the days difference in the Group #1 footer. |
Posted By: DBlank
Date Posted: 18 Mar 2010 at 10:25am
|
If you always only have 2 rows per group:
Use the aobe formula to convert the field to a date, I'll call it mailto:@DateField - '@DateField' for my example.
Now use a datediff function with a Maxi and Min values at group1 to get the value
datediff('d',minimum( mailto:%7b@dateField - {@dateField , group1 field),Maximum( mailto:%7b@dateField - {@dateField , group1 field))
Place in group footer1
|
Posted By: benner
Date Posted: 18 Mar 2010 at 10:52am
|
We don't always necessarily have just two rows. Might be more based on hom many sp payments the patient makes. Here is my formula...I'm getting zero's when I use the min...if i use max it works but I really want the min on the sp part as some patient's make multiple self pay pmts...so I want first sp payment date that is posted.
datediff('d',(Maximum ( mailto:%7b@Ins - {@Ins Pymnt date}, {DS_ENCOUNTER_PAYMENTS.ENCOUNTER_NUMBER})),
(Minimum ( mailto:%7b@SP - {@SP Pymnt date}, {DS_ENCOUNTER_PAYMENTS.ENCOUNTER_NUMBER}))) |
Posted By: DBlank
Date Posted: 18 Mar 2010 at 11:38am
|
try changing your formula to throw in a large future date on the else when it is an SP type...
sp payment formula to:
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
{DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE} else dateadd('yyyy',12,currentdate) |
Posted By: benner
Date Posted: 19 Mar 2010 at 2:45am
|
Here is my formula now for the sp pmt: if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
date(mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),5,2) + '/' + mid (totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),7)+'/'+left (totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),4))else else dateadd('yyyy',12,currentdate) I get the error "a number, currency amount, boolean, date, time,date-time or string is expected here"
|
Posted By: benner
Date Posted: 19 Mar 2010 at 2:48am
| Sorry...too early in the morning...I had two "esle"...I'm testing now. Will let you know how it turns out. |
Posted By: benner
Date Posted: 19 Mar 2010 at 2:58am
|
Here is the formula now and this works except when the patient does not have a self pay payment. If there is no self pay payment can we make the date zero? So the date diff will calculate a zero?
if {DS_ENCOUNTER_PAYMENTS.INSURANCE_PLAN_CODE} = "SP" then
date(mid(totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),5,2) + '/' + mid (totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),7)+'/'+left (totext({DS_ENCOUNTER_PAYMENTS.PAYMENT_POSTING_DATE},0,''),4))else dateadd('yyyy',12,currentdate)
|
Posted By: DBlank
Date Posted: 19 Mar 2010 at 3:49am
|
Maybe:
if Minimum ( mailto:%7b@SP - {@SP Pymnt date}, {DS_ENCOUNTER_PAYMENTS.ENCOUNTER_NUMBER})=
dateadd('yyyy',12,currentdate) then 0 else
datediff('d',(Maximum ( mailto:%7b@Ins - {@Ins Pymnt date}, {DS_ENCOUNTER_PAYMENTS.ENCOUNTER_NUMBER})),
(Minimum ( mailto:%7b@SP - {@SP Pymnt date}, {DS_ENCOUNTER_PAYMENTS.ENCOUNTER_NUMBER}))) |
Posted By: benner
Date Posted: 19 Mar 2010 at 3:55am
|
It works! Thank you for all your help on this! This is a great forum.
Thanks again!
 |
Posted By: Kohinoor
Date Posted: 16 Sep 2010 at 8:44pm
|
hi friends
i am using this formula in suppress in crystal report bcoz i need to compare that date difference is less then 30 if (datediff('d', date({@AsOnDate}), date({command.ImportDate})) > 30) then true else false thank you for helping me i learn it from here only but still i have some problem so please help me more thank you again kohinoor ------------- hi i am software developer from gandhinagar,gujarat, india |
Posted By: DBlank
Date Posted: 17 Sep 2010 at 3:48am
|
1. what is the Asondate formula?
2. what is the problem you are having?
3. drop the if and then portion as it is not needed...
datediff('d', date({@AsOnDate}), date({command.ImportDate})) > 30
|