Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: date difference/comparison within several details Post Reply Post New Topic
<< Prev Page  of 3 Next >>
Author Message
benner
Newbie
Newbie


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet Posted: 18 Mar 2010 at 6:18am
Here is the formula:
 
Days between pmts:
datediff('d',date({@SP Pymnt date}),date({@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!
IP IP Logged
benner
Newbie
Newbie


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet 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({@SP Pymnt date}),date({@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!
 
 
IP IP Logged
DBlank
Moderator
Moderator


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


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


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


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet 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.

            Ins SP Ins SP
  Encounter # Insurance Plan Payment Type Payment Amt Payment Date Pymnt Pymnt Pymnt Date Pymnt Date
Group #1 header XX0001234567                
Details XX0001234567 XXX.XX.XX RCP 6,410.1 20090821 6,410.06 0.00 20090821 0
Details XX0001234567 SP RCP 1,318.6 20090904 0.00 1,318.57 0 20090904
Group #1 footer XX0001234567         6,410.06 1,318.57 20090821 20090904
  XX0001234568                
  XX0001234568 XXX.XX.XX RCP 3,841.6 20090918 3,841.60 0.00 20090918 0
  XX0001234568 SP RCP 770.1 20090925 0.00 770.14 0 20090925
  XX0001234568         3,841.60 770.14 20090918 20090925
  XX0001234569                
  XX0001234569 XXX.XX.XX RCP 15,341.2 20091023 15,341.19 0.00 20091023 0
  XX0001234569 SP RCP 320.6 20091113 0.00 320.61 0 20091113
  XX0001234569         15,341.19 320.61 20091023 20091113
  XX0001234560                
  XX0001234560 XXX.XX.XX RCP 252.1 20100108 252.06 0.00 20100108 0
  XX0001234560 SP RCP 529.6 20100115 0.00 529.62 0 20100115
  XX0001234560         252.06 529.62 20100108 20100115
 
IP IP Logged
benner
Newbie
Newbie


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet 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))
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?


Edited by DBlank - 18 Mar 2010 at 9:36am
IP IP Logged
benner
Newbie
Newbie


Joined: 18 Mar 2010
Online Status: Offline
Posts: 23
Quote benner Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 '@DateField' for my example.
Now use a datediff function with a Maxi and Min values at group1 to get the value
datediff('d',minimum({@dateField, group1 field),Maximum({@dateField, group1 field))
Place in group footer1
 
IP IP Logged
<< Prev Page  of 3 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.031 seconds.